Adding data

Where are we?

You know how to create databases, add tables, and test connections. Now let’s get our hands (brains?) dirty. Let’s see how you help users add data.

This lesson’s goals

In this lesson, you will learn:

  • There are two pages for adding a record: one page with a form the user fills in, and another page that adds the user’s data to the database.
  • The SQL INSERT statement does the work.
  • Use stripslashes() to remove backslashes that PHP adds to form data.
  • Use $db->escape_string() to foil SQL injection attacks.

Adding a new toy

Let’s see how data gets added to the DogToys database.

The big picture

What’s the workflow when a user enters a new record?

First, the user clicks the Add product link on the administration menu.

Main administration screen

Figure 1. Main DogToys administration screen

The user sees a form:

Adding product data

Figure 2. Adding product data

The user types in the data, and clicks the Save button. This sends the data to a PHP page that saves the data. That page then goes back to the administration menu.

So the workflow is:

Adding product data

Figure 3. Adding product workflow

  • Browser: Show the administration menu.
  • User: Click the Add product link.
  • Browser: Show a form.
  • User: Fill in the form and click the Save button.
  • Server: Run a PHP program that saves the data. Then tell the browser to…
  • Browser: Show the administration menu.

The form

Here’s the code for the form. I omitted some validation stuff; I’ll add it back in the next lesson.

<form id="new_product_form" method="post" action="save-new-product.php">
  <p>
    Name<br>
    <input type="text" name="name" id="name" size="30">
  </p>
  <p>
    Description<br>
    <textarea name="description" id="description" rows="5" cols="30"></textarea>
  </p>
  <p>
    Image file name<br>
    <input type="text" name="image_file_name" id="image_file_name" size="30">
  </p>
  <p>
    Price<br>
    <input type="text" name="price" id="price" size="10">
  </p>
  <p>
    <button type="submit">Save</button>
  </p>
</form>

Figure 4. HTML for the form

First there’s the <form> tag:

<form id="new_product_form" method="post" action="save-new-product.php">

action tells the browser where to go to save-new-product.php when the user submits the form.

The rest of the code should be familiar. The <input type="text"> tags create one-line input fields. <textarea> creates a multi-line input field.

PHP to insert a record

OK, let’s look at save-new-product.php, the PHP that saves the data. The pattern for the page is:

  • Connect to the database.
  • Get the values the user typed into the form.
  • Make the values safe.
  • Create and run an SQL INSERT statement.
  • Jump back to the admin menu.

Here is the entire code. We’ll run through it a step at a time.

<?php
//Save a new product.
//Input (all POST):
//  name: Name of the product.
//  description: Description of the product.
//  image_file_name: Name of the file containing an image of the product.
//  price: Selling price of the product.

$path_to_root = '..';
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);

//Get the form fields.
$name = stripslashes($_POST['name']);
$description = stripslashes($_POST['description']);
$image_file_name = stripslashes($_POST['image_file_name']);
$price = stripslashes($_POST['price']);

//Make the fields safe.
$name = $db->escape_string($name);
$description = $db->escape_string($description);
$image_file_name = $db->escape_string($image_file_name);
$price = $db->escape_string($price);

//Create and run the SQL.
$query = "insert into products
  (name, description, image_file_name, price)
  values ('$name', '$description', '$image_file_name', $price)";
$db->query($query);
//Back to admin menu.
header('location:index.php');
exit();
?>

Figure 5. save-new-product.php

Let’s break it down into steps.

Lines 2 to 7 are documentation. They explain what the page does, and what input it expects. It’s good practice to add comments like this.

Line 9 sets the variable $path_to_root to the path from save-new-product.php (the file we’re talking about) to the root of the site. This is part of the templating system. Recall that one of the business goals of DogToys was to make the site easy to change. The templating system lets us do that.

Database connection

The first step in the pattern is:

  • Connect to the database.
  • Get the values the user typed into the form.
  • Make the values safe.
  • Create and run an SQL INSERT statement.
  • Jump back to the admin menu.

Lines 11 and 12 connect to the database.

You could make a database connection with a line like this:

$db = new mysqli('localhost', 'dogtoys', 'password', 'dogtoys');

