SakhaliaNetHome PageHistory of the RailwayVorKutaAcceptance of cookiesAcceptance of cookies

PHP Tutorial :: Exercises (IX)

PHP Exercise #18

Insert customers into database

Create a table that contains the following information about the customers of a restaurant: customer ID, name, phone number and favourite dish ID. Write a program that shows a form that allows to insert customers in the table, where a select menu is used to incorporate the favourite dish. The customer ID should be generated by the program.

The structure for the customers table is: CREATE TABLE customers ( customer_id INT UNSIGNED, customer_name VARCHAR(255), phone VARCHAR(15), favorite_dish_id INT ). The column customer_id is set as primary and auto-incremental.

<?php
// Loads the form's auxiliary functions
require 'scripts/formhelpers.php';
// Connects with database
$db = mysqli_connect('insert_hostname_here','insert_username_here',
'insert_password_here','insert_databasename_here');
if (! $db) { die("Can't connect: " . mysqli_connect_error()); }
// Obtains the array of dishes from the database
$dish_names = array();
$sql = 'SELECT dish_id, dish_name FROM dishes_2';
$q = mysqli_query($db, $sql);
if (mysqli_num_rows($q) > 0) {
while ($row = mysqli_fetch_object($q)) {
$dish_names[] = $row->dish_name;
}
} else {
print 'No dishes available.';
}
// 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()) {
show_form($form_errors);
} else {
// The data sent is valid, therefore, process it
process_form();
}
} else {
// The form had not been sent, therefore, show it
show_form();
}
function show_form($errors ='') {
global $db;
// If the form has been sent, obtain the default values
// from the parameters sent
if ($_POST['_submit_check']) {
$defaults = $_POST;
} else {
// If not, set our own default values
$defaults = array();
}
// If errors were passed, print them
if ($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']; ?>">
<table>
<?php print $error_text; ?>
<tr><td>Customer Name:</td>
<td><?php input_text('customer_name', $defaults); ?></td></tr>
<tr><td>Phone Number:</td>
<td><?php input_text('phone', $defaults); ?></td></tr>
<tr><td>Favorite Dish:</td>
<td><?php input_select('favorite_dish_id', $defaults, $GLOBALS['dish_names']); ?></td></tr>
<tr><td colspan="2" align="center"><?php input_submit('save','Add Customer'); ?>
</td></tr>
</table>
<input type="hidden" name="_submit_check" value="1"/>
</form>
<?php
} // The end of show_form()
function validate_form() {
global $dish_names;
$errors= array();
// The name of the customer is required
if (! strlen(trim($_POST['customer_name']))) {
$errors[] = 'Please enter the customer name.';
}
// The phone number is required
if (! strlen(trim($_POST['phone']))) {
$errors[] = 'Please enter a phone number.';
} elseif (! preg_match('/^\(\d{3}\) ?\d{3}-\d{4}$/', $_POST['phone'])) {
$errors[] = 'Please enter a phone number in the format (XXX) XXX-XXXX.';
}
// The favourite dish is required
if (! array_key_exists($_POST['favorite_dish_id'], $dish_names)) {
$errors[] = 'Please select a favorite dish.';
}
return $errors;
}
function process_form() {
global $db;
// Obtains an unique ID for this customer
$customer_id = mysqli_insert_id($db);
// Inserts the new customer in the table
$sql = 'INSERT INTO customers (customer_id, customer_name,
phone, favorite_dish_id) ';
$sql .= 'VALUES (' . $customer_id . ", '" .
mysqli_real_escape_string($db,$_POST['customer_name']) . "', '";
$sql .= mysqli_real_escape_string($db,$_POST['phone']) . "', " .
mysqli_real_escape_string($db,$_POST['favorite_dish_id']) . ')';
$q = mysqli_query($db, $sql);
// Checks errors in mysqli_query
if ($q == false) {
printf ("Error: %s\n", mysqli_error($db));
} else {
// Tells to the user that a new customer has been added
print 'Added ' . htmlentities($_POST['customer_name']) .
' to the database.';
}
print nl2br("\n\n"); // Allows to show the form
show_form(); // after being sent
?>
Customer Name:
Phone Number:
Favorite Dish: