Where are we?

You’ve created an administration menu, that lets users delete and edit records. You know how to program the delete part. Let’s talk about the edit part.

This lesson’s goals

In this lesson, learn:

• The add and edit forms are similar, the edit form has data already in it, and passes around an id number.

• The SQL UPDATE statement changes data in an existing record. Usually, it has a single primary key value in its WHERE clause.

Editing is like adding

Here’s a screenshot of someone editing a product record:

Figure 1. Editing a product record

The user changes the values and clicks the button.

Here’s the form for adding a product:

Figure 2. Adding a product

They’re almost the same. They have the same form fields, and the same client-side validation.

The main differences between add and edit are:

• Records being edited already exist in the database. Records being added do not. Therefore…

• Records being edited already have a primary key value. For products, that’s a product_id. New records don’t have a product id. MySQL will choose an id value when it adds a record to the database. (Recall that we made product_id an auto_increment field.)

• The edit form has the current data in the fields. All of the fields on the add form are blank.

• To edit a record, you use the SQL statement UPDATE. To add a new record, we use the INSERT statement.

The UPDATE statement

Let’s look at the dogs table again. Each row has data about one dog. The table has the fields:

• dog_id: the dog’s id number (integer, primary key).

• name: name of the dog (character).

• breed: breed of the dog (character).

• weight: weight of the dog in pounds (integer).

Here’s some sample data:

Figure 3. Dog data

Suppose Brian puts on two pounds. Here’s an SQL statement to show the change:

update dogs
   set weight = 53
   where dog_id = 5;

The statement tells MySQL three things:

• What table to change: dogs.

• Which record to change: the one with a dog_id of 5.

• What the new field values are: change weight to 53.

Most update statements just change one record, using a primary key value.

You can change text values, like this:

update dogs
   set name = 'Fido'
   where dog_id = 5;

Don’t forget the quotes around the text.

You can change more than one field at a time. For example:

update dogs
   set name = 'Fido',
     weight = 56
   where dog_id = 5;

Let’s see how we can use the update statement.

DogToys: Editing a product

Suppose a user wants to edit product data on the DogToys site. Let’s look at the workflow.

The workflow

The user starts at the admin menu, and selects a product to edit:

Figure 4. Admin menu

Here’s a sample Edit link:

<a href='edit-product.php?id=2'>Edit</a>

Clicking the link jumps to the page edit-product.php, passing an id of 2. This is the product_id of the product the user wants to edit.

edit-product.php shows a form like this:

Figure 1 (again). Editing a product record

The user changes the data and clicks the Save button. If all the validation checks are passed, the new data is saved into the database, and the user is taken back to the main menu.

Here’s a picture of the workflow:

Figure 5. Workflow for editing a product record

The edit form

Let’s look at the code for edit-product.php, the edit form. Here’s what the code has to produce.

Figure 1 (again). Editing a product record

Each of the form fields shows the current data.

Here’s how edit-product.php works:

• Get the id of the product.
• Look up the data for that product.
• Show the form, with product data in each field.

Here’s the page. Some of the code has been removed for simplicity.

<?php
...
//Connect to the database.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
//Fetch product data.
$product_id = $_GET['id'];
$query = "select name, description,
      image_file_name, price
    from products
    where product_id = " . $product_id;
$record_set = $db->query($query);
$row = $record_set->fetch_assoc();
//Extract fields.
$name = $row['name'];
$description = $row['description'];
$image_file_name = $row['image_file_name'];
$price = $row['price'];
?>
...
<form id="edit_product_form" method="post" action="save-edited-product.php">
  <p>
    Name<br>
    <input type="text" name="name" id="name" size="30"
           value="<?php print $name; ?>">
  </p>
  <p>
    Description<br>
    <textarea name="description" id="description" rows="5" cols="30"><?php print $description; ?></textarea>
  </p>
  <p>
    Image file name<br>
    <input type="text" name="image_file_name" id="image_file_name" size="30"
           value="<?php print $image_file_name; ?>">
  </p>
  <p>
    Price<br>
    <input type="text" name="price" id="price" size="10"
           value="<?php print $price; ?>">
  </p>
  <p>
    <input type="hidden" name="product_id" value="<?php print $product_id; ?>">
    <button type="submit">Save</button>
  </p>
</form>

Figure 6. edit-product.php

Line 4 to 5 connect to the database.

Line 7 gets the product id from the URL. Recall that the URLs are like this:

edit-product.php?id=2

Lines 8 to 11 create an SQL statement that looks up the data for the product. For example, if id was 2, the query would be:

select name, description, image_file_name, price
   from products
   where product_id = 2

Line 12 runs the query. The query only returns one row, because the where clause tests the primary key.

Line 13 fetches the row. Lines 15 to 18 get the individual fields, and put them into variables. For example:

$name = $row['name'];

Here’s how that data is used:

<input type="text" name="name" id="name" size="30"
   value="<?php print $name; ?>">

If $name contained Frisbee, this line would become:

<input type="text" name="name" id="name" size="30"
   value="Frisbee">

When the browser renders the field, it will put the value Frisbee into it:

Figure 7. Name field rendered

This page has to send the new data to save-product.php. It needs to include the product_id. But the id is not actually shown in the form. The user can’t change it, so there’s no point in showing it.

So how do you put some data into a form so that it can be sent, but not have it visible to the user?

The solution: use a hidden field. Like this:

<input type="hidden" name="product_id" value="<?php print $product_id; ?>">

