PHP Tutorial :: Databases (I)

PHP Example #73

A complete data insertion 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). Today, even free servers allow to create databases and those are really much more useful than many people think. They are great for testing and learning, available even to the most humble users.

For these examples, a very simple database was created, containing a table with four columns and a handful of rows. You can test it by introducing some values on it via this form and then searching for them thru the form in the next example. 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 first field of the table is the ID, which stores an integer number, and is set to increment this number automatically with every data insertion. So, rows that have duplicated values can be distinguished by their unique ID. 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().

The table in this example was created with the SQL statement: CREATE TABLE dishes ( dish_id INT, dish_name VARCHAR(255), price DECIMAL(4,2), is_spicy INT ). Literally speaking, this command would mean: Create a table called 'dishes', where the first field is called 'dish_id' and stores an integer value, the second field is called 'dish_name' and stores a string value whose maximum lenght is 255 characters, the third field is called 'price' and stores a float number that has two decimal digits and a maximum of four digits, and a fourth field called 'is_spicy' that stores an integer value.

// 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' => '', 'price' => '5.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>
<td><?php input_text('price', $defaults); ?></td></tr>
<td><?php input_radiocheck('checkbox', 'is_spicy', $defaults, 'yes'); ?>
<tr><td colspan="2" align="center">
<?php input_submit('save','Save'); ?>
<input type="hidden" name="_submit_check" value="1"/>
} // The end of show_form()
function validate_form() {
$errors = array();
// The name of the dish is required
if (! strlen(trim($_POST['dish_name']))) {
$errors[] = 'Please enter the name of the dish.';
// The price must be a float number and greater than 0
if (floatval($_POST['price']) <= 0) {
$errors[] = 'Please enter a valid price.';
return $errors;
function process_form() {
// Accesses the global variable $db inside this function
global $db;
// Obtain an unique ID for this dish
$dish_id = mysqli_insert_id($db);
// Set the value of $is_spicy based in the checkbox
if ($_POST['is_spicy'] == 'yes') {
$is_spicy = 1;
} else {
$is_spicy = 0;
// Inserts the new dish in the table
$sql = 'INSERT INTO dishes (dish_id, dish_name, price, is_spicy) ';
$sql .= 'VALUES (' . $dish_id . ", '" .
mysqli_real_escape_string($db,$_POST['dish_name']) . "', ";
$sql .= mysqli_real_escape_string($db,$_POST['price']) . ", " .
$is_spicy . ')';
$q = mysqli_query($db, $sql);
// Checks errors in mysqli_query
if ($q == false) {
printf ("Error: %s\n", mysqli_error($db));
} else {
// Tells the user that a new dish has been inserted
print 'Added ' . htmlentities($_POST['dish_name']) .
' to the database.';
print nl2br("\n\n"); // Permite visualizar el formulario
show_form(); // despu├ęs de ser enviado
Dish Name:
Spicy: Yes