Consider this working example which uses HTML, PHP, MySQL and one flat Javascript command:
index.php
<?php
// Include the class that handles the database interactivity
require_once 'Database.php';
// Initialise the database
$Database = new Database();
// Get the list of Zones
$Zones = $Database->getZones();
// ZONE
if (isset($_POST['Zone'])) {
// You could validate and whitelist entries here if you wanted
if (!in_array($_POST['Zone'], $Zones)) {
$response = 'Sorry but that was not a valid selection';
}
// Passed validation
else {
// Get the corresponding Seats
$Seats = $Database->getSeats($_POST['Zone']);
// Store the Zone selection
$selectedZone = $_POST['Zone'];
// Set the response
$response = 'Viewing seats for '.$_POST['Zone'];
}
}
// SEAT
if (isset($_POST['Seat'])) {
printf('Zone that was chosen: '.$selectedZone);
printf('<br>');
printf('Seat that was chosen: '.$_POST['Seat']);
exit;
}
// This deals with initally loading the page
if (!isset($_POST['Zone']) && !isset($_POST['Seat'])) {
// Open variables
$response = '';
$selectedZone = 'n/a';
$Seats = array();
}
// You could move the code from here onwards into another file
// So you have a template like:
// require_once 'view.php'; which has a form that posts to index.php
// Start generating the page html
$page = '
<!DOCTYPE html>
<html>
<head>
<title>Awesome Page!</title>
</head>
<body>
<form method="POST" action="index.php">
';
// If theres a response to display
if (strlen($response) > 0) {
$page .= '
<p>'.$response.'</p>
';
}
// Dealing with the Zone selection
$page .= '
<p>Zones</p>
<select name="Zone" onchange="this.form.submit()">
<option value="">Please select an option</option>
';
// Itterate over the Zones
foreach ($Zones as $name) {
// If this is the selected Zone
if ($selectedZone == $name) {
$page .= '
<option selected value="'.$name.'">'.$name.'</option>
';
}
// This is not a selected Zone
else {
$page .= '
<option value="'.$name.'">'.$name.'</option>
';
}
}
$page .= '
</select>
';
// Dealing with the Seat selection
if (count($Seats) > 0) {
$page .= '
<p>Seats</p>
<select name="Seat" onchange="this.form.submit()">
<option value="">Please select an option</option>
';
// Itterate over the Seats
foreach ($Seats as $RowNumber) {
$page .= '
<option value="'.$RowNumber.'">Row Number: '.$RowNumber.'</option>
';
}
$page .= '
</select>
';
}
// Theres no Seats yet as Zone is not selected
else {
$page .= '
<p>Please select a Zone first.</p>
';
}
$page .= '
</form>
</body>
</html>
';
// Display the page
echo $page;
Database.php
<?php
class Database
{
// Active connection
private $link;
// This fires when you call new Database();
public function __construct()
{
$this->doConnect();
}
private function doConnect()
{
// Define database details
$DBHost = 'localhost';
$DBUser = 'username';
$DBPass = 'password';
$DBName = 'database_name';
// Create a database connection for PHP to use
$this->link = mysqli_connect($DBHost, $DBUser, $DBPass);
// Preform from tasks to ensure the connection is active
if (!$this->link) {
echo 'Error: Unable to connect to MySQL' . '<br>';
echo 'Debugging errno: ' . mysqli_connect_errno() . '<br>';
echo 'Debugging error: ' . mysqli_connect_error() . '<br>';
exit;
}
// Sets encoding type to uft8
if (!mysqli_set_charset($this->link, 'utf8')) {
$this->processError();
}
// Set database that is in use (makes queries shorter to write)
if (!mysqli_select_db($this->link, $DBName)) {
$this->processError();
}
}
public function getZones()
{
// Stores the result
$Zones = array();
// Build query
$query = 'SELECT `name` ';
$query .= 'FROM `Zone` ';
// Prepare the statement
if (!$stmt = $this->link->prepare($query)) { $this->processError(); }
// Execute the query
if (!$stmt->execute()) { $this->processError(); }
// Bind variable to query values
if (!$stmt->bind_result($name)) { $this->processError(); }
// Itterate over the rows
while ($stmt->fetch()) {
// Add this Zones name to the result
$Zones[] = $name;
}
// Close the statement
$stmt->close();
// Return the result
return $Zones;
}
public function getSeats($selectedZone)
{
// Stores the result
$Seats = array();
// Build query
$query = 'SELECT `RowNumber` ';
$query .= 'FROM `Seat` ';
$query .= 'WHERE `Zone` = ? ';
// Prepare the statement
if (!$stmt = $this->link->prepare($query)) { $this->processError(); }
// Bind in form values to prevent sql injection
if (!$stmt->bind_param('s', $selectedZone)) { processError($link); } // NB: Assumed this to be a string but might be an integer, if so use i instead of s
// Execute the query
if (!$stmt->execute()) { $this->processError(); }
// Bind variable to query values
if (!$stmt->bind_result($RowNumber)) { $this->processError(); }
// Itterate over the rows
while ($stmt->fetch()) {
// Add this RowNumber to the Seats
$Seats[] = $RowNumber;
}
// Close the statement
$stmt->close();
// Return the result
return $Seats;
}
private function processError()
{
echo 'Error: Unable to connect to MySQL' . '<br>';
echo 'Debugging errno: ' . $this->link->errno . '<br>';
echo 'Debugging error: ' . $this->link->error . '<br>';
exit;
}
}
Essentially, we have our PHP bit at the top that deals with getting data and form submissions, then we have the html template underneath which includes a form that submits to itself.
The template (view type of logic) will make use of the data provided by the first bit (controller type of logic) which is assisted by the Database class (model type of logic).
So this is at heart a very simple implementation of the MVC pattern.
Javascript is used very simply so as to detect changes to the value of the selection and then submit the form: onchange="this.form.submit()"
I tried to comment the code as much as possible so as to increase the understanding that you have for it but if you do have any questions, please feel free to ask :)