We’re going to be making connections to the database in many different PHP pages. The page that adds a record to the database needs to connect to the database. So does the page that saves edited data. And the page that deletes a record. And the product catalog page itself.

All of these pages would have a line like:

$db = new mysqli('localhost', 'dogtoys', 'password', 'dogtoys');

Now, what if we need to change the password? We’d need to find every one of these lines, in every page, and change it.

Ack!

There’s an easier way: put the connection information in a separate file, then include that file when needed. Here are the two lines from save-new-product.php:

require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);

Part of Figure 5 (again). save-new-product.php

Line 11 loads db-connect.php. Here’s what’s in it:

<?php
//DB connection data.
$host = 'localhost';
$db = 'dogtoys';
$user_name = 'dogtoys';
$password = 'password';
?>

Figure 6. db-connect.php

Line 12 uses the variables set in db-connect.php to make the connection.

Every page that needs to connect to the database uses db-connect.php.

If I want to change the password? I change one line in db-connect.php, and it’s changed for every page on the site.

Hooray! That’s another productivity win from reuse.

Grabbing the form data to insert

So we have a connection to the database. What’s next? Let’s look at the pattern.

  • Connect to the database.
  • Get the values the user typed into the form.
  • Make the values safe.
  • Create and run an SQL INSERT statement.
  • Jump back to the admin menu.

Here’s some more code from save-new-product.php.

//Get the form fields.
$name = stripslashes($_POST['name']);
$description = stripslashes($_POST['description']);
$image_file_name = stripslashes($_POST['image_file_name']);
$price = stripslashes($_POST['price']);

Part of Figure 5 (again). save-new-product.php

This gets the data the user typed into the form fields, and puts it into variables.

As we talked about earlier, PHP sometimes adds backslashes (\) to data typed into form fields. It’s trying to be “helpful.” The stripslashes() function gets rid of them. It’s good to send every value from the user through stripslashes().

Foiling the Evil Doer

What’s next?

  • Connect to the database.
  • Get the values the user typed into the form.
  • Make the values safe.
  • Create and run an SQL INSERT statement.
  • Jump back to the admin menu.

The next few lines are:

//Make the fields safe.
$name = $db->escape_string($name);
$description = $db->escape_string($description);
$image_file_name = $db->escape_string($image_file_name);
$price = $db->escape_string($price);

Part of Figure 5 (again). save-new-product.php

We’re going to create an SQL command from the data the user types in. It’s possible for an Evil Doer – one who knows SQL – to make trouble. Unless we do something about it.

Suppose someone typed this into the form:

Evil Doer at work

Figure 7. Evil Doer at work

The DROP statement is an SQL command that erases a table. By inserting quotes, semicolons, and SQL, a clever Evil Doer can make our PHP program do bad things.

This is called an SQL injection attack. You can see the consequences at XKCD.

escape_string() will foil the Evil Doer. It will mess up the quotes, semicolons, and other special characters needed for an SQL injection attack.

Hooray!

So run all form data through escape_string() before you do anything with it.

Creating and running an INSERT statement

What’s next?

  • Connect to the database.
  • Get the values the user typed into the form.
  • Make the values safe.
  • Create and run an SQL INSERT statement.
  • Jump back to the admin menu.

Here are the next few lines:

//Create and run the SQL.
$query = "insert into products
  (name, description, image_file_name, price)
  values ('$name', '$description', '$image_file_name', $price)";
$db->query($query);

Part of Figure 5 (again). save-new-product.php

This makes an SQL statement, and puts it into the variable $query. I split the statement across several lines to make it easier to read. PHP lets you do this when you use double quotes (”) around the string. SQL doesn’t care that statements are split across lines.

Here’s another example of line splitting in PHP:

$temp = "
   <blockquote>
     <p>I love dogs!</p>
   </blockquote>";

The HTML inside the quotes is easy to follow.

Note all the single quotes (’) on line 29. It’s important to get them right. The field name is a text field, so you need to put quotes around the values you put in it.

The field price is a number. So no quotes for its value.

Line 30 sends the SQL to MySQL for execution.

