php - PHP数据库表格展示
问题描述
我有一个需要更正的作业。我的代码具有所有工作功能。但是，我需要我的代码能够在页面最初打开和加载时显示我的预订表。目前，该表格仅在添加另一个航班时显示。我相信我遗漏了几行代码，或者没有将代码放在正确的部分。请帮忙。
> <html>
<head>
<title>HW09</title>
</head>
<body>
<h1>HW09 - Airline Reservation</h1>
<form>
<h3>Find a flight:</h3>
<?php
include("config.php");
$connect=mysqli_connect("localhost",$username,$dbpassword,"CHA") or die ("Cannot open database");
$sql="SELECT * FROM `AirportList` ";
$result = mysqli_query($connect,$sql);
print "<label>From: </label>";
print "<select name='from'>";
while ($row = $result -> fetch_assoc()) {
print "<option value='" . $row['AirportCode'] . "'>" . $row['AirportName'] . "</option>\n";
}
print "</select>\n";
print "<br>";
$result = mysqli_query($connect,$sql);
print "<label>To: </label>";
print "<select name='to'>";
while ($row = $result -> fetch_assoc()) {
print "<option value='" . $row['AirportCode'] . "'>" . $row['AirportName'] . "</option>\n";
}
print "</select>\n";
print "<br>";
print "<label>Date: </label>";
print "<input type=\"date\" name=\"datee\" id=\"datee\" >";
print"<br>
<input type='submit' value='Find Flights'>
</br>";
$de = $_GET['datee'];
$d = str_replace("-","",$de);
$from = $_GET['from'];
$to = $_GET['to'];
$sql2 = "SELECT * FROM `Flights` WHERE `Date` LIKE '$d' AND `Takeoff` LIKE '$from' AND `Landing` LIKE '$to' ";
print "<br />";
if ($_GET) {
print "<p>Let's find a reservation</p>";
$result2 = mysqli_query($connect,$sql2);
$count = 0;
while ($num = $result2 -> fetch_assoc()) {
$count = $count + 1;
}
print "<p>We have " . $count . " options</p><br />";
$result2 = mysqli_query($connect,$sql2);
if ($_GET['datee']) {
print "
<table border=1>
<tr>
<th>Select</th>
<th>Seats</th>
<th>From</th>
<th>To</th>
<th>Time</th>
<th>Flight#</th>
</tr>";
}
while ($info = $result2 -> fetch_assoc()) {
print"
<form>
<tr>
<td>
<input type='hidden' name='addflight' value=" . $info['Serial'] . ">
<input type='submit' name='Reserve' value='Reserve'>
</td>
<td>
<select name='seats'>";
for ($x = 1; $x <= 11; $x++) {
print"<option value= " . $x . ">" . $x . "</option>";
}
print"</td>
<td>" . $info['Takeoff'] . "</td><td>" . $info['Landing'] . "<td>" . $info['Time'] . "</td><td>" . $info['Flightnum'] . "</td></tr>
</form>";
}
print "</table>";
if (isset($_GET['Reserve'])) {
$connect3=mysqli_connect("localhost",$username,$dbpassword,"jasonlalaki_HW09") or die ("Cannot open database");
$sql3="INSERT INTO `Reservations` (`ResSerial`, `Seats`) VALUES (" . $_GET['addflight'] . ", " . $_GET['seats'] . ");";
$result3 = mysqli_query($connect3,$sql3);
$sql4="SELECT * FROM `Reservations` ";
$result4 = mysqli_query($connect3,$sql4);
print"
<table border=1>
<tr>
<th>Delete</th>
<th>Date</th>
<th>Time</th>
<th>Flight</th>
<th>From</th>
<th>To</th>
<th>Seats</th>
</tr>";
while ($row1 = $result4 -> fetch_assoc()) {
$sql5="SELECT * FROM `Flights` WHERE `Serial` = " . $row1['ResSerial'];
$result5 = mysqli_query($connect,$sql5);
while ($info2 = $result5 -> fetch_assoc()) {
print"
<form>
<tr>
<td>
<input type='submit' name='Delete' value='Delete'>
</td>
<td>" . $info2['Date'] . "</td><td>" . $info2['Time'] . "</td><td>" . $info2['Flightnum'] . "</td><td>" . $info2['Takeoff'] . "</td><td>" . $info2['Landing'] . "</td><td style=\"text-align: right;\">" . $row1['Seats'] . "</td></tr>
</form>";
}
}
print"
</table>";
}
}
?>
</form>
</body></html>
解决方案
在稍微研究了您的代码之后，我决定重写它 - 包括表的命名约定，以向您展示“最佳实践”示例。我选择的表结构肯定不是正确的，但我认为这里没有太大的相关性。
建议：
- 您应该始终避免使用 PHP 代码打印 HTML 代码。例如，避免这样的片段：
print "<label>From: </label>";. 像往常一样编写 HTML 代码：<label>From: </label>. - 在某些情况下，必须在 HTML 控件（值、属性等）中打印 PHP 代码。然后尝试只打印 PHP 变量，不打印复杂代码。而不是
<input value="<?php echo $selectedFlightDate ?? ''; ?>" ... />优化到<?php $defaultFlightDate = $selectedFlightDate ?? ''; ?> <input value="<?php echo $defaultFlightDate; ?>" ... />. - 尝试将查询 PHP 代码的数据库与页面的其余部分分开。查询结果应该以数组的形式获取，稍后将在页面中轻松使用。
- 不要犹豫，为变量、常量、函数等使用可发音且具有意图的名称
<input type="date" name="datee">。因此，不要使用<input type="date" name="flightDate">. 或者，在 PHP 中，而不是$de = $_GET['datee'];使用$flightDate = $_GET['flightDate'];. - 您正在使用 MySQLi 扩展。我建议你使用它的面向对象的类和方法，而不是它的过程函数。例如，
$connection = mysqli_connect(/* args list */);应该使用$connection = new mysqli(/* args list */);（面向对象的风格）而不是（过程风格）。请参阅文档。 - 您正在使用 MySQLi 数据库扩展，但我强烈建议您尽快切换到PDO 扩展！
- 为了避免恶意 SQL 注入，您应该始终使用所谓的预准备语句。您会注意到我从未使用过
mysqli_query，因为它与报表准备过程不兼容。我对我的代码进行了很多评论，尤其是在我准备用于获取航班列表的 SQL 语句时（SELECT * FROM flights WHERE ...）。
代码：
- 用你的更改我的数据库凭据。
- 我将字符串“_test”附加到所有表名以快速创建它们而不影响您的表名和测试。
连接.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);
索引.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>
样式.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; }
使用的表定义和测试数据：
“airports_test”表：
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 |
“航班测试”表：
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”表：
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|
--|---------|----|
资源：