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.
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.
The sources are available for download from GitHub at: https://github.com/polarvortex/tickets.git.
Document As You Go 