The product_id will travel along with the rest of the form fields. But the user won’t see it.

W00f!

Saving the data

Here’s the workflow again:

Figure 5 (again). Workflow for editing a product record

When the user clicks the Save button, the browser sends the data to save-edited-product.php. This is the action property of the form in line 21 in Figure 6:

<form id="edit_product_form" method="post" action="save-edited-product.php">

Here’s what save-edited-product.php has to do:

• Get the new data for the product.
• Make an update statement.
• Run it.
• Jump back to the admin menu.

Here’s the code. Error checking and some other code has been omitted.

<?php
$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.
$product_id = stripslashes($_POST['product_id']);
$name = stripslashes($_POST['name']);
$description = stripslashes($_POST['description']);
$image_file_name = stripslashes($_POST['image_file_name']);
$price = stripslashes($_POST['price']);
//Make the fields safe.
$product_id = $db->escape_string($product_id);
$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 = "update products
  set name = '$name',
  description = '$description',
  image_file_name = '$image_file_name',
  price = $price
  where product_id = $product_id";
$db->query($query);
//Back to admin menu.
header('location:index.php');
exit();
?>

Figure 8. save-edited-product.php

Lines 4 and 5 connect to the database.

Lines 7 to 11 get the form data, including the product_id passed as a hidden field. Note that stripslashes() was used to undo PHP’s “helpful” insertion of backslashes.

Lines 13 to 17 make the data safe from Evil Doers. It defuses SQL injection attacks.

Lines 19 to 24 creates the SQL query. It includes quotes (’) for text fields, and a where clause to select the right product.

Line 25 runs the query.

Line 27 jumps back to the admin menu.

That’s it for DogToys. Let’s move on.

DogRock: Editing an article

Workflow

The workflow for editing an article is the same as the workflow for editing a product:

• User clicks the Edit link in the admin menu.

• Browser shows an edit form.

• User changes the data and clicks the Save button.

• A PHP page saves the data.

The edit form

Here’s what the form looks like:

Figure 9. Editing an article

Here’s the code, with some stuff omitted:

<?php
//Connect to the database.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
//Fetch article data.
$article_id = $_GET['id'];
$query = "select title, body, when_published, author
    from articles
    where article_id = " . $article_id;
$record_set = $db->query($query);
$row = $record_set->fetch_assoc();
//Extract fields.
$title = $row['title'];
$body = $row['body'];
$when_published = $row['when_published'];
$author = $row['author'];
//Format the date.
$when_published = date('F j, Y', strtotime($when_published));
?>
<form id="new_article_form" method="post" action="save-edited-article.php">
  <p>
    Title<br>
    <input type="text" name="title" id="title" size="40"
           value="<?php print $title; ?>">
  </p>
  <p>
    Author<br>
    <input type="text" name="author" id="author" size="40"
           value="<?php print $author; ?>">
  </p>
  <p>
    When published<br>
    <input type="text" name="when_published" id="when_published" size="40"
           value="<?php print $when_published; ?>">
  </p>
  <p>
    Body<br>
    <textarea name="body" id="body" rows="8" cols="40"><?php print $body; ?></textarea>
  </p>
  <p>
    <input type="hidden" name="article_id" value="<?php print $article_id; ?>">
    <button type="submit">Save</button>
  </p>
</form>

Figure 10. edit-article.php

Lines 3 and 4 connect to the database.

Lines 6 gets the id of the article wants to edit. Lines 7 to 9 create the SQL query, which includes the article’s primary key. Line 10 runs the query, and line 11 gets the row fetched by MySQL.

Lines 13 to 16 extract the individual fields from the row. Line 18 formats the date to a familiar format.

The form is then shown. Each field’s value property puts the current value into the field. Line 41 adds the article-id as a hidden field, so the id will get passed to the page that saves the data.

Saving the data

Here’s save-edited-article.php, the program that saves the new article data.

<?php
$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.
$article_id = stripslashes($_POST['article_id']);
$title = stripslashes($_POST['title']);
$body = stripslashes($_POST['body']);
$author = stripslashes($_POST['author']);
$when_published = stripslashes($_POST['when_published']);
//Make the fields safe.
$article_id = $db->escape_string($article_id);
$title = $db->escape_string($title);
$body = $db->escape_string($body);
$author = $db->escape_string($author);
$when_published = $db->escape_string($when_published);
//Format the date.
$when_published = date('Y-m-d', strtotime($when_published));
//Create and run the SQL.
$query = "update articles
  set title = '$title',
  author = '$author',
  body = '$body',
  when_published = '$when_published'
  where article_id = $article_id";
$db->query($query);
//Back to admin menu.
header('location:index.php');
exit();
?>

Figure 11. save-edited-article.php

Lines 4 and 5 connect to the database. Lines 7 to 11 get the data passed into the form, and strips the excess backslashes with stripslashes().

Lines 13 to 17 sanitize the data. Line 19 converts the publication date into the format MySQL prefers.

Lines 21 to 26 create the SQL update statement. Line 27 runs the query.

Line 29 jumps back to the admin menu.

W00f!

Patterns

Here’s the pattern for the edit page itself.

[node:pattern/updating-database-record noterms]

Summary

• The add and edit forms are similar, the edit form has data already in it, and passes around an id number.

• The SQL UPDATE statement changes data in an existing record. Usually, it has a single primary key value in its WHERE clause.

What now?

W00f!

Time for some more exercises.