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://www.slingandstonemusic.com/seats/seats.php.
Test users: user1 (pass123) and user2 (pass321).
Download link (make sure to rename the downloaded file to .zip instead of .odt)
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 "<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' />";
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:
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”.
Could you set up a live demo of the script, please?
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.
thank you so much for this!
Is there a demo of it anywhere? My service provider doesn’t have mysql
I keep getting this ‘Phpinfo’ thing and no ticketreservationsystem! What do I do?
Ok got it but what is the login and password??
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.
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).
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
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
@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.
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
THANK YOU VERY MUCH SIR!!! YOU REALLY ARE OUR SAVIOR!!!
MAY MORE BLESSINGS COME TO YOU. . .
GODBLESS,
AARON
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
Glad to be of assistance.
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…
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
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..
@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.
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.
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?????
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
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.
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
You’re most welcome Ben.
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..
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!
I would like to develope a simple BUS RESERVATION SYSTEM in Php and MySQL.
PLEASE SEND ME A SAMPLE OF IT TO GUIDE ME
Email sent.
Nice blog bro!
Hi i m working in a software which is linked of your developed software can you guve me your software on my mail address it will be very thakful to me.pls
pls sent me a sample to guide
Tahim, sorry for the late reply. You can find a link to the complete source at the bottom of the post (before the comments section). Thanks.
i’m really interested with this system.. 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.
Email sent. Ashraf, please check your inbox. Thanks.
hi guys, i’d like to have the file sent to me too please…thanks guys!!
Hi,
Two things
1. Please email me the files i see how they work
2. I am doing a bus reservation system and a cinema reservation system. Could you please chat me as i would like to give you some work if you have time.
Email me for my IM.
@randyrr, @Bernard: The download link is at the bottom of the post content. I’ve also updated the post to repeat the download information at the top of the post.
Hope this helps.
@Bernard: Thanks for the offer, but unfortunately, I can’t take additional workload at the moment. I can always assist you though if you encounter any major issues. Please feel free to post your concerns here.
Thx a lot for the code, simple and nice. After implement your system, I send you link, that you can check ;o) Nice work
i always cant connect to mysql server. I am sure that parametres are OK. Its possible that my server doesn’t get the ‘mysql_pconnect’ command?
ok, sorry it works. But it needs time about 3-5 minutes to view the avaible seats (and page fully loads) . What can be wrong?
@pyby: Can you try checking if there are loops/routines which are being called/executed more than they should be? Other than that, I can’t make any useful comments unless you send us your code.
hi
good job
can i use it for airline booking?
please help me if you have any script about airline ticket booking
best regards
Hi mohsen!
You can simply update the values in the database depending on the layout of the seats, so the answer is YES, this can also be used for airline booking.
Cheers,
Paul
hi
how can i make control panel for manage reserved seats by users?
i want all users can reserve seats and admin can see which user reserved which seat?
thanks a lot
Hi imad,
You will need to update userauth database table to include all the users that you want to have access to the system.
And then in seats.php you probably need to update the part where we change the color of the seat based on the user who reserved the seat.
Sorry, if the application currently requires digging through the code for such customizations. I hope I can make improvements to it in the future but I just don’t have the time now to make such changes.
Hope you still find it useful though.
Paul
thanks a lot Paul;)
hi paul
please help me
i want too change the authentication system , your script uses $_SERVER[PHP_AUTH_USER] , and my script uses md5 hash for password in member db, how can i change the authentication for working with md5 hash and match witch my script , i want too use my table too authenticate ,
I’m guessing that you just need to update login.php to point to your table instead of the default userauth. Also, when trying to match passwords, you can use the built-in md5() function in MySQL to compare what is stored in the DB to what the user entered.
“select … where … and password = md5(” .$_SERVER[PHP_AUTH_PW] .”)”
Note: The method above is for illustration purposes only and is prone to SQL injection attacks. I recommend you also clean the user entered parameters before using them in your SQL statements.