CIS 236 Interactive Web Programming

Addendum #2: Campground Reservation Systems

 

This page contains additional details and specifications for the CRS project.

Reservation Table Definition

-- phpMyAdmin SQL Dump
-- version 2.10.0.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Apr 27, 2009 at 03:43 PM
-- Server version: 5.0.22
-- PHP Version: 5.1.2

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `cis236shared`
--

-- --------------------------------------------------------

--
-- Table structure for table `reservations`
--

CREATE TABLE `reservations` (
`ResID` int(11) NOT NULL auto_increment,
`site` varchar(4) NOT NULL,
`camperID` char(20) NOT NULL,
`startDate` date NOT NULL,
`endDate` date NOT NULL,
`charge` decimal(19,2) NOT NULL,
`amtPaid` decimal(19,2) NOT NULL,
`cancelled` char(1) NOT NULL default 'N',
`notes` text NOT NULL,
PRIMARY KEY (`ResID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `reservations`
--

INSERT INTO `reservations` (`ResID`, `site`, `camperID`, `startDate`, `endDate`, `charge`, `amtPaid`, `cancelled`, `notes`) VALUES
(1, '100', 'sc4camper', '2009-04-29', '2009-05-08', '75.00', '25.00', 'N', 'Our first sample reservation'),
(2, '100', 'sc4camper', '2009-07-01', '2009-07-14', '135.00', '150.00', 'N', 'Back for the 4th of July');

 

SQL Examples:

Here are some "snippets" to try out in the SQL window of phpMyAdmin. (which can then be incorporated as search strings in your PHP program) W3Schools has a pretty good tutorial/overview of SQL with examples http://www.w3schools.com/sql/default.asp

Find reservations that aren't paid in full:

SELECT * FROM `reservations`
WHERE `amtPaid` < `charge`
 

Add a column for length of reservations, and a column with the last possible end date for a 2 week stay:

SELECT *,
  DATEDIFF(`endDate`,`startDate`) AS numDays,
  ADDDATE(`startDate` , 14) AS gottaGO
FROM `reservations`

Get the email address for reservations in June and July

SELECT reservations.ResID, reservations.site, campers.email, reservations.startDate
FROM `reservations`
LEFT JOIN campers
ON reservations.camperID = campers.camperID
WHERE reservations.startDate
BETWEEN '2009-06-01' AND '2009-07-31'

Dealing with Dates and date formats:

Good article/blog post explaining the mySQL and PHP date differences (and why this is a pain in the rear) http://www.bigroom.co.uk/blog/dates-in-php-and-mysql

Official MySQL date reference at http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

Here are some code examples to handle mySQL <-> JavaScript date format differences.

http://www.finefrog.com/2009/01/31/convert-a-mysql-date-string-into-javascript-date-object/

http://boonedocks.net/mike/archives/157-Formatting-a-Javascript-Date-for-MySQL.html

DESIGN DECISION - Can we get away without having to actually manipulate the dates in PHP? (in other words, in PHP we handle them as strings, and do all of the necessary manipulation in JavaScript or MySQL)

Here is an interesting tool that generates mySQL and PHP "snippets" for date formatting http://www.bitbybit.dk/php/date_format/