Back to the administration menu

What’s next?

  • Connect to the database.
  • Get the values the user typed into the form.
  • Make the values safe.
  • Create and run an SQL INSERT statement.
  • Jump back to the admin menu.

The end of save-new-product.php is:

header('location:index.php');

This tells the browser to jump back to index.php. There’s no directory, just a file name. save-new-product.php is in the /admin directory, so the browser will jump to /admin/index.php.

So that’s it.

  • The user fills in a form and clicks its submit button.
  • The data is sent to a PHP page:

  • Connect to the database.
  • Get the values the user typed into the form.
  • Make the values safe.
  • Create and run an SQL INSERT statement.
  • Jump back to the admin menu.

W00f!

That was DogToys. Let’s have a look at DogRock.

Adding a new article

Writers add articles to DogRock. Let’s look at the overall workflow.

The big picture

What’s the workflow when a user enters a new record?

First, the user clicks the “Add article” link on the administration menu.

Main administration screen

Figure 8. Main DogRock administration screen

The user sees a form. S/he types in the data, and clicks the Save button.

Adding an article

Figure 9. Adding an article

This sends the data to a PHP page, which saves the data. That page then goes back to the administration menu.

So the workflow is:

Adding an article

Figure 10. Workflow for adding an article

  • Browser: Show the administration menu.
  • User: Click the Add article link.
  • Browser: Show a form.
  • User: Fill in the form and click the Save button.
  • Server: Run a PHP program that saves the data. Then tell the browser to…
  • Browser: Show the administration menu.

The form

Here’s the code for the form. Again, I stripped out some validation stuff.

<form id="new_article_form" method="post" action="save-new-article.php">
  <p>
    Title<br>
    <input type="text" name="title" id="title" size="30">
  </p>
  <p>
    Author<br>
    <input type="text" name="author" id="author" size="30">
  </p>
  <p>
    Body<br>
    <textarea name="body" id="body" rows="10" cols="30"></textarea>
  </p>
  <p>
    <button type="submit">Save</button>
  </p>
</form>

Figure 11. HTML for the form

First there’s the <form> tag:

<form id="new_article_form" method="post" action="save-new-article.php">

action tells the browser to send the data to the page save-new-article.php.

The rest of the code should be familiar. The <input type="text"> tags create one-line input fields. <textarea> creates a multi-line input field.

PHP to insert a record

Let’s look at save-new-article.php, the PHP that saves the data. The pattern is:

  • Connect to the database.
  • Get the values the user typed into the form.
  • Make the values safe.
  • Get the current date.
  • Create and run an SQL INSERT statement.
  • Jump back to the admin menu.

The pattern is the same as before, except for the fourth line.

Here’s the code. I’ll go over it step by step in a moment.

<?php
//Save a new article.
//Input (all POST):
//  title: Title of the article.
//  body: Body of the article.
//  author: Author of the article.

$path_to_root = '..';
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);

//Get the form fields.
$title = stripslashes($_POST['title']);
$body = stripslashes($_POST['body']);
$author = stripslashes($_POST['author']);

//Make the fields safe.
$title = $db->escape_string($title);
$body = $db->escape_string($body);
$author = $db->escape_string($author);

//Prepare the publish date.
$when_published = date('Y-m-d');

//Create and run the SQL.
$query = "insert into articles
  (title, author, body, when_published)
  values ('$title', '$author', '$body', '$when_published')";
$db->query($query);

//Back to admin menu.
header('location:index.php');
exit();
?>

Figure 12. save-new-article.php

Let’s break it down.

Lines 2 to 6 are documentation. They explain what the page does, and what input it expects.

Database connection

The pattern is:

  • Connect to the database.
  • Get the values the user typed into the form.
  • Make the values safe.
  • Get the current date.
  • Create and run an SQL INSERT statement.
  • Jump back to the admin menu.

Lines 10 and 11 connect to the database. As before, the connection parameters (host, user name, etc.) are in a separate file.

Grabbing the form data to insert

What’s next?

  • Connect to the database.
  • Get the values the user typed into the form.
  • Make the values safe.
  • Get the current date.
  • Create and run an SQL INSERT statement.
  • Jump back to the admin menu.

