Example 8-53 is another complete database and form program. It presents a search form and then prints an HTML table of all rows in the dishes table that match the search criteria. Like Example 8-28, it relies on the form helper class being defined in a separate FormHelper.php file.
Example 8-53. Program for searching the dishes table
<?php
// Load the form helper class
require ‘FormHelper.php’;
// Connect to the database
try {
$db = new PDO(‘sqlite:/tmp/restaurant.db’);
} catch (PDOException $e) {
print “Can’t connect: ” . $e->getMessage();
exit();
}
// Set up exceptions on DB errors
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Set up fetch mode: rows as objects
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
// Choices for the “spicy” menu in the form
$spicy_choices = array(‘no’,’yes’,’either’);
// The main page logic:
// – If the form is submitted, validate and then process or redisplay
// – If it’s not submitted, display
if ($_SERVER[‘REQUEST_METHOD’] == ‘POST’) {
// If validate_form() returns errors, pass them to show_form()
list($errors, $input) = validate_form();
if ($errors) {
show_form($errors);
} else {
// The submitted data is valid, so process it
process_form($input);
}
} else {
// The form wasn’t submitted, so display
show_form();
}
function show_form($errors = array()) {
// Set our own defaults
$defaults = array(‘min_price’ => ‘5.00’,
‘max_price’ => ‘25.00’);
// Set up the $form object with proper defaults
$form = new FormHelper($defaults);
// All the HTML and form display is in a separate file for clarity
include ‘retrieve-form.php’;
}
function validate_form() {
$input = array();
$errors = array();
// Remove any leading/trailing whitespace from submitted dish name
$input[‘dish_name’] = trim($_POST[‘dish_name’] ?? ”);
// Minimum price must be a valid floating-point number
$input[‘min_price’] = filter_input(INPUT_POST,’min_price’,
FILTER_VALIDATE_FLOAT);
if ($input[‘min_price’] === null || $input[‘min_price’] === false) {
$errors[] = ‘Please enter a valid minimum price.’;
}
// Maximum price must be a valid floating-point number
$input[‘max_price’] = filter_input(INPUT_POST,’max_price’,
FILTER_VALIDATE_FLOAT);
if ($input[‘max_price’] === null || $input[‘max_price’] === false) {
$errors[] = ‘Please enter a valid maximum price.’;
}
// Minimum price must be less than the maximum price
if ($input[‘min_price’] >= $input[‘max_price’]) {
$errors[] = ‘The minimum price must be less than the maximum price.’;
}
$input[‘is_spicy’] = $_POST[‘is_spicy’] ?? ”;
if (! array_key_exists($input[‘is_spicy’], $GLOBALS[‘spicy_choices’])) {
$errors[] = ‘Please choose a valid “spicy” option.’;
}
return array($errors, $input);
}
function process_form($input) {
// Access the global variable $db inside this function
global $db;
// Build up the query
$sql = ‘SELECT dish_name, price, is_spicy FROM dishes WHERE
price >= ? AND price <= ?’;
// If a dish name was submitted, add to the WHERE clause.
// We use quote() and strtr() to prevent user-entered wildcards from working.
if (strlen($input[‘dish_name’])) {
$dish = $db->quote($input[‘dish_name’]);
$dish = strtr($dish, array(‘_’ => ‘\_’, ‘%’ => ‘\%’));
$sql .= ” AND dish_name LIKE $dish”;
}
// If is_spicy is “yes” or “no”, add appropriate SQL
// (if it’s “either”, we don’t need to add is_spicy to the WHERE clause)
$spicy_choice = $GLOBALS[‘spicy_choices’][ $input[‘is_spicy’] ];
if ($spicy_choice == ‘yes’) {
$sql .= ‘ AND is_spicy = 1’;
} elseif ($spicy_choice == ‘no’) {
$sql .= ‘ AND is_spicy = 0’;
}
// Send the query to the database program and get all the rows back
$stmt = $db->prepare($sql);
$stmt->execute(array($input[‘min_price’], $input[‘max_price’]));
$dishes = $stmt->fetchAll();
if (count($dishes) == 0) {
print ‘No dishes matched.’;
} else {
print ‘<table>’;
print ‘<tr><th>Dish Name</th><th>Price</th><th>Spicy?</th></tr>’;
foreach ($dishes as $dish) {
if ($dish->is_spicy == 1) {
$spicy = ‘Yes’;
} else {
$spicy = ‘No’;
}
printf(‘<tr><td>%s</td><td>$%.02f</td><td>%s</td></tr>’,
htmlentities($dish->dish_name), $dish->price, $spicy);
}
}
}
?>
Example 8-53 is a lot like Example 8-28: it uses the standard display/validate/process form structure with global code for database setup and database interaction inside process_fom(). The show_form() function displays the form HTML defined in the retrieve-form.php file. This file is shown in Example 8-54.
Example 8-54. Form for retrieving information about dishes
form method=”POST” action=”<?= $fom->encode($_SERVER[‘PHP_SELF’]) ?>”>
<tabte>
<?php if ($errors) { ?>
<tr>
<td>You need to correct the following errors:</td>
<td><ul>
<?php foreach ($errors as $error) { ?>
<li><?= $form->encode($error) ?></li>
<?php } ?>
</ul></td>
<?php } ?>
<tr>
<td>Dish Name:</td>
<td><?= $form->input(‘text’, [‘name’ => ‘dish_name’]) ?></td>
</tr>
<tr>
<td>Minimum Price:</td>
<td><?= $form->input(‘text’,[‘name’ => ‘min_price’]) ?></td>
</tr>
<tr>
<td>Maximum Price:</td>
<td><?= $form->input(‘text’,[‘name’ => ‘max_price’]) ?></td>
</tr>
<tr>
<td>Spicy:</td>
<td><?= $form->select($GLOBALS[‘spicy_choices’], [‘name’ => ‘is_spicy’]) ?> </td>
</tr>
<tr>
<td colspan=”2″ align=”center”>
<?= $form->input(‘submit’, [‘name’ => ‘search’,
‘value’ => ‘Search’]) ?></td>
</tr>
</table>
</form>
One difference in Example 8-53 is an additional line in its database setup code: a call to setAttribute() that changes the fetch mode. Since process_fom() is going to retrieve information from the database, the fetch mode is important.
The process_form() function builds up a SELECT statement, sends it to the database with execute(), retrieves the results with fetchAH(), and prints the results in an HTML table. Up to four factors go into the WHERE clause of the SELECT statement. The first two are the minimum and maximum price. These are always in the query, so they get placeholders in $sql, the variable that holds the SQL statement.
Next comes the dish name. That’s optional, but if it’s submitted, it goes into the query. A placeholder isn’t good enough for the dish_name column, though, because the submitted form data could contain SQL wildcards. Instead, quote() and strtr() prepare a sanitized version of the dish name, and it’s added directly onto the WHERE clause.
The last possible column in the WHERE clause is is_spicy. If the submitted choice is yes, then AND is_spicy = 1 goes into the query so that only spicy dishes are retrieved. If the submitted choice is no, then AND is_spicy = 0 goes into the query so that only nonspicy dishes are found. If the submitted choice is either, then there’s no need to have is_spicy in the query—rows should be picked regardless of their spiciness.
After the full query is constructed in $sql, it’s prepared with prepare() and sent to the database program with execute(). The second argument to execute() is an array containing the minimum and maximum price values so that they can be substituted for the placeholders. The array of rows that fetchAll() returns is stored in $dishes.
The last step in process_form() is printing some results. If there’s nothing in $dishes, No dishes matched is displayed. Otherwise, a foreach() loop iterates through dishes and prints out an HTML table row for each dish, using printf() to format the price properly and htmlentities() to encode any special characters in the dish name. An if() clause turns the database-friendly is_spicy values of 1 or 0 into the human-friendly values of Yes or No.
Source: Sklar David (2016), Learning PHP: A Gentle Introduction to the Web’s Most Popular Language, O’Reilly Media; 1st edition.