PHP MySQL Availability System

MySQL database
#1

I am having a little trouble with a part of my project. This part requires me to display my ‘booked’ table which consists of all patients that have booked an appointment and these appointments these can be confirmed or not. Now I need another table underneath with the 3 possible doctors names (Curly, Moe and Larry). This bottom table basically shows which doctor is available at which time of the day (colour coded).

To make this more clear the table will have Monday to Friday headings across the top and down the side headings 9-10 AM, 10-11 AM through to 5 PM. If for example Larry is available at 9-10 AM his name text should be green - if not it should be in red text.

I have a cinema booking system that our lecturer gave in lab work which is a little similar. I have managed to get the top table working but I do not know how to get the bottom doctors availability table to automatically place doctors names in the appropriate available slots.

Also I have been trying to add the time headings going down the left but I’m unsure how to do it? I will post my php and javascript code below. If anyone can help me or point me to the right direction to research in order to complete this task.

Sorry if there is any confusion, it wouldn’t let me add any pictures which would of made it easier to explain.

PHP

[php]<?php

include_once ‘functions.php’;

// information about the cinema:
$nrows = 9;
$ncolumns = 5;

// a variable to say whether the user has tried submitting form data before:
$submitted = false;

// check the superglobals for previously submitted data:
if (isset($_GET[‘requests’]))
{
// get the list of requests that were updated:
$requests = json_decode($_GET[‘requests’]);

if (isset($_GET['seats']))
{
    // get the list of associated seats:
    $seats = json_decode($_GET['seats']);
    $submitted = true;
}

}

if ($submitted)
{
////////////////////////////////////////////////////////////////////////////////////////////////////
/////// WARNING: about to update tables with no server-side sanitisation, or validation. ///////////
/////// Simultaneous changes by multiple users could cause booking rules to be violated. ///////////
////////////////////////////////////////////////////////////////////////////////////////////////////

$fail = ""; 
//$fail = testRequestedBookingsAreAcceptable($requests, $seats); <-- can you implement this?

}

// if we’ve already submitted good data, then use it to update the tables:
if ($submitted && ($fail == “”))
{
// use any HTTP GET data we received to update the tables:
for ($i=0; $i<count($requests); $i++)
{
$temp_r = explode("-", $requests[$i]);

    if ($seats[$i] == null)
    {
        // an existing booking was removed: delete it from the table:
        $query = "DELETE FROM bookings WHERE id=$temp_r[1]";
    }
    else
    {
        // a booking was created, or changed: insert or overwrite it:
        $temp_s = explode("-", $seats[$i]);
        
        // insert the new booking, overwriting any existing one for this customer:
        $query = "INSERT INTO bookings (id, brow, bcolumn) VALUES ($temp_r[1], $temp_s[1], $temp_s[2]) 
                    ON DUPLICATE KEY UPDATE brow=VALUES(brow), bcolumn=VALUES(bcolumn)";
    }

    // make the change to the database:
    $result = mysql_query($query);

    // did we fail?:
    if (!$result)
    {
        die("Database access failed: " . mysql_error());
    }
}

}

/////////////////////////////////////////////////////////////////////
/////////////// Output our HTML and JavaScript //////////////////////
/////////////////////////////////////////////////////////////////////

/*** (Re-)Display the requests and bookings data using HTML ***/

echo “<!doctype html>\n”;
echo “\n”;
echo “\n”;
// we’ll be using jQuery:
echo “\n”;
// and a style sheet:
echo “\n”;
echo “Advanced Web Assignment\n”;
echo “Advanced Web Assignment\n”;

// First: show the requests

// construct a MySQL query string:
$query = “SELECT * FROM booking”;

// get all requests from the database:
$result = mysql_query($query);

// did we fail?:
if (!$result)
{
die("Database access failed: " . mysql_error());
}

// show all the requests in a table:
echo “

\n”;
echo “\n”;
echo “\n”;
$requests = array();
for ($i=0; $i<mysql_num_rows($result); $i++)
{
$row = mysql_fetch_row($result);
$requests[] = $row;
// each row in the table body has an id of: “request-”:
echo “\n”;
}
echo “\n”;
echo “
Surname ID Am OR Pm Date Requested Doctor Alt Am OR Pm Alt Date Alt Doctor Booked
$row[0] $row[1] $row[2] $row[3] $row[4] $row[5] $row[6] $row[7] $row[8]
\n”;
echo “\n”;

echo “\n”;

// Second: show the bookings

// construct a MySQL query string:
$query = “SELECT * FROM bookings”;

// get all bookings from the database:
$result = mysql_query($query);

// did we fail?:
if (!$result)
{
die("Database access failed: " . mysql_error());
}

// take a PHP copy of the bookings table…:
$rows = array();
$columns = array();
$ids = array();
for ($i=0; $i<mysql_num_rows($result); $i++)
{
$row = mysql_fetch_row($result);
$ids[] = $row[0];
$rows[] = $row[1];
$columns[] = $row[2];
}

//… and use it to make a visualisation of the cinema using an HTML table:
echo “

\n”;
echo "\n"; echo "\n"; for ($i=0; $i<$nrows; $i++) { echo ""; for ($j=0; $j<$ncolumns; $j++) { // presume this seat wasn't booked: $booked = -1; 
    // search for it in the bookings data:
    for ($k=0; $k<count($ids); $k++)
    {
        if ($i == $rows[$k] && $j == $columns[$k])
        {
            // retain the ID of its owner if it has been booked:
            $booked = $ids[$k];
            break;
        }
    }

    // seats have and ID of: seat-<row>-<column>, and there is a unique class for each row and each column

    // if the seat is booked...: 
    if ($booked>0)
    {
        //... add it to the "booked" class:
        echo "<td id='seat-$i-$j' class='row-$i column-$j booked'><b></b></td>\n";
        echo "<script>\n";
        //... store the ID of the associated customer in this table cell:
        echo "$('.row-$i.column-$j').data('request', 'request-$booked')\n";
        //... store the seat number in the customer's row of the request table:
        echo "$('#request-$booked').data('seat', 'seat-$i-$j').addClass('booked')\n";
        //... and insert their seat number into the final row:
        echo "$('#request-$booked > td:last').text('seat-$i-$j')\n";
        echo "</script>\n";
    }
    else
    {
        // if it's not booked:

/* echo "











\n";
*/
echo “

\n”;
echo “\n”;
} 
}
echo "</tr>\n";

}
echo “

\n”;
echo “
Monday Tuesday Wednesday Thursday Friday
05-12-2016 9-10AM 10-11AM 12-1PM 1-2PM 3-4PM 4-5PM
Curly, Moe, Larry
\n”;

echo “\n”;
echo “\n”;
echo “\n”;

// if the user has just clicked the submit button, print a success/failure message directly underneath:
if ($submitted && ($fail == “”))
{
echo “Booking Successful\n”;

}
else if ($submitted)
{
echo “Booking Failed\n”;
}
else
{
echo “\n”;
}

echo “\n”;
echo “\n”;

// now all the JavaScript:
echo <<<_END[/php]

[code] _END; ?>[/code]

#2

You are using Deprecated code that will not work at all in the latest version of PHP. You need to use PDO with prepared statements.

Your code is alo vulnerable to SQL Injection. You never, ever send user supplied data directly to the database.

Sponsor our Newsletter | Privacy Policy | Terms of Service