Here’s some more code from save-new-article.php.

//Get the form fields.
$title = stripslashes($_POST['title']);
$body = stripslashes($_POST['body']);
$author = stripslashes($_POST['author']);

Part of Figure 12 (again). save-new-article.php

This gets the data the user typed into the form fields, and puts it into variables. The stripslashes() function gets rid of the backslashes that PHP adds to form field data.

Foiling the Evil Doer

What’s next?

  • Connect to the database.
  • Get the values the user typed into the form.
  • Make the values safe.
  • Get the current date.
  • Create and run an SQL INSERT statement.
  • Jump back to the admin menu.

Here’s the code for that step:

//Make the fields safe.
$title = $db->escape_string($title);
$body = $db->escape_string($body);
$author = $db->escape_string($author);

Part of Figure 12 (again). save-new-article.php

As before, this messes up an SQL injection attack.

Setting the date

What’s next?

  • Connect to the database.
  • Get the values the user typed into the form.
  • Make the values safe.
  • Get the current date.
  • Create and run an SQL INSERT statement.
  • Jump back to the admin menu.

One of the things we store in the record for each article is the publication date. Rather than making the user type it, we can ask PHP what the current date is, and use that.

Here’s the line that does it:

$when_published = date('Y-m-d');

The date() function gets today’s date. The Y-m-d stuff gives a format: four digits for year, then a dash, then two digits for month, then a dash, then two digits for day. This is the international format that MySQL usually expects dates to be in.

What happens to the formatted date? It goes into the variable $when_published. We can use it just like the variables that contain the form data ($title, $body, and $author).

Creating and running an INSERT statement

What’s next?

  • Connect to the database.
  • Get the values the user typed into the form.
  • Make the values safe.
  • Get the current date.
  • Create and run an SQL INSERT statement.
  • Jump back to the admin menu.

Here’s the code:

//Create and run the SQL.
$query = "insert into articles
  (title, author, body, when_published)
  values ('$title', '$author', '$body', '$when_published')";
$db->query($query);

Part of Figure 12 (again). save-new-article.php

This makes an SQL statement, and puts it into the variable $query. Again, the single quotes need to be right on line 29.

Line 30 sends the SQL to MySQL for execution.

Back to the administration menu

What’s next?

  • Connect to the database.
  • Get the values the user typed into the form.
  • Make the values safe.
  • Get the current date.
  • Create and run an SQL INSERT statement.
  • Jump back to the admin menu.

The end of save-new-article.php is:

header('location:index.php');

This tells the browser to jump back to index.php. save-new-article.php is in the /admin directory, so the browswer will jump to /admin/index.php.

So that’s it.

  • The user fills in a form and clicks its submit button.
  • The data is sent to a PHP page:

  • Connect to the database.
  • Get the values the user typed into the form.
  • Make the values safe.
  • Get the current date.
  • Create and run an SQL INSERT statement.
  • Jump back to the admin menu.

W00f!

Exercise: Adding jokes

Change the Jokes application so that users can add jokes.

Remember that there are two pages:

  • The form the user fills in (admin/add-joke.php).
  • The PHP program that saves the joke to the database (admin/save-new-joke.php).

The first one you already have. It was in the zip file you downloaded. Your job is to write save-new-joke.php.

You can base it on save-new-product.php from DogToys and save-new-article.php from DogRock.

Don’t forget to adjust library/db-connect.php to use the right connection parameters.

Once you’ve finished, you can compare your solution with mine. But don’t look at it now!

If you want to share your solution with other people, you’ll need to:

  • Export your database to your hosting server.
  • Upload your Jokes application.

(Log in to enter your solution to this exercise.)

Summary

  • There are two pages for adding a record: one page with a form the user fills in, and another page that adds the user’s data to the database.
  • The SQL INSERT statement does the work.
  • Use stripslashes() to remove backslashes that PHP adds to form data.
  • Use $db->escape_string() to foil SQL injection attacks.

What now?

You know how to add a record. But what happens when something goes wrong? Let’s see.


How to...

Lessons

User login

Log in problems? Try here


Dogs