PHP 101: A Simple Seat Reservation System

I would like to share a little application I developed last year for a simple concert that our church sponsored.

The requirement was to develop a seat/ticket reservation system which will be accessed from two different locations.

The application will be used by administrators only to check the available seats and will not be accessible to end users.

UPDATE: 8/1

In response to visitor requests, a live demo of the script is now available at http://music.slingandstoneweb.com/seats/seats.php.

Test users: user1 (pass123) and user2 (pass321).

Database Design:


CREATE TABLE seats (
   rowId varchar(1) not null,
   columnId int not null,
   status int,
   updatedby varchar(10),
   PRIMARY KEY (rowId,columnId)
);

CREATE TABLE userauth (
   rowID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   commonname VARCHAR(35) NOT NULL,
   username VARCHAR(8) NOT NULL,
   pswd VARCHAR(32) NOT NULL,
   PRIMARY KEY(rowID)
);

I tried to make the tables as simple as possible.

For the seats table, I used the rowId and columnId columns as the PK. The status column represent the state of a particular seat (0-available, 1-reserved, 2-confirmed). The updatedby column is for storing the name of the user who last updated the record (importance of this column will be discussed later).

I also needed some form of authentication which is contained in the userauth table. I think the columns for this table are self-explanatory, so there’s really no need to go into more detail about that.

I created a login.php page for authentication. I had to embed this page to all the other pages which I wanted to protect from unauthorized access.


function authenticate_user() {
   header('WWW-Authenticate: Basic realm="Tickets"');
   header("HTTP/1.0 401 Unauthorized");
   exit;
}

If you try to access a page which invokes the fragment above then you will be presented with a screen similar to what is shown below:

A word of caution from the Beginning PHP and MySQL 5 book:

Note that unless output buffering is enabled, these commands must be executed before
any output is returned. Neglecting this rule will result in a server error, because of the violation
of the HTTP specification.

You can retrieve the values entered into this form through the following scripts:


$_SERVER[PHP_AUTH_USER] // for retrieving username
$_SERVER[PHP_AUTH_PW] // for retrieving password

The values retrieved from the form are then compared to the entries in the userauth table. And if no match is found, the login form is re-displayed to the user.


// Connect to the MySQL database
mysql_pconnect("localhost", "tickets", "tickets")
   or die("Can't connect to database server!");
mysql_select_db("tickets") or die("Can't select database!");

// Check for matching users
$query = "SELECT username, pswd FROM userauth"
   . " WHERE username='$_SERVER[PHP_AUTH_USER]'"
   . " AND pswd='$_SERVER[PHP_AUTH_PW]'";
$result = mysql_query($query);

// Re-display login form
if (mysql_num_rows($result) == 0) {
authenticate_user();
}

I do hope all these snippets still make sense to you because we are just about to proceed to the more complicated stuff.

Given the seat layout of the concert hall, I decided to represent the rows using letters and the columns as numbers. The tricky part was how to dynamically generate the seat layout based on the rows retrieved from the seats DB table.

The next fragments were taken from seats.php


// Select all the seats in the venue
// The seat numbers in the concert hall were read from right to left
// (which is why columnId is sorted as desc)
$query = "SELECT * from seats order by rowId, columnId desc";
$result = mysql_query($query);

// Iterate through results, assign values to rowId, columnId,
// status and updatedBy variables
while (list($rowId, $columnId, $status, $updatedby)
   = mysql_fetch_row($result))

We now begin with the fun part, for every seat, which is still available, create a checkbox. For every seat which is reserved, also create a checkbox only if the user currently logged in is also the same user who reserved the seat. You may be wondering, why do I need to create a checkbox for a seat that’s already reserved? This is because, in this application, the reserved state is not the final state for a seat. A reserved seat can still be canceled or confirmed. But once a reserved seat is confirmed, no further modifications can be made to that seat.

echo "\n
<td bgcolor='$seatColor' align='center'>";
echo "$rowId$columnId";

if ($status == 0
   || ($status == 1
      && $updatedby == $_SERVER['PHP_AUTH_USER'])) {
   echo "<input type='checkbox' name='seats[]'"
      . " value='$rowId$columnId'></checkbox>";

From PHP, we step back a bit and discuss the JavaScript part of the code which is as important as the PHP scripts.

	function reserveSeats() {

		var selectedList = getSelectedList('Reserve Seats');

		if (selectedList) {
			if (confirm('Do you want to reserve selected seat/s ' + selectedList + '?')) {
				document.forms[0].oldStatusCode.value=0;
				document.forms[0].newStatusCode.value=1;
				document.forms[0].action='bookseats.php';
				document.forms[0].submit();
			} else {
				clearSelection();
			}
		}
	}

	function cancelSeats() {

		var selectedList = getSelectedList('Cancel Reservation');

		if (selectedList) {
			if (confirm('Do you want to cancel reserved seat/s ' + selectedList + '?')) {
				document.forms[0].oldStatusCode.value=1;
				document.forms[0].newStatusCode.value=0;
				document.forms[0].action='bookseats.php';
				document.forms[0].submit();
			} else {
				clearSelection();
			}
		}
	}

	function confirmSeats() {

		var selectedList = getSelectedList('Confirm Reservation');

		if (selectedList) {
			if (confirm('Do you want to confirm reserved seat/s ' + selectedList + '?')) {
				document.forms[0].oldStatusCode.value=1;
				document.forms[0].newStatusCode.value=2;
				document.forms[0].action='bookseats.php';
				document.forms[0].submit();
			} else {
				clearSelection();
			}
		}
	}

You will notice that there are three main functions (reserveSeats(), cancelSeats() and confirmSeats()) for modifying a seat’s status.

These scripts submit the values of the HTML FORM in seats.php to another PHP page named bookseats.php. This bookseats page is particularly interested in three FORM parameters from the seats page namely, oldStatusCode, newStatusCode and the seats array generated by the checkboxes.

An example of the request would be:

oldStatusCode=0
newStatusCode=1
seats[A1,A2,B3]

Which means Reserve the currently Available A1, A2 and B3 seats.

It is important to note the behavior of checkboxes in HTML. Only the values of “selected” checkboxes get submitted when you make a POST request. You can retrieve request parameters in PHP using $_GET for GET requests and$_POST for POST requests.

The bookseats page is divided into two parts. The first part checks whether the seats are still in the same state (no changes were done to the seats by another user while we were busy doing something else) prior to making any updates.


// dynamically build select statement

$selectQuery = "SELECT rowId, columnId from seats where (";
$count = 0;
foreach($_POST['seats'] AS $seat) {
if ($count > 0) {
$selectQuery .= " || ";
}
$selectQuery .= " ( rowId = '" . substr($seat, 0, 1) . "'";
$selectQuery .= " and columnId = " . substr($seat, 1) . " ) ";
$count++;
}

$selectQuery .= " ) and status = $oldStatusCode";
if ($oldStatusCode == 1) {
$selectQuery .= " and updatedby = '$user'";
}

// execute select statement
$result = mysql_query($selectQuery);

$selectedSeats = mysql_num_rows($result);
if ($selectedSeats != $count) {
$problem = "
<h3>There was a problem executing your request. No seat/s were updated.</h3>
";
die ($problem);
}

If the system detects any concurrent updates then it stops with step 1, displays an error message to the user and does not proceed with step 2.

The second part performs the actual database update after necessary checks from step 1 have been processed.


// prepare update statement
$newStatusCode = $_POST['newStatusCode'];
$oldStatusCode = $_POST['oldStatusCode'];

$updateQuery = "UPDATE seats set status=$newStatusCode, updatedby='$user' where ( ";
$count = 0;
foreach($_POST['seats'] AS $seat) {
 if ($count > 0) {
 	$updateQuery .= " || ";
 }
 $updateQuery .= " ( rowId = '" . substr($seat, 0, 1) . "'";
 $updateQuery .= " and columnId = " . substr($seat, 1) . " ) ";
 $count++;
}
$updateQuery .= " ) and status = $oldStatusCode";
if ($oldStatusCode == 1) {
 $updateQuery .= " and updatedby = '$user'";
}

// perform update
$result = mysql_query($updateQuery);
$updatedSeats = mysql_affected_rows();

if ($result && $updatedSeats == $count) {
 echo "
<h3>";
 echo "You have successfully updated $updatedSeats seat/s: ";
 echo "[";
 foreach($_POST['seats'] AS $seat) {
 	$rowId = substr($seat, 0, 1);
 	$columnId = substr($seat, 1);
 	echo $rowId . $columnId . ", ";
 }
 echo "]";
 echo "...</h3>
";
}

This application represents my first attempt into the PHP world and I must say that there are still a lot of improvements that can be added to this quick and dirty solution.

For one, the user can create his own login form instead of relying in the browser’s basic login form (shown earlier).

A logout option would be a very worthy addition and can be achieved by simply adding a link which calls the session_destroy() directive.

Styles and scripts can also be moved to external CSS and external JavaScript files.

Anyway, I still hope I was able to provide you enough details about PHP to serve as starting point.

Feel free to download (and rename to .zip) the application and modify to your own liking.

Recommended books:

1590598628 05960097630596101996

About these ads

34 Comments »

  1. dayg said

    If you need to enable PHP error messages during development, just look for the php.ini file in your PHP installation directory and change the “display_errors” property value from “Off” to “On”.

  2. charlie said

    Could you set up a live demo of the script, please?

  3. dayg said

    Hi Charlie, I hope you’re not much in a hurry.

    I should be able to upload a live demo by August.

    I’m currently busy working on http://www.slingandstonemusic.com and another intranet application for our church ministry.

    Hope you won’t mind.

  4. John said

    thank you so much for this!

  5. Benny said

    Is there a demo of it anywhere? My service provider doesn’t have mysql

  6. Benny said

    I keep getting this ‘Phpinfo’ thing and no ticketreservationsystem! What do I do?

  7. Benny said

    Ok got it but what is the login and password??

  8. dayg said

    Hi Benny,

    Try running this statement in MySQL:

    insert into userauth values (1, ‘User 1′, ‘user1′, ‘password’);

    Then you should be able to login using the user1/password combo.

    If you wish to add password encryption, you can also checkout some of the encryption functions available in MySQL.

    Hope this helps.

  9. dayg said

    In response to visitor requests, a live demo of the script is now available at http://www.slingandstonemusic.com/seats/seats.php.

    Test users: user1 (pass123) and user2 (pass321).

  10. Pradeep said

    Hi,

    I am actually looking to implement selection of seating arrangements for our existing portal which is meant for theatre lovers.

    Please get in touch with me with your email contact details so that I can discuss the possibility of outsourcing this project.

    thanks,
    Pradeep

  11. Ram Kishore said

    Hello,

    I was searching for a solution for a similar requirement and I found your code. It is really nice and simple. Please let me know if there is any latest modification in this code.

    Please get in touch with me with your email contact details so that I can discuss more and improvise the code.

    Thanks,
    Ram Kishore

  12. dayg said

    @Ram: Thanks for the nice comments. Yes of course, feel free to make enhancements to the scripts. Unfortunately, I haven’t made updates to them since a long time ago. I was still starting with PHP back then (and still learning now).

    @Pradeep: Thanks for the invite, but I’m quite loaded at the moment. Hope you can get a programmer for this one soon. It shouldn’t be that difficult to implement.

  13. aaron said

    Excuse me sir Dayg,

    I’m a 4th yr. college student and my course is Information technology. We are on the process of Systems Analysis and Design and the system that we are going to implement is Bus Seat reservation. The system that you made was great and we could use it in our system and you are willing to give the codes and edit it as we like. But, the problem is i don’t know how to rename the file to .zip, hehe sorry but I’m still a rookie but i understand the codes that you wrote.

    Could you please send me the file of this system so that me and my classmates can review the system and implement it in the system that we are going to make. My email account is [removed to prevent spam].

    Please sir, I am hoping for your kind consideration. And understand us that not all things were thought in school and it is really hard to make a system for a bunch of rookies.

    Thanks,
    Aaron

  14. aaron said

    THANK YOU VERY MUCH SIR!!! YOU REALLY ARE OUR SAVIOR!!! :D

    MAY MORE BLESSINGS COME TO YOU. . .

    GODBLESS,
    AARON

  15. carrol said

    i am 34 now ..my wish is to make a successful portal…i am trying to learn PHP on my own ….i was loosing hope………your page made a difference…
    thanks a lot

  16. dayg said

    Glad to be of assistance. :)

  17. piyong said

    hello sir, im a fourth year student and your code is very helpful to us… Can you teach me sir to update the cancel button where in i added a 1 field to database which is reservedby and when i click the cancel button the reserved by field will be set to null, thank you sir hope you will help us and the Lord will give you back all the blessings, thank you very much God bless you…

  18. wawa said

    hello sir Dyang..

    i’m really interested with this system. For now, i want to implement your system to my online ticketing system for cinema. i’m still new with php so i really hope u can send the file of this system so that i will more understand to do mine.

    can u send it to my email. plez..i’m stuck now n really need ur help.

    thanks alot and hav a nice day :)

  19. wawa said

    hi sir,

    i’m tried to rename the application to .zip but it’s cannot extract..can u send the file of this system to my email?..i really need it for my online movie ticketing system..i know ur system can help me much..plez..

    thanks..

  20. dayg said

    @wawa: I renamed the attached sample application to zip extension (benefits of not being hosted in wordpress.com). Please try downloading it again.

    @piyong: Do you have an IM? I think it might be a better idea to discuss things there. I don’t want to give you the exact answer, but I’d love to assist you come up with the solution.

  21. wawa said

    hi sir,

    i’m tried re-downloading it and it’s works..thanks a lot sir. At this moments i hav a prob on printing the reservation and i’m glad u can assist me.

    owh, i hav ym so glad to see u there. add me on wawarais_thegreat.

    thanks a lot sir, may god bless u.

  22. obtrs said

    i want to try this by my self when ever i reserve or confirm seat nothing happens i have same data base as yours also 2 users in database as urs when i book nothing happens no color change and its not posting it to the database what should i do?????

  23. obtrs said

    i want to try this by my self when ever i reserve or confirm seat nothing happens i have same data base as yours also 2 users in database as urs when i book nothing happens no color change and its not posting it to the database what should i do?????

    sorry wrong email address on the first comment

  24. dayg said

    obtrs,

    Currently the application uses hardcoded “usernames” to determine the seat colors.

    Did you use user1 and user2 as the usernames? If not, you will need to modify a few lines at the bottom of the scripts to conform with the names you created.

    HTH.

    If you still have a hard time setting up the application feel free to contact me again.

    :)

  25. Ben said

    WOW thanks sooo much,
    I have been looking for a script like this on the web for ages.
    I am planning to edit this script to be multi user, as in 1200+ users, and have the database save their name next to their seats, and then add an admin panel function to allow a staff member to readily obtain a list of ticket buyers

    once again thanks

  26. dayg said

    You’re most welcome Ben. :)

  27. ezeepzee said

    Hey dayg,
    May i say, an excellent system you have here. As with most of the other comments, i too am trying to implement a seat reservation system for an assignment at university.

    Being totally new to php, it is a bit mind blowing – i have a similar layout to yours, however i cant seem to get the page to update the fields in the mysql database.

    I was wondering if i could be a bit cheeky, and see if you would be prepared to supply the bookseats.php source code? If i could compare mine to yours, it would be a great help :) – im sure i have a few lines of code wrong somewhere, but cant seem to put my fingers on it..

    Thanks in advanced, and may i say how refreshing it is to find someone on the internet who is prepared to assist others with coding problems.

    Kind Regards..

  28. dayg said

    ezeepzee,

    You can find the link to the full source at the bottom of the post.

    However, since wordpress does not allow zip extensions, I used the .odt extension. Just remove the .odt and rename it to .zip.

    I’m glad you found it useful.

    See you around!

  29. vinny said

    I would like to develope a simple BUS RESERVATION SYSTEM in Php and MySQL.
    PLEASE SEND ME A SAMPLE OF IT TO GUIDE ME

  30. dayg said

    Email sent. :)

  31. Lysender said

    Nice blog bro!

  32. macoy said

    sir can you email me a sample of this php programming? it inspired me and i admired your genorosity. thank you very much.

  33. francis said

    Hi am creating a bus booking system. Would you mind sending me a copy of this code

  34. dayg said

    Hi Francis,

    Thank you for your interest. The link to the code can be found at the start and at the end of the post: http://unlikelyteacher.com/2008/04/17/php-101-a-simple-seat-reservation-system/

    Look for the keyword “download” or “application”.

    Cheers,
    Paul

RSS feed for comments on this post · TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: