Web Development
Using PHP

Simpson College
Computer Science

Introduction to Databases

Introduction to the terminology

A database table is similar to a spreadsheet table that lists data items in rows.

fig.table
Table
fig.fields
Field
fig.rows
Row
fig.database
Database

Set up the database

Start up EasyPHP and MySQL.

Click 'e' button, and then select "Administration."

fig.select_administration
Selecting the administration menu

Click Manage MySQL

fig.manage_mysql
Manage Databases

Select "Users" tab.

Create a new user.

fig.create_new_database
Create a new user

Use the name "cis305". For simplicity sake, use the same for the password. Remember you'd never do this in a production system. Then select that you want to create a database with the same name. Finally, way down in the lower right is the "go" button that you can click to create the user.

fig.create_new_database
Screen for creating a new user

Create a new table called "People" with 3 fields.

fig.create_people_table
Create a database table

Fill out the information as follows:

fig.people_table
Create fields for database table

Afterwards, you should have a table:

fig.list_tables
List of tables

The table detail should look like this:

fig.table_detail
Detail of people table

Click the insert tab then add the record data:

fig.add_people_record
Add record to people table

After inserting the record, the system should display a message like this:

fig.record_inserted
Record inserted

Hit the browse tab and make sure the record was added:

fig.browse_people
Browse people

Sample code to display database records

The code to list the fields looks like this:

<!DOCTYPE html>
 
<html lang="en">
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=us-ascii" />
 
  <title>List People</title>
</head>
 
<body>
  <h1>List of people:</h1>
  <?php
  /* Info to hook up to a database.
   * Normally, we'd import this from a common file. You would
   * not want to update this in each file when the password
   * changes.
   */
  $username = "cis305";
  $password = "cis305";
  $database = "cis305";
  $server   = "localhost";
 
  $connection = mysqli_connect($server, $username, $password, $database) or die("Unable to connect");

  // Pull all users from the database
  $query = "SELECT * FROM people";
  $result = mysqli_query($connection, $query) or die("Query failed");
 
  // Loop through each record. This could also
  // be done with a 'for' loop.
  while($row = mysqli_fetch_assoc($result)) {
    $firstname = $row['first'];
    $lastname = $row['last'];
    echo "$firstname $lastname<br />";
  }
  mysqli_free_result($result);
  mysqli_close($connection);
  ?>
</body>
</html>

Try it out: [link]

The example in the section above has a problem. It is not secure against HTML code that is contained in the database fields. To properly display the first name and last name, use the htmlentities function again. Change the following code:

echo "$firstname $lastname
";

To this:

echo htmlentities($firstname);
echo " ";
echo htmlentities($lastname);
echo "
";

Sample code to insert records

The SQL database statement to insert a record into the database is simple. It looks like this:

insert into people (first, last)
values ('Jane','Smith')

For more information on this statement, read W3School's coverage on the INSERT INTO statement.

These four files will list people in the table, and allow a user to insert a person into the table.

The first file is a common include file that sets up the database connection. The code in this file is not different than prior code examples, it has just been put into a separate file.

<?php
  // Information for hooking up to the database.
  $username = "cis305";
  $password = "cis305";
  $database = "cis305";
  $server   = "localhost";
?>

This file is very similar to the prior version of list_people.php. The main differences:

<!DOCTYPE html>

<html lang="en">
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
  <title>Manage People</title>
</head>

<body>
  <h1>Manage people</h1>
  <p><a href="form.php">Add new person</a></p>
  <h2>List of people:</h2>
  <?php
  // Include common db setup code
  include ("db_setup.php");

  $connection = mysqli_connect($server, $username, $password, $database) or die("Unable to connect");

  // Pull all users from the database
  $query = "SELECT * FROM people";
  $result = mysqli_query($connection, $query) or die("Query failed");

  // Loop through each record. This could also
  // be done with a 'for' loop.
  while($row = mysqli_fetch_assoc($result)) {
    $firstname = $row['first'];
    $lastname = $row['last'];
    echo "$firstname $lastname<br />";
  }
  mysqli_free_result($result);
  mysqli_close($connection);
  ?>
</body>
</html>

This is a simple form to take a first and last name. It is very similar to prior examples. It expects that form information will be stored in the session in an array called form.

<!DOCTYPE html>

<html lang="en">
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
  <title>Sample PHP</title>
</head>

<body>
  <h1>Form Test</h1><?php
  session_start();
  if(isset($_SESSION['messages'])) {
    $messages = $_SESSION['messages'];
    $form = $_SESSION['form'];
    unset($_SESSION['messages']);
    unset($_SESSION['form']);
  }
  if(!empty($messages)) {
          foreach ($messages as $message) {
              echo $message;
          }
  }
  ?>

  <form action="form_process.php" method="get">
    <!-- Enter name -->
    First name: <input type="text" name="firstname" <?php
    if( isset($form['firstname']) ) {
        echo 'value="';
        echo htmlentities ($form['firstname']);
        echo '"';
    }
    ?>/><br />
    Last name: <input type="text" name="lastname" <?php
    if( isset($form['lastname']) ) {
        echo 'value="';
        echo htmlentities ($form['lastname']);
        echo '"';
    }
    ?>/><br />
    <!-- Add submit button -->
    <input type="submit" value="Submit" />
  </form>
</body>
</html>

This code processes the form. It includes the code required to insert the record into the database. The important part of the code for the SQL insert statement is:

/* Set up the database connection */
include ("db_setup.php");

/* Escape the string to prevent SQL injection */
$firstname_safe = mysql_real_escape_string($form['firstname']);
$lastname_safe = mysql_real_escape_string($form['lastname']);

/* Construct the SQL statement */
$query="insert into people (first, last) values ('$firstname_safe', '$lastname_safe')";

/* Run the SQL statement */
mysql_query($query);

Note lines 5 and 6. Just like HTML, input from the user could be in the form of a database query. Terrible things could happen, including the deletion of all the data in the database table. See the following comic for an example: [link] The function mysql_real_escape_string will keep this from happening.

Remember, before entering data it is necessary to "sanatize" any input data.

<?php
session_start();

/* Keep track if there is an error or not */
$error=FALSE;

/* Validation of first name */
if(empty($_REQUEST['firstname'])) {
    /* There was no first name, that's odd */
    $error=TRUE;
    /* Create an array of messages to display the user */
    $messages['firstname']="<p class='errormsg'>Error - Invalid First Name</p>";
} else {
    /* Get the first name from the request */
    $form['firstname'] = $_REQUEST['firstname'];
    /* See if the first name matches our not-very-good filter */
    if (!preg_match("/^[A-Za-z]{1,25}$/", $form['firstname'])) {
        /* No match, display an error */
        $error=TRUE;
        /* Create an array of messages to display the user */
        $messages['firstname']="<p class='errormsg'>Error - Invalid First Name</p>";
    }
}
/* Validation of last name */
if(empty($_REQUEST['lastname'])) {
    /* There was no last name, that's odd */
    $error=TRUE;
    /* Create an array of messages to display the user */
    $messages['lastname']="<p class='errormsg'>Error - Invalid Last Name</p>";
} else {
    /* Get the last name from the request */
    $form['lastname'] = $_REQUEST['lastname'];
    /* See if the first name matches our not-very-good filter */
    if (!preg_match("/^[A-Za-z]{1,25}$/", $form['lastname'])) {
        /* No match, display an error */
        $error=TRUE;
        /* Create an array of messages to display the user */
        $messages['lastname']="<p class='errormsg'>Error - Invalid Last Name</p>";
    }
}

/* If there was an error, include the form again. Otherwise continue on. */
if($error==FALSE) {
	/* Set up the database connection */
	include ("db_setup.php");

    $connection = mysqli_connect($server, $username, $password, $database) or die("Unable to connect");

	/* Escape the string to prevent SQL injection */
	$firstname_safe = mysql_real_escape_string($form['firstname']);
	$lastname_safe = mysql_real_escape_string($form['lastname']);

	/* Construct the SQL statement */
	$query="insert into people (first, last) values ('$firstname_safe', '$lastname_safe')";

	/* Run the SQL statement */
	mysqli_query($connection, $query) or die("Insert query failed to run.");

    header("Location: list_people.php");
} else {
    $_SESSION['messages'] = $messages;
    $_SESSION['form'] = $form;
    header("Location: form.php");
}

?>

Try it out: [link]

Sample code to delete records

Deleting records requires a minimum of two files. One to list the items, and one to process the delete request.

To list the items, take the prior example list_people.php and modify it in the following way:

<!DOCTYPE html>

<html lang="en">
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
  <title>Manage People</title>
</head>

<body>
  <h1>Manage people</h1>
  <p><a href="form.php">Add new person</a></p>
  <h2>List of people:</h2>
  <?php
  // Include common db setup code
  include ("db_setup.php");

  $connection = mysqli_connect($server, $username, $password, $database) or die("Unable to connect");

  // Pull all users from the database
  $query = "SELECT * FROM people";
  $result = mysqli_query($connection, $query) or die("Query failed");

  // Loop through each record. This could also
  // be done with a 'for' loop.
  while($row = mysqli_fetch_assoc($result)) {
    $firstname = $row['first'];
    $lastname = $row['last'];
    $id = $row['id'];
		echo "[<a href='delete_process.php?id=$id'>delete</a>] ";
		echo htmlentities($firstname);
		echo " ";
		echo htmlentities($lastname);
		echo "<br />";
  }
  ?>
</body>
</html>

This code will process the delete request, removing it from the database. It uses the delete SQL statement.

This example code is VERY DANGEROUS because it is missing two items:

1) In this example, the user does not need to be logged in to get to this point. Anyone may delete the people in the database. If a search engine explores the web site, it will explore all the delete links and delete all the records in the database.

2) Even if there was a log-in, this code does not check to see if the person logged in has permission to delete this particular record. Remember that the id value can be changed and anyone could swap it for a different number.

<?php
session_start();

/* Keep track if there is an error or not */
$error = FALSE;

/* Validation of first name */
if(empty($_REQUEST['id'])) {
    /* There was no first name, that's odd */
    $error = TRUE;
} else {
    /* Get the first name from the request */
    $form['id'] = $_REQUEST['id'];
    /* See if the first name matches our not-very-good filter */
    if (!preg_match("/^[0-9]{1,25}$/", $form['id'])) {
        /* No match, display an error */
        $error = TRUE;
    }
}

/* If there was no error, delete the record.  */
if( !$error ) {
	/* Set up the database connection */
	include ("db_setup.php");
	$connection = mysqli_connect($server, $username, $password, $database) or die("Unable to connect");
	/* DANGER DANGER DANGER
	 * This example code is VERY DANGEROUS
	 * because it is missing two items.
	 *
	 * 1) In this example, the user does not need to be logged in
	 * to get to this point. Anyone may delete the people in the
	 * database. If a search engine explores the web site, it will
	 * explore all the delete links and delete all the records in
	 * the database.
	 *
	 * 2) Even if there was a log-in, this code does not
	 * check to see if the person logged in has permission to delete
	 * this particular record. Remember that the id value can be
	 * changed and anyone could swap it for a different number.
	 */
	/* Escape the string to prevent SQL injection */
	$id_safe = mysql_real_escape_string($form['id']);
	/* Construct the SQL statement */
	$query="delete from people where id = $id_safe";

	/* Run the SQL statement */
	mysqli_query($connection, $query);
	mysqli_close($connection);
}
/* Go back to the list people page. */
header("Location: list_people.php");

?>

Try it out: [link]

Sample code to update records

Updating is the most complex portion of Creating, Reading, Updating, and Deleting (CRUD) database records. First, the record needs to be read into the session. Next, the form needs to be displayed to the user with all the record information pre-filled in. Finally, the record data needs to be updated.

The files for listing records, displaying the form, and processing the form can be adapted to support both insertion of a new record and updating an existing record. A new file needs to be created that will load the existing record and get it ready to be put in the form.

The list_people.php file is updated with a link to edit the record.

<!DOCTYPE html>

<html lang="en">
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
  <title>Manage People</title>
</head>

<body>
  <h1>Manage people</h1>
  <p><a href="form.php">Add new person</a></p>
  <h2>List of people:</h2>
  <?php
  // Include common db setup code
  include ("db_setup.php");

  $connection = mysqli_connect($server, $username, $password, $database) or die("Unable to connect");

  // Pull all users from the database
  $query = "SELECT * FROM people";
  $result = mysqli_query($connection, $query) or die("Query failed");

  // Loop through each record. This could also
  // be done with a 'for' loop.
  while($row = mysqli_fetch_assoc($result)) {
    $firstname = $row['first'];
    $lastname = $row['last'];
    $id = $row['id'];
		echo "[<a href='delete_process.php?id=$id'>delete</a>] ";
		echo "[<a href='edit_process.php?id=$id'>edit</a>] ";
		echo htmlentities($firstname);
		echo " ";
		echo htmlentities($lastname);
		echo "<br />";
  }
  ?>
</body>
</html>

This new file loads the database record into session and forwards the user to the form.

<?php
session_start();

/* Keep track if there is an error or not */
$error=FALSE;

/* Validation of first name */
if(empty($_REQUEST['id'])) {
    /* There was no first name, that's odd */
    $error=TRUE;
} else {
    /* Get the first name from the request */
    $form['id'] = $_REQUEST['id'];
    /* See if the first name matches our not-very-good filter */
    if (!preg_match("/^[0-9]{1,25}$/", $form['id'])) {
        /* No match, display an error */
        $error=TRUE;
    }
}

/* If there was no error, load the record.  */
if(!$error) {
	/* Set up the database connection */
	include ("db_setup.php");
	$connection = mysqli_connect($server, $username, $password, $database) or die("Unable to connect");

	/* DANGER DANGER DANGER
	 * This example code is VERY DANGEROUS
	 * because it is missing an important items.
	 *
	 * 1) If there was a log-in, this code does not
	 * check to see if the person logged in has permission to edit
	 * this particular record. Remember that the id value can be
	 * changed and anyone could swap it for a different number.
	 */
	/* Escape the string to prevent SQL injection */
	$id_safe = mysql_real_escape_string($form['id']);

	/* Construct the SQL statement */
	$query="select * from people where id = $id_safe";

	/* Run the SQL statement */
	$result = mysqli_query($connection, $query) or die("Query failed");

	$row = mysqli_fetch_assoc($result);

	if($row) {
		/* Grab the data and put in the form array */
		$form['id']=$row['id'];
		$form['firstname']=$row['first'];
		$form['lastname']=$row['last'];
	} else {
		// We didn't find the row the user was looking for.
		die("That record doesn't exist.");
	}

	/* Save form to session */
    $_SESSION['form'] = $form;

	/* Send message to user */
	$messages['message']="Editing the record for " . $form['firstname'] . " " . $form['lastname'];

	/* Save messages to session */
    $_SESSION['messages'] = $messages;

	/* Go to the form */
	header("Location: form.php");
} else {
	/* Error, just go back */
	header("Location: list_people.php");
}
?>

This update to the form adds a hidden html field to hold the id. Remember that any slightly sophisticated user can view and modify hidden form fields!

<!DOCTYPE html>

<html lang="en">
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
  <title>Sample PHP</title>
</head>

<body>
  <h1>Form Test</h1><?php
  session_start();
  if(isset($_SESSION['messages'])) {
    $messages = $_SESSION['messages'];
    $form = $_SESSION['form'];
    unset($_SESSION['messages']);
    unset($_SESSION['form']);
  }
  if(!empty($messages)) {
          foreach ($messages as $message) {
              echo $message;
          }
  }
  ?>

  <form action="form_process.php" method="get">
    <!-- Enter name -->
