After studying your code a bit, I decided to rewrite it - including the naming conventions of the tables, to show you a "best practice" example. The table structures chosen by me are certainly not the correct ones, but I didn't consider this here of big relevance.
Suggestions:
- You should always avoid printing HTML code using PHP code. For example, avoid snippets like this:
print "<label>From: </label>";. Write HTML code as normal: <label>From: </label>.
- There are the cases where PHP code must be printed inside HTML controls (values, attributes, etc). Then try to print only PHP variables, no complex code. Instead of
<input value="<?php echo $selectedFlightDate ?? ''; ?>" ... /> optimize to <?php $defaultFlightDate = $selectedFlightDate ?? ''; ?> <input value="<?php echo $defaultFlightDate; ?>" ... />.
- Try to separate the database querying PHP codes from the rest of the page. The querying results should be fetched in arrays, which will easily be used later in the page.
- Don't hesitate to use pronounceable and intention-revealing names for variables, constants, functions, etc. So, instead of
<input type="date" name="datee"> use <input type="date" name="flightDate">. Or, in PHP, instead of $de = $_GET['datee']; use $flightDate = $_GET['flightDate'];.
- You are using MySQLi extension. I suggest you to use the object oriented classes and methods of it, instead of the procedural functions of it. For example, instead of
$connection = mysqli_connect(/* args list */); (procedural style) you should use $connection = new mysqli(/* args list */); (object oriented style). See the docs.
- You are using MySQLi database extension, but I strongly advice you to switch to the PDO extension as soon as possible!
- In order to avoid malicious SQL injections, you should always use the so-called prepared statements. You'll notice that I never used
mysqli_query, because it's not compatible with the process of statements preparing. I commented my code a lot, especially where I prepared the SQL statement for fetching the flights list (SELECT * FROM flights WHERE ...).
Code:
- Change my db credentials with yours.
- I appended the string "_test" to all table names for quickly creating them without affecting yours, and testing.
connection.php:
<?php
/*
* This page contains the code for creating a mysqli connection instance.
*/
// Db configs.
define('HOST', 'localhost');
define('PORT', 3306);
define('DATABASE', 'tests');
define('USERNAME', 'tests');
define('PASSWORD', 'tests');
// Error reporting.
error_reporting(E_ALL);
ini_set('display_errors', 1); /* SET IT TO 0 ON A LIVE SERVER !!! */
/*
* Enable internal report functions. This enables the exception handling.
*
* @link http://php.net/manual/en/class.mysqli-driver.php
* @link http://php.net/manual/en/mysqli-driver.report-mode.php
* @link http://php.net/manual/en/mysqli.constants.php
*/
$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = (MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
// Create a new db connection.
$connection = new mysqli(HOST, USERNAME, PASSWORD, DATABASE, PORT);
index.php:
<?php
require 'connection.php';
/*
* ==================================================================
* Define flags for the various operations. Can be very useful later.
* ==================================================================
*/
$searchingFlightsStarted = false;
$flightReservationStarted = false;
$reservationDeletionStarted = false;
/*
* =========================================================
* Operations upon submission of form "searchingFlightsForm"
* =========================================================
*/
if (isset($_POST['searchFlightsButton'])) {
$searchingFlightsStarted = true;
/*
* Read submitted values.
*/
$selectedStartingAirport = $_POST['startingAirport'] ?? '';
$selectedDestinationAirport = $_POST['destinationAirport'] ?? '';
// I didn't applied any formatting to the date value. Do it, if you need to.
$selectedFlightDate = $_POST['flightDate'] ?? '';
/*
* Validate submitted values.
*/
if (empty($selectedStartingAirport)) {
$errorMessages[] = 'Please select a starting point.';
}
if (empty($selectedDestinationAirport)) {
$errorMessages[] = 'Please select a destination.';
}
if (empty($selectedFlightDate)) {
$errorMessages[] = 'Please select a date for the flight.';
}
/*
* If no validation errors yet, proceed with searching flights.
* Note the use of the prepared statement in order to avoid malicious SQL injections.
*/
if (!isset($errorMessages)) {
/*
* The SQL statement to be prepared. Notice the so-called markers,
* e.g. the "?" signs. They will be replaced later with the
* corresponding values when using mysqli_stmt::bind_param.
*
* @link http://php.net/manual/en/mysqli.prepare.php
*/
$sql = 'SELECT *
FROM flights_test
WHERE
date = ? AND
takeoff LIKE ? AND
landing LIKE ?';
/**
* Prepare the SQL statement for execution, but
* ONLY ONCE - read the docs to find out why.
*
* @link http://php.net/manual/en/mysqli.prepare.php
*/
$statement = $connection->prepare($sql);
$boundSelectedStartingAirport = '%' . $selectedStartingAirport . '%';
$boundSelectedDestinationAirport = '%' . $selectedDestinationAirport . '%';
/*
* Bind variables for the parameter markers ("?") in the
* SQL statement that was passed to prepare(). The first
* argument of bind_param() is a string that contains one
* or more characters which specify the types of the
* corresponding bind variables (string, integer, etc).
*
* @link http://php.net/manual/en/mysqli-stmt.bind-param.php
*/
$statement->bind_param(
'sss',
$selectedFlightDate,
$boundSelectedStartingAirport,
$boundSelectedDestinationAirport
);
/*
* Execute the prepared SQL statement.
* When executed any parameter markers which exist,
* e.g. each "?" character, will automatically be
* replaced with the appropriate data.
*
* @link http://php.net/manual/en/mysqli-stmt.execute.php
*/
$statement->execute();
/*
* Get the result set from the prepared statement.
*
* NOTA BENE:
* Available only with mysqlnd ("MySQL Native Driver")! If this
* is not installed, uncomment "extension=php_mysqli_mysqlnd.dll" in
* php.ini and restart web server and mysql service. Or use the following instead:
* mysqli_stmt::store_result + mysqli_stmt::bind_result + mysqli_stmt::fetch.
*
* @link http://php.net/manual/en/mysqli-stmt.get-result.php
* @link https://stackoverflow.com/questions/8321096/call-to-undefined-method-mysqli-stmtget-result
*/
$result = $statement->get_result();
// Fetch all found flights and save them in an array for later use.
$foundFlights = $result->fetch_all(MYSQLI_ASSOC);
/*
* Free the memory associated with the result. You should
* always free your result when it is not needed anymore.
*
* @link http://php.net/manual/en/mysqli-result.free.php
*/
$result->close();
/*
* Close the prepared statement. It also deallocates the statement handle.
* If the statement has pending or unread results, it cancels them
* so that the next query can be executed.
*
* @link http://php.net/manual/en/mysqli-stmt.close.php
*/
$statement->close();
}
}
/*
* ==========================================================
* Operations upon submission of form "flightReservationForm"
* ==========================================================
*/
if (isset($_POST['reserveFlightButton'])) {
$flightReservationStarted = true;
/*
* Read submitted values.
*/
$flightIdToReserve = $_POST['flightIdToReserve'];
$seatToReserve = $_POST['seatToReserve'];
/*
* Proceed with the reservation of the selected flight.
* Note the use of the prepared statement.
*/
$sql = 'INSERT INTO reservations_test (
flight_id,
seat
) VALUES (
?, ?
)';
$statement = $connection->prepare($sql);
$statement->bind_param('ii', $flightIdToReserve, $seatToReserve);
$statement->execute();
$statement->close();
$successMessages[] = 'The reservation of the selected flight was successfully performed.';
}
/*
* ============================================================
* Operations upon submission of form "reservationDeletionForm"
* ============================================================
*/
if (isset($_POST['deleteReservationButton'])) {
$reservationDeletionStarted = true;
/*
* Read submitted values.
*/
$reservationIdToDelete = $_POST['reservationIdToDelete'];
/*
* Proceed with the deletion of the selected flight.
* Note the use of the prepared statement.
*/
$sql = 'DELETE FROM reservations_test
WHERE id = ?';
$statement = $connection->prepare($sql);
$statement->bind_param('i', $reservationIdToDelete);
$statement->execute();
$statement->close();
$successMessages[] = 'The selected flight reservation was successfully deleted.';
}
/*
* ===========================================================
* Fetch the airports list (used in the searching comboboxes).
* Note the use of the prepared statement.
* ===========================================================
*/
$sql = 'SELECT * FROM airports_test';
$statement = $connection->prepare($sql);
$statement->execute();
$result = $statement->get_result();
// Fetch all airports and save them in an array for later use.
$airports = $result->fetch_all(MYSQLI_ASSOC);
$result->close();
$statement->close();
/*
* =======================================================
* Fetch the reservations list, which is always displayed.
* Note the use of the prepared statement.
* =======================================================
*/
$sql = 'SELECT
r.id,
r.flight_id,
f.date,
f.time,
f.flight_no,
f.takeoff,
f.landing,
r.seat
FROM reservations_test AS r
LEFT JOIN flights_test AS f ON
r.flight_id = f.id';
$statement = $connection->prepare($sql);
$statement->execute();
$result = $statement->get_result();
// Fetch all reservations and save them in an array for later use.
$reservations = $result->fetch_all(MYSQLI_ASSOC);
$result->close();
$statement->close();
?>
<html>
<head>
<!-- Required meta tags -->
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=yes" />
<title>Demo</title>
<link href="styles.css" rel="stylesheet">
</head>
<body>
<header>
<h1>
Airline Reservation
</h1>
</header>
<section class="messages">
<?php
/*
* Display all error messages.
*/
if (isset($errorMessages)) {
foreach ($errorMessages as $errorMessage) {
?>
<div class="error">
<?php echo $errorMessage; ?>
</div>
<?php
}
}
/*
* Display all success messages.
*/
if (isset($successMessages)) {
foreach ($successMessages as $successMessage) {
?>
<div class="success">
<?php echo $successMessage; ?>
</div>
<?php
}
}
?>
</section>
<section>
<header>
<h3>
Search flights:
</h3>
</header>
<article>
<form id="searchingFlightsForm" method="post" action="">
<div class="form-group">
<label>From: </label>
<select name="startingAirport">
<option value="">-- Select a starting point --</option>
<?php
if ($airports) {// if any records available
foreach ($airports as $airport) {
$airportCode = $airport['code'];
$airportName = $airport['name'];
$attributeSelected = ($airportCode === $selectedStartingAirport) ?
'selected' :
'';
?>
<option value="<?php echo $airportCode; ?>" <?php echo $attributeSelected; ?>>
<?php echo $airportName; ?>
</option>
<?php
}
}
?>
</select>
</div>
<div class="form-group">
<label>To: </label>
<select name="destinationAirport">
<option value="">-- Select a destination --</option>
<?php
if ($airports) {// if any records available
foreach ($airports as $airport) {
$airportCode = $airport['code'];
$airportName = $airport['name'];
$attributeSelected = ($airportCode === $selectedDestinationAirport) ?
'selected' :
'';
?>
<option value="<?php echo $airportCode; ?>" <?php echo $attributeSelected; ?>>
<?php echo $airportName; ?>
</option>
<?php
}
}
?>
</select>
</div>
<div class="form-group">
<label>Date: </label>
<?php $defaultFlightDate = $selectedFlightDate ?? ''; ?>
<input type="date" id="flightDate" name="flightDate" value="<?php echo $defaultFlightDate; ?>" />
</div>
<div class="form-group">
<label> </label>
<button type="submit" name="searchFlightsButton" class="formButton" value="Search Flights">
Search Flights
</button>
</div>
</form>
</article>
</section>
<?php
$numberOfFoundFlights = isset($foundFlights) ? count($foundFlights) : 0;
?>
<section>
<header>
<h3>
<?php
$foundFlightsMessage = $numberOfFoundFlights > 0 ?
'Found flights: ' . $numberOfFoundFlights . '. Let\'s make a reservation.' :
'No flights found yet.';
echo $foundFlightsMessage;
?>
</h3>
</header>
<?php
if ($numberOfFoundFlights > 0) {
?>
<article>
<table>
<thead>
<tr>
<th>Select</th>
<th>Seat</th>
<th>From</th>
<th>To</th>
<th>Time</th>
<th>Flight #</th>
</tr>
</thead>
<tbody>
<?php
foreach ($foundFlights as $foundFlight) {
$flightId = $foundFlight['id'];
$flightTakeoff = $foundFlight['takeoff'];
$flightLanding = $foundFlight['landing'];
$flightTime = $foundFlight['time'];
$flightNumber = $foundFlight['flight_no'];
?>
<tr>
<td>
<?php
/*
* You are not allowed to enclose a table row in a "form" tag!
* Though, in HTML5, you can define a form for each table row
* wherever you want on the page, and then add the attribute "form"
* to each control residing outside of it, but destined to belong
* to it. The "form" attribute MUST contain the id of the form
* to which the current control should belong. As example see
* the comboboxes named "seatToReserve" bellow.
*/
?>
<form id="flightReservationForm_<?php echo $flightId; ?>" class="flightReservationForm" method="post" action="">
<input type="hidden" name="flightIdToReserve" value="<?php echo $flightId; ?>">
<button type="submit" name="reserveFlightButton" class="columnButton" value="Reserve">
Reserve
</button>
</form>
</td>
<td>
<select name="seatToReserve" form="flightReservationForm_<?php echo $flightId; ?>">
<?php
for ($seat = 1; $seat <= 11; $seat++) {
?>
<option value="<?php echo $seat; ?>">
<?php echo $seat; ?>
</option>
<?php
}
?>
</select>
</td>
<td><?php echo $flightTakeoff; ?></td>
<td><?php echo $flightLanding; ?></td>
<td><?php echo $flightTime; ?></td>
<td><?php echo $flightNumber; ?></td>
</tr>
<?php
}
?>
</tbody>
</table>
</article>
<?php
}
?>
</section>
<?php
$numberOfReservations = $reservations ? count($reservations) : 0;
?>
<section>
<header>
<h3>
<?php
$reservationsMessage = $numberOfReservations > 0 ?
'Reservations:' :
'No reservations available yet.';
echo $reservationsMessage;
?>
</h3>
</header>
<?php
if ($numberOfReservations > 0) {
?>
<article>
<table>
<thead>
<tr>
<th>Delete</th>
<th>Date</th>
<th>Time</th>
<th>Flight</th>
<th>From</th>
<th>To</th>
<th>Seat</th>
</tr>
</thead>
<tbody>
<?php
foreach ($reservations as $reservation) {
$reservationId = $reservation['id'];
$reservationFlightId = $reservation['flight_id'];
$reservationDate = $reservation['date'];
$reservationTime = $reservation['time'];
$reservationFlightNumber = $reservation['flight_no'];
$reservationTakeoff = $reservation['takeoff'];
$reservationLanding = $reservation['landing'];
$reservationSeat = $reservation['seat'];
?>
<tr>
<td>
<form id="reservationDeletionForm_<?php echo $reservationId; ?>" class="reservationDeletionForm" method="post" action="">
<input type="hidden" name="reservationIdToDelete" value="<?php echo $reservationId; ?>">
<button type="submit" name="deleteReservationButton" class="columnButton" value="Delete">
Delete
</button>
</form>
</td>
<td><?php echo $reservationDate; ?></td>
<td><?php echo $reservationTime; ?></td>
<td><?php echo $reservationFlightNumber; ?></td>
<td><?php echo $reservationTakeoff; ?></td>
<td><?php echo $reservationLanding; ?></td>
<td><?php echo $reservationSeat; ?></td>
</tr>
<?php
}
?>
</tbody>
</table>
</article>
<?php
}
?>
</section>
</body>
</html>
styles.css:
*, *::before, *::after { box-sizing: border-box; }
:root { font-size: 16px; }
body { margin: 0; padding: 20px; font-family: "Verdana", Arial, sans-serif; font-size: 1rem; font-weight: 400; background-color: #fff; }
table { border-collapse: collapse; }
table, th, td { border: 1px solid #ccc; }
th, td { padding: 7px; text-align: left; }
th { background-color: #f4f4f4; }
tbody tr:hover { background: yellow; }
.messages { width: 50%; }
.messages .error { background-color: #e83e8c; color: #fff; padding: 5px; margin: 5px; }
.messages .success { background-color: #5cb85c; color: #fff; padding: 5px; margin: 5px; }
#searchingFlightsForm { width: 50%; padding: 20px; background-color: #f4f4f4; }
#searchingFlightsForm .form-group { padding: 5px; }
#searchingFlightsForm label { display: inline-block; min-width: 70px; }
#searchingFlightsForm select { min-width: 240px; }
#searchingFlightsForm input[type="date"] { min-width: 240px; padding: 7px; }
.formButton { padding: 5px 7px; background-color: #009926; color: #fff; }
.columnButton { padding: 3px 5px; background-color: #0086b3; color: #fff; }
form.flightReservationForm { margin: 0; }
form.reservationDeletionForm { margin: 0; }
The used table definitions and testing data:
"airports_test" table:
CREATE TABLE `airports_test` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`code` varchar(100) DEFAULT NULL,
`name` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
id|code|name |
--|----|--------------------|
1|LON |London Airport |
2|BUA |Buenos Aires Flights|
3|BUD |Budapest Airport |
"flights_test" table:
CREATE TABLE `flights_test` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`date` varchar(10) DEFAULT NULL,
`time` time DEFAULT NULL,
`takeoff` varchar(100) DEFAULT NULL,
`landing` varchar(100) DEFAULT NULL,
`flight_no` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
id|date |time |takeoff|landing|flight_no|
--|----------|--------|-------|-------|---------|
1|2020-12-27|15:38:00|BUA |BUD |43245 |
2|2020-12-29|22:44:00|BUD |LON |245 |
3|2020-12-30|05:31:00|BUD |BUA |876643 |
4|2020-12-30|10:00:00|LON |BUD |5443 |
5|2020-12-30|18:45:00|LON |BUD |4287 |
"reservations_test" table:
CREATE TABLE `reservations_test` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`flight_id` bigint(20) unsigned DEFAULT NULL,
`seat` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
id|flight_id|seat|
--|---------|----|
Resources:
