PHP Tutorial :: Databases (II)

PHP Example #74

A complete data recovery form

To connect to a database, we need to know the DSN (Data Source Name) of that particular database. The generic form of a DSN includes a hostname, an username, a password and the database name. All this data is generally available in the control panel of your server (the host). For these examples, a very simple database was created, containing a table with four columns and a handful of rows.

You can search for the values introduced in the database by using the form below. Names stored in the database are Braised Sea Cucumber, Walnut Bun, Cashew Nuts and White Mushrooms, Dried Mulberries, Eggplant with Chili Sauce, Red Bean Bun and General Tso's Chicken. The prices oscillate between 1.00 and 6.50.

Note that the variable $db is turned into an object that represents the whole database after the DSN command is passed to it, if the connection was successful; otherwise, an error object will be returned instead of a database object. The function mysqli_real_escape_string() is needed to avoid SQL injection attacks; it escapes characters that could be used to insert dangerous codes into the system, having a similar effect than htmlentities() or strip_tags().

// Loads the form's auxiliary functions
require 'scripts/formhelpers.php';
// Connects with database
$db = mysqli_connect('insert_hostname_here','insert_username_here',
if (! $db) { die("Can't connect: " . mysqli_connect_error()); }
// Options for spicy menu in the form
$spicy_choices = array('no','yes','either');
// Main logic for the form
// - If the form is sent, validate it and process it or show it again
// - If it is not sent, show it
if (! array_key_exists('_submit_check', $_POST)) {
$_POST['_submit_check'] = 0;
if ($_POST['_submit_check']) {
// If validate_form() returns errors, pass them to show_form()
if ($form_errors = validate_form()) {
} else {
// The data sent is valid, therefore, process it
} else {
// The form had not been sent, therefore, show it
function show_form($errors ='') {
// If the form has been sent, obtain the default values
// of the parameters sent
if ($_POST['_submit_check']) {
$defaults = $_POST;
} else {
// If not, set our own default values
$defaults = array('dish_name' => '', 'min_price' => '5.00',
'max_price' => '25.00');
// If errors were passed, put them in $error_text (with HTML code)
if (is_array($errors)) {
$error_text = '<tr><td>You need to correct the following errors:';
$error_text .= '</td><td><ul><li>';
$error_text .= implode('</li><li>',$errors);
$error_text .= '</li></ul></td></tr>';
} else {
// If there were no errors, then $error_text is empty
$error_text = '';
// Exit PHP mode to facilitate the visualization of all the HTML tags
<form method="post" action="<?php print $_SERVER['PHP_SELF']; ?>">
<?php print $error_text; ?>
<tr><td>Dish Name:</td>
<td><?php input_text('dish_name', $defaults); ?></td></tr>
<tr><td>Minimum Price:</td>
<td><?php input_text('min_price', $defaults); ?></td></tr>
<tr><td>Maximum Price:</td>
<td><?php input_text('max_price', $defaults); ?></td></tr>
<td><?php input_select('is_spicy', $defaults,
$GLOBALS['spicy_choices']); ?>
<tr><td colspan="2" align="center">
<?php input_submit('search','Search'); ?>
<input type="hidden" name="_submit_check" value="1"/>
} // The end of show_form()
function validate_form() {
$errors = array();
// The minimum price must be a valid float number
if ($_POST['min_price'] != strval(floatval($_POST['min_price']))) {
$errors[] = 'Please enter a valid minimum price.';
// The maximum price must be a valid float number
if ($_POST['max_price'] != strval(floatval($_POST['max_price']))) {
$errors[] = 'Please enter a valid maximum price.';
// The minimum price must be lesser than the maximum price
if ($_POST['min_price'] >= $_POST['max_price']) {
$errors[] = 'The minimum price must be lesser than the maximum price.';
if (! array_key_exists($_POST['is_spicy'], $GLOBALS['spicy_choices'])) {
$errors[] = 'Please choose a valid "spicy" option.';
return $errors;
function process_form() {
// Accesses the global variable $db inside this function
global $db;
// Create the query
$sql = 'SELECT dish_name, price, is_spicy FROM dishes WHERE ';
// Adds minimum price to the query
$sql .= "price >= '" .
mysqli_real_escape_string($db, $_POST['min_price']) . "' ";
// Adds maximum price to the query
$sql .= " AND price <= '" .
mysqli_real_escape_string($db, $_POST['max_price']) . "' ";
// If a dish name has been sent, add it to the WHERE clause
// We use mysqli_real_escape_string() and strtr() to prevent that
// wildcards facilitated by the user can work
if (strlen(trim($_POST['dish_name']))) {
$dish = mysqli_real_escape_string($db, $_POST['dish_name']);
$dish = strtr($dish, array('_' => '\_', '%' => '\%'));
// mysqli_real_escape_string() doesn't add single quotes around the value
// so we have to put them around $dish in the query
$sql .= " AND dish_name LIKE '$dish'";
// If is_spicy is "yes" or "no", add the appropriate SQL query
// (If it is any of both, we don't need to add is_spicy to the WHERE clause)
$spicy_choice = $GLOBALS['spicy_choices'][$_POST['is_spicy']];
if ($spicy_choice == 'yes') {
$sql .= ' AND is_spicy = 1';
} elseif ($spicy_choice == 'no') {
$sql .= ' AND is_spicy = 0';
// Sends the query to the database program and obtain all the rows
$q = mysqli_query($db, $sql);
if (mysqli_num_rows($q) == 0) {
print 'No dishes matched.';
print nl2br("\n\n"); // Allows to visualize the form
show_form(); // after being sent
} else {
print '<table>';
print '<tr><th>Dish Name</th><th>Price</th><th>Spicy?</th></tr>';
while ($dish = mysqli_fetch_object($q)) {
if ($dish->is_spicy == 1) {
$spicy = 'Yes';
} else {
$spicy = 'No';
htmlentities($dish->dish_name), $dish->price, $spicy);
print '</table>';
print nl2br("\n\n"); // Allows to visualize the form
show_form(); // after being sent
Dish Name:
Minimum Price:
Maximum Price: