forgot password?
need an account?

Notifications close x

PHP MYSQL AJAX Simple Reservation System

loading social widgets...
LewisAndersonWritten By: LewisAnderson   flag
Publish Date: January 16, 2015
Code from this tutorial. connect.php
<?php // Put your specific mysql database connection data below // host, user, password, database name in between the double quotes ---> " here " $connect = mysqli_connect("localhost", "root", "pass", "reservations"); // Evaluate the connection if (mysqli_connect_errno()) { echo mysqli_connect_error(); exit(); } ?>
make_tables.php
<?php include_once("connect.php"); $tbl_available = "CREATE TABLE IF NOT EXISTS available ( id int(11) NOT NULL AUTO_INCREMENT, tablenum varchar(50) NOT NULL, avail int(11) NOT NULL, price varchar(20) NOT NULL, PRIMARY KEY (id) )"; $query = mysqli_query($connect, $tbl_available); if ($query === TRUE) { echo "<h3>available table created OK :) </h3>"; } else { echo "<h3>available table NOT created :( </h3>"; } $tbl_confirms = "CREATE TABLE IF NOT EXISTS confirms ( id int(11) NOT NULL AUTO_INCREMENT, tablename varchar(50) NOT NULL, numseats int(11) NOT NULL, person varchar(255) NOT NULL, email varchar(255) NOT NULL, PRIMARY KEY (id) )"; $query = mysqli_query($connect, $tbl_confirms); if ($query === TRUE) { echo "<h3>confirms table created OK :) </h3>"; } else { echo "<h3>confirms table NOT created :( </h3>"; } $tbl_reserves = "CREATE TABLE IF NOT EXISTS reserves ( id int(11) NOT NULL AUTO_INCREMENT, tablenumber varchar(50) NOT NULL, numseats int(11) NOT NULL, restime datetime NOT NULL, PRIMARY KEY (id) )"; $query = mysqli_query($connect, $tbl_reserves); if ($query === TRUE) { echo "<h3>reserves table created OK :) </h3>"; } else { echo "<h3>reserves table NOT created :( </h3>"; } $tbl_insert = "INSERT INTO available (tablenum,avail,price) VALUES ('Table 1', 10, '$10.00'), ('Table 2', 10, '$10.00'), ('VIP Table 1', 10, '$20.00')"; $query = mysqli_query($connect, $tbl_insert); if ($query === TRUE) { echo "<h3>Starting data inserted OK :) </h3>"; } else { echo "<h3> NOT inserted :( </h3>"; } ?>
auction.css
#wrapper{ width: 600px; margin-right: auto; margin-left: auto; } #stage{ background-image: url(stage.jpg); background-repeat: no-repeat; height: 200px; width: 600px; } .available{ background-image: url(openTable.jpg); background-repeat: no-repeat; float: left; height: 200px; width: 200px; } .full{ background-image: url(tableFull.jpg); background-repeat: no-repeat; float: left; height: 200px; width: 200px; } .clear{ clear: both; } .numSeats{ color: #03F; margin: 75px; height: 50px; width: 50px; }
resProcessing.php
<?php //**** START Database connection script // Connect to database script include ("connect.php"); //**** END Database connection script //**** START Create numbered seat buttons if (isset($_POST['getSeatBtns'])){ // Initialize our display output to NULL $spots = ""; // Clean incoming POST to only allow numbers 0-9 // This will be what table id we are using to build seat buttons $tid = preg_replace('#[^0-9]#', '', $_POST['getSeatBtns']); // If POST is empty after cleaning, exit script if($tid == ""){ exit(); } // Build our query and run it $sql = "SELECT * FROM available WHERE id='$tid'"; $query = mysqli_query($connect, $sql) or die (mysqli_error($connect)); // Run a quick check to verify there are any results $quick_check = mysqli_num_rows($query); // If there are results to be had, get them in a loop if ($quick_check != 0){ while ($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){ // Assign values from query $id = $row['id']; $tablenum = $row['tablenum']; $avail = $row['avail']; $price = $row['price']; $stBtn = ""; // Build display output // VIP TABLE 1 has 7 seats available - $20.00 per seat $spots .= $tablenum.' now has '.$avail.' seat(s) available - '.$price.' per seat<br />'; $spots .= "Click number of seats you want to reserve, will be held for 3 minutes once you click.<br />"; // Construct our buttons for each seat that is available for ($k = 0 ; $k < $avail; $k++){ $k2 = $k+1; // Give each button the id of tbid_ and the table id and seat number // tbid_3_7 $stBtn .= '<button id="tbid_'.$id.'_'.$k2.'" onClick="reserveSeats(this.id)">'.$k2.'</button>'; } // Add our seat buttons to the display output $spots .= "$stBtn<br />"; } } else { // If they sat there too long without picking a table // Someone could have already reserved all the seats at that table $spots = "Sorry, seats are no longer available for that table."; } // Return our display output to ajax and exit echo $spots; exit(); } //**** END Create numbered seat buttons //**** START reserving number of seats if (isset($_POST['reserve'])){ // Initialize our display output to NULL $spots = ""; // Clean incoming POST // $tid will hold the id of the table they are reserving // $num willl hold the number of seats they are reserving $tid = preg_replace('#[^0-9]#', '', $_POST['reserve']); $num = preg_replace('#[^0-9]#', '', $_POST['num']); // If POSTS are empty after cleaning, exit if($tid == "" || $num == ""){ exit(); } // Build our query and run it $sql = "SELECT * FROM available WHERE id='$tid' AND avail >='$num'"; $query = mysqli_query($connect, $sql) or die (mysqli_error($connect)); // Run a quick check to verify there are any results $quick_check = mysqli_num_rows($query); // If there are results to be had, get them in a loop if ($quick_check != 0){ while ($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){ // Assign values from query $id = $row['id']; $tablenum = $row['tablenum']; $avail = $row['avail']; $price = $row['price']; // Build display output // You just reserved 8 seats at table VIP Table 1 $spots .= 'You just reserved '.$num.' seats at table '.$tid.'<br />'; $spots .= 'You only have 3 minutes to finish or your reservation expires and the seats open up to other people.<br />'; } // Math to change available number of seats for other visitors $availNow = $avail - $num; // Update the database to reflect new number of seats for that table $sql = "UPDATE available SET avail='$availNow' WHERE id='$id' LIMIT 1"; $query = mysqli_query($connect, $sql); // Insert the reserved table and number of seats into the reserves table $sql = "INSERT INTO reserves(tablenumber,numseats,restime) VALUES ('$tablenum','$num',now())"; $query = mysqli_query($connect, $sql); // Get the insert id $reserveID = mysqli_insert_id($connect); ///////////////////////////////////////////////////// ////////// If you want paypal do it here //////////// ///////////////////////////////////////////////////// $spots .= 'Add paypal form here if wanted<br />'; // Just a simple name/email form to "buy" the seats they reserved // We are cancelling the submit to handle with ajax $spots .= '<form name="confirmform" id="confirmform" onSubmit="return false;">'; $spots .= 'Name:<input id="name" type="text"><br />'; // hidden field holds the table name $spots .= '<input id="tableNumber" type="hidden" value="'.$tablenum.'">'; // hidden field holds the number of seats $spots .= '<input id="numSeats" type="hidden" value="'.$num.'">'; // hidden field holds the reserve insert id $spots .= '<input id="reserveID" type="hidden" value="'.$reserveID.'">'; // On submit call js function $spots .= '<button id="confirmbtn" onClick="confirmSeats()">Buy Seats</button>'; // Give them a cancel button if they change their mind $spots .= '&nbsp;&nbsp;&nbsp;<button onClick="cancelReserve(''.$reserveID.'')">Cancel Reserved Seats</button>'; $spots .= '</form>'; ///////////////////////////////////////////////////// /////////////////////End paypal////////////////////// ///////////////////////////////////////////////////// } else { $spots .= "Sorry, someone just reserved those. Try another table"; $reserveID = "open"; } // Return our output to ajax and exit echo "$spots|$reserveID"; exit(); } //**** END reserving number of seats //**** START register/buy seats if (isset($_POST['confirm'])){ // Initialize our display output to NULL $response = ""; // Clean incoming POST // $rid is our id from when we inserted into the reserve table // $name is the users name // $tableNumber is the table number they are paying for // $numSeats is the number of seats they are paying for $rid = preg_replace('#[^0-9]#', '', $_POST['confirm']); $name = preg_replace('#[^a-z0-9 ]#i', '', $_POST['n']); $tableNumber = preg_replace('#[^a-z0-9 ]#i', '', $_POST['tn']); $numSeats = preg_replace('#[^0-9]#', '', $_POST['ns']); // If POSTS are empty after cleaning, exit if($rid == "" || $name == "" || $tableNumber == "" || $numSeats == ""){ exit(); } // See if reservation has timed out by seeing if the... // id from when we inserted into the reserve table still exists $sql = "SELECT id FROM reserves WHERE id='$rid' LIMIT 1"; $query = mysqli_query($connect, $sql) or die (mysqli_error($connect)); $quick_check = mysqli_num_rows($query); // If that id has timed out and been removed due to.... // them being slow, we need to see if their # of seats... // is still available for that table if ($quick_check != 1){ // Select with table name and number of seats they want $sql = "SELECT id, avail FROM available WHERE tablenum='$tableNumber' AND avail>='$numSeats' LIMIT 1"; $query = mysqli_query($connect, $sql) or die (mysqli_error($connect)); $quick_check2 = mysqli_num_rows($query); // If not available any longer... // They farted around too long // tell them they are too slow if ($quick_check2 == 0){ $confirmedStatus = "false"; $response = "Your reservation expired, please start over by refreshing page"; echo "$confirmedStatus|$response"; exit(); } else { // They are still available even though they timed out // Grab that id and number available so they can buy them while ($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){ $id = $row['id']; $avail = $row['avail']; } // Math to calculate new seats available after purchase $availNow = $avail - $numSeats; // Update table to reflect seats that were just bought $sql = "UPDATE available SET avail='$availNow' WHERE id='$id' LIMIT 1"; $query = mysqli_query($connect, $sql); // Insert directly into confirms table after they pay // We are skipping the reserves table as we don't need it $sql = "INSERT INTO confirms(tablename,numseats,person) VALUES ('$tableNumber','$numSeats','$name')"; $query = mysqli_query($connect, $sql); // Build display output $confirmedStatus = "true"; $response = 'only needed for fail'; // Return display output to ajax and exit echo "$confirmedStatus|$response"; exit(); } } else { // They have not timed out so let them buy the seats // Insert them in the confirms table $sql = "INSERT INTO confirms(tablename,numseats,person) VALUES ('$tableNumber','$numSeats','$name')"; $query = mysqli_query($connect, $sql); // Delete the reservation data as they have paid $sql = "DELETE FROM reserves WHERE id='$rid' LIMIT 1"; $query = mysqli_query($connect, $sql); // Build display output $response = 'only needed for fail'; $confirmedStatus = "true"; // Return display output to ajax and exit echo "$confirmedStatus|$response"; exit(); } } //**** END register/buy seats //**** START clear reservations if (isset($_POST['clearRes'])){ $rid = preg_replace('#[^0-9]#', '', $_POST['clearRes']); $clean = "SELECT r.*, a.avail FROM reserves AS r LEFT JOIN available AS a ON a.tablenum = r.tablenumber WHERE r.id ='$rid'"; $freequery = mysqli_query($connect, $clean) or die (mysqli_error($connect)); $num_check = mysqli_num_rows($freequery); if ($num_check != 0){ while ($row = mysqli_fetch_array($freequery, MYSQLI_ASSOC)){ $dI = $row['tablenumber']; $dS = $row['numseats']; $dIdRow = $row['id']; $originalavail = $row['avail']; // Add back the expired reserves $updateAvailable = $originalavail + $dS; // Delete the reserves $sql3 = "DELETE FROM reserves WHERE tablenumber='$dI' LIMIT 1"; $query3 = mysqli_query($connect, $sql3); // Update the database with newly available seats $sql3 = "UPDATE available SET avail='$updateAvailable' WHERE tablenum='$dI' LIMIT 1"; $query3 = mysqli_query($connect, $sql3); } } } //**** END clear reservations ?>
reservations.js
// START initial state of common vars var processStage = "open"; var reservationId = "open"; // END initial state of common vars //**** START get buttons for availble seats function showSeats(tbl){ // If they have unfinished reservation, cancel it if (processStage == "closed"){ cancelReserve(reservationId); } // tbl gives us -> tbid_3 // We only need the (3) part so we split it var a = tbl.split("_"); // Add placeholder text to seatBtns div document.getElementById("returnData").innerHTML = 'Double Checking Availability ...'; // For in depth of following code // visit http://www.developphp.com/view.php?tid=1185 var hr = new XMLHttpRequest(); // PHP processing script url var url = "resProcessing.php"; hr.open("POST", url, true); hr.setRequestHeader("Content-type", "application/x-www-form-urlencoded"); hr.onreadystatechange = function() { if(hr.readyState == 4 && hr.status == 200) { var return_data = hr.responseText; // Send seat buttons to div id seatBtns document.getElementById("returnData").innerHTML = return_data; } } // Send getSeatBtns=3 to PHP processing script hr.send("getSeatBtns="+a[1]); } //**** END get buttons for availble seats //**** START reserve number of seats function reserveSeats(numseats){ processStage = "closed"; // numseats gives us -> tbid_2_3 // 2 is the id of the table(id not table name) // 3 is the number of seats they want // Split our data at the _ var a = numseats.split("_"); // Add text while request processes to buyNow div document.getElementById("returnData").innerHTML = 'Double Checking Availability ...'; // For in depth of following code // visit http://www.developphp.com/view.php?tid=1185 var hr = new XMLHttpRequest(); // PHP processing script url var url = "resProcessing.php"; hr.open("POST", url, true); hr.setRequestHeader("Content-type", "application/x-www-form-urlencoded"); hr.onreadystatechange = function() { if(hr.readyState == 4 && hr.status == 200) { var return_data = hr.responseText.split("|"); reservationId = return_data[1]; // Return data sent to buyNow div document.getElementById("returnData").innerHTML = return_data[0]; } } // Send reserve=2&num=3 to PHP processing script hr.send("reserve="+a[1]+"&num="+a[2]); } //**** END reserve number of seats //**** START register/buy seats function confirmSeats(){ // Get form values var name = document.getElementById("name").value; var tnum = document.getElementById("tableNumber").value; var nseats = document.getElementById("numSeats").value; var rid = document.getElementById("reserveID").value; if(name == "" || tnum == "" || nseats == "" || rid == ""){ return false; } // Compile our data from form to send to processing var confData = "confirm="+rid+"&n="+name+"&tn="+tnum+"&ns="+nseats; // For in depth of following code // visit http://www.developphp.com/view.php?tid=1185 var hr = new XMLHttpRequest(); // PHP processing script url var url = "resProcessing.php"; hr.open("POST", url, true); hr.setRequestHeader("Content-type", "application/x-www-form-urlencoded"); hr.onreadystatechange = function() { if(hr.readyState == 4 && hr.status == 200) { var return_data = hr.responseText.split("|"); // They farted around too long and someone else got the seats if (return_data[0] == "false"){ // Display fail message document.getElementById("returnData").innerHTML = return_data[1]; var processStage = "open"; var reservationId = "open"; } else { // They got the seats succesfully var processStage = "open"; var reservationId = "open"; // Alert the success message alert ("You own the seats"); // Reload the page window.location = 'auction.php'; } } } // Send our compiled data hr.send(confData); } //**** END register/buy seats // START cancel reservation button function cancelReserve(resId){ // For in depth of following code // visit http://www.developphp.com/view.php?tid=1185 var hr = new XMLHttpRequest(); // PHP processing script url var url = "resProcessing.php"; hr.open("POST", url, true); hr.setRequestHeader("Content-type", "application/x-www-form-urlencoded"); // Send reservation id hr.send("clearRes="+resId); window.location = 'auction.php'; } // END cancel reservation button
auction.php
<?php //**** START Database connection script // Connect to database script include ("connect.php"); //**** END Database connection script //**** START Clean out expired reservations //**** Amount of time that reservations are held is set here 3 minutes // Get list of expired seats from 1 table and original number of seats from another table $clean = "SELECT r.*, a.avail FROM reserves AS r LEFT JOIN available AS a ON a.tablenum = r.tablenumber WHERE r.restime < (NOW() - INTERVAL 3 MINUTE)"; $freequery = mysqli_query($connect, $clean) or die (mysqli_error($connect)); $num_check = mysqli_num_rows($freequery); if ($num_check != 0){ while ($row = mysqli_fetch_array($freequery, MYSQLI_ASSOC)){ $dI = $row['tablenumber']; $dS = $row['numseats']; $dIdRow = $row['id']; $originalavail = $row['avail']; // Add back the expired reserves $updateAvailable = $originalavail + $dS; // Delete the reserves $sql3 = "DELETE FROM reserves WHERE tablenumber='$dI' LIMIT 1"; $query3 = mysqli_query($connect, $sql3); // Update the database with newly available seats $sql3 = "UPDATE available SET avail='$updateAvailable' WHERE tablenum='$dI' LIMIT 1"; $query3 = mysqli_query($connect, $sql3); } } //**** END Clean out expired reservations //**** START Get initial state of tables with seats after clean up // Initialize our output to NULL $chart = ""; // Query for tables with seats available $sql = "SELECT * FROM available"; $query = mysqli_query($connect, $sql) or die (mysqli_error($connect)); // Loop and get all the data while ($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){ // Assign table data to variables $id = $row['id']; $tablenum = $row['tablenum']; $avail = $row['avail']; $price = $row['price']; // Build display output // Display for tables with no available seats if ($avail == 0){ $chart .= '<div class="full"><div class="numSeats">0 Seats Available</div></div>'; } else { // Display for tables with available seats - clickable inner div $chart .= '<div class="available"><div id="tbl_'.$id.'" class="numSeats" onClick="showSeats(this.id)">'.$avail.' Seats Available</div></div>'; } } $chart .= '<div class="clear">'; //**** END Get initial state of tables with seats after clean up ?> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <link rel="stylesheet" href="auction.css"> <script src="reservations.js"></script> </head> <body> <div id="wrapper"> <div id="stage"></div> <div id="seats"> <?php echo $chart; ?> </div> <div id="returnData">Click Available Tables To Get Seats</div> </div> </body> </html>
You can read more articles by LewisAnderson by clicking this link:
Articles written by LewisAnderson

User Notes And Comments ↓

Monday January 19, 2015 04:14:20 AM
promiselxg said:thanks Lewis....We really Appreciate all your efforts
Monday January 19, 2015 12:33:57 PM
hsn0 said:wow, great [happy] its very nice thing looks like, I really appreciate it. but I didn't what really it is. :P
Monday January 19, 2015 12:34:23 PM
hsn0 said:I mean I see it.
Wednesday January 21, 2015 12:21:56 PM
CodeWizzy said::D
Friday July 03, 2015 05:25:06 AM
Paul said:Thanks.
Sunday November 15, 2015 02:34:23 AM
phpsystems said:can you help with project lewis, got spaghetti code here, good news it's small project.any skype?
© 2015 webintersect.com
There is a top-shelf free educational course of videos associated with this domain, all about how to build custom communities and social network websites. There are over 45 videos in the course totaling just over 10 hours. We also archived the deprecated course material here for you to reference on the source files page.