<?php
	if (isset($form['id'])) {
		echo "<input type='hidden' name='id' value='";
		echo htmlentities($form['id']);
		echo "'>";
	}
?>
    First name: <input type="text" name="firstname" <?php
    if( isset($form['firstname']) ) {
        echo 'value="';
        echo htmlentities ($form['firstname']);
        echo '"';
    }
    ?>/><br />
    Last name: <input type="text" name="lastname" <?php
    if( isset($form['lastname']) ) {
        echo 'value="';
        echo htmlentities ($form['lastname']);
        echo '"';
    }
    ?>/><br />
    <!-- Add submit button -->
    <input type="submit" value="Submit" />
  </form>
</body>
</html>

Updating records requires the use the update SQL statement. An if statement checks for the existance of id. If it exists, the code assumes that an update is needed rather than an insertion of an new record.

<?php
session_start();

/* Keep track if there is an error or not */
$error=FALSE;

/* Validation of first name */
if(empty($_REQUEST['id'])) {
    $form['id']="";
} else {
    /* Get the first name from the request */
    $form['id'] = $_REQUEST['id'];
    /* See if the first name matches our not-very-good filter */
    if (!preg_match("/^[0-9]{1,25}$/", $form['id'])) {
        /* No match, what is happening? */
        $error=TRUE;
        /* Create an array of messages to display the user */
        $messages['id']="<p class='errormsg'>System error, unable to edit record.</p>";
    }
}

/* Validation of first name */
if(empty($_REQUEST['firstname'])) {
    /* There was no first name, that's odd */
    $error=TRUE;
    /* Create an array of messages to display the user */
    $messages['firstname']="<p class='errormsg'>Error - Invalid First Name</p>";
} else {
    /* Get the first name from the request */
    $form['firstname'] = $_REQUEST['firstname'];
    /* See if the first name matches our not-very-good filter */
    if (!preg_match("/^[A-Za-z]{1,25}$/", $form['firstname'])) {
        /* No match, display an error */
        $error=TRUE;
        /* Create an array of messages to display the user */
        $messages['firstname']="<p class='errormsg'>Error - Invalid First Name</p>";
    }
}
/* Validation of last name */
if(empty($_REQUEST['lastname'])) {
    /* There was no last name, that's odd */
    $error=TRUE;
    /* Create an array of messages to display the user */
    $messages['lastname']="<p class='errormsg'>Error - Invalid Last Name</p>";
} else {
    /* Get the last name from the request */
    $form['lastname'] = $_REQUEST['lastname'];
    /* See if the first name matches our not-very-good filter */
    if (!preg_match("/^[A-Za-z]{1,25}$/", $form['lastname'])) {
        /* No match, display an error */
        $error=TRUE;
        /* Create an array of messages to display the user */
        $messages['lastname']="<p class='errormsg'>Error - Invalid Last Name</p>";
    }
}

/* If there was an error, include the form again. Otherwise continue on. */
if($error==FALSE) {
	/* Set up the database connection */
	include ("db_setup.php");

    $connection = mysqli_connect($server, $username, $password, $database) or die("Unable to connect");

	/* Escape the string to prevent SQL injection */
    $id_safe = mysql_real_escape_string($form['id']);
	$firstname_safe = mysql_real_escape_string($form['firstname']);
	$lastname_safe = mysql_real_escape_string($form['lastname']);

    if( $id_safe == "" ) {
       /* Construct the SQL statement */
       $query="insert into people (first, last) values ('$firstname_safe', '$lastname_safe')";
    } else {
       $query="update people set first='$firstname_safe', last='$lastname_safe' where id='$id_safe'";
    }

	/* Run the SQL statement */
	mysqli_query($connection, $query) or die("Insert query failed to run.");

    header("Location: list_people.php");
} else {
    $_SESSION['messages'] = $messages;
    $_SESSION['form'] = $form;
    header("Location: form.php");
}

?>

Try it out: [link]

You are not logged in. Log in here and track your progress.