SQL: Update: Free PHP & MySQL Tutorial

Topics covered in this PHP & MySQL tutorial:

The UPDATE statement, Update form, Display data in the update form, Display checkboxes, Hidden fields

Exercise Overview

This exercise will show you the SQL syntax for updating a record in a database as well as how to use a form to update user information including checkboxes and hidden fields.

Update Syntax

Updating a record in the database is simple. If we wanted to update a user in the users table, the syntax might look like:

UPDATE users
SET 
   firstName = 'Albert',
   lastName = 'Einstein',
   email = 'albert@someemail.com'
WHERE
   id = 231

This says to update the users table and set the firstName, lastName, and email to their corresponding values, where the id equals 231. It is extremely important to always add the where line, otherwise you would update every row in the database! You must always specify which particular row you’d like to update. That’s why having a unique id field in every table is so important, otherwise you might update the wrong data.

A Simple Example

Let’s see this in action. First, we need to look up an id in the users table that we are going to modify. It doesn’t really matter which id you choose.

1. Go to phpMyAdmin:

   Mac
   • Switch to MAMP PRO.
   • Click the WebStart button.
   • In the page that opens, click on Tools and choose phpMyAdmin.
   Windows
   • Open a browser and go to http://localhost
   • On the start page, in the Tools section click phpMyAdmin.

2. On the left click phpclass_yourname to go to your database.

3. Next to the users table click the Browse icon to view the records in the database.

4. You’ll see all the records in the database. Find a record you’d like to modify and make a note of the id. It doesn’t matter which one you choose. If in doubt just choose the first one, id = 1.

5. Open update-easy.php from the phpclass folder.

6. Set up the variables we are going to add. At the top of the page add the bold code (for $id set it to whatever id you chose earlier):

   <?php 
   
      $firstName = 'Update';
      $lastName = 'Me';
      $email = 'updated@update.com';
      $id = 1;
   
   ?>
   

7. Add the connection script by adding the following bold code:

   $firstName = 'Update';
   $lastName = 'Me';
   $email = 'updated@update.com';
   $id = 1;
   
   require_once('inc/dbConnect.php');
   

8. Next, we’ll add the UPDATE statement. Add the bold code:

   require_once('inc/dbConnect.php');
   
   $sql = "UPDATE users
          SET 
             firstName = ?,
             lastName = ?,
             email = ?
   
          WHERE
             id = ?
          ";
   

   Remember, because we are using prepared statements we use ? placeholders.

9. The rest will be identical to the other prepared statements we’ve written, first connecting and initializing the statement, then preparing the SQL. Below the $sql, add the bold code:

   $stmt = $conn->stmt_init();
   $stmt->prepare($sql);
   

10. Then bind the parameters:

    $stmt = $conn->stmt_init();
    $stmt->prepare($sql);
    $stmt->bind_param('sssi', $firstName, $lastName, $email, $id);
    

11. Lastly, execute the statement and add some basic error checking:

    $stmt = $conn->stmt_init();
    $stmt->prepare($sql);
    $stmt->bind_param('sssi', $firstName, $lastName, $email, $id);
    $stmt->execute();
    if ($stmt->error) {
       echo $stmt->error;
       exit();
    }
    

12. Save the page and then in a browser go to:

    • Mac: localhost:8888/phpclass/update-easy.php
    • Windows: localhost/phpclass/update-easy.php

    If all goes well you’ll see a blank page.

13. Switch back to phpMyAdmin and Browse the users table.

14. Look for the id you updated and you’ll see that the changes have been made.

Update Form

For the rest of this exercise, we’ll explore a more useful real world example and build an update form that could be used for a user admin.

1. Open userList.php from the update folder.

2. In a browser go to:

   • Mac: localhost:8888/phpclass/update/userList.php
   • Windows: localhost/phpclass/update/userList.php

3. You’ll see a list of all the records in the users table. The first column contains links that say “Edit” that will bring the user to an edit page where they can update the user info. Also notice that the last column, Subscribe, contains 1s and 0s. We’ll change this to a more useful Yes or No.

4. Switch back to your code editor.

5. Around line 49 find the code that displays the $subscribe variable:

   <td><?php echo $subscribe; ?></td>
   

   While we could use an if statement to display Yes if $subscribe is equal to 1, and No if it is not, this is a perfect time to introduce you to PHP’s ternary operator. The ternary operator serves as shorthand notation for if statements.

6. Change the code as shown in bold. (Don’t forget to delete the semi-colon and add parentheses around $subscribe!)

   <td><?php echo ($subscribe) ? 'Yes' : 'No' ?></td>
   

   The ternary operator uses three expressions separated by a question mark and a colon. The question mark follows the test expression (what’s in parentheses) and can be thought of as asking, “Is it true?”. The colon then separates the two possible values; the first is chosen if the test expression is true, the second if it is false.

   In our example the ternary operator evaluates $subscribe and returns the first response (Yes) if it evaluates to true; the second (No) if it is false. If $subscribe is equal to 0, or is NULL it will evaluate as not true.

   Next, we’ll make a link that brings the user to an update form. To do that, we need to pass the user id to a form page. We’ll do that by putting the id in the URL. The anchor tag we’re going to build will look like this:

   <a href="userForm.php?id=3">Edit</a>
   

   The special thing about this URL is that it has a question mark after the page name, followed by id=3. Anything after the question mark will be treated as a URL variable. To access the URL variable on the following page, we’ll use PHP’s $_GET array.

7. Around line 43, find the Edit link. Currently it links to userForm.php, but does not have a URL variable:

   <td><a href="userForm.php">Edit</a></td>
   

8. Let’s add the start of the variable. Add the bold code:

   <td><a href="userForm.php?id=">Edit</a></td>
   

9. Next, add the PHP that will echo each user’s id. Directly after the id= add the bold code:

   <td><a href="userForm.php?id=<?php echo $id; ?>">Edit</a></td>
   

10. Save the page and then in a browser go to:

    • Mac: localhost:8888/phpclass/update/userList.php
    • Windows: localhost/phpclass/update/userList.php

11. Try clicking a few of the links and notice that you are taken to userForm.php and that the id of the user you click is in the URL.

Display Data in the Update Form

1. Switch back to your code editor.

2. Open userForm.php from the update folder.

3. Look at the top of the page and note that we’ve already written the SQL and PHP code for you to display a user record. This is a similar SELECT statement to the ones you’ve made in previous exercises, although one thing to note is that on line 9 we reference the URL variable like so:

   $stmt->bind_param('i',$_GET['id']);
   

   This is the one parameter in the select statement, the user id. Here we are telling the bind_param() function to expect one integer, the ‘id’ in the $_GET array (which is passed on from the preceding page).

   Because the select statement is already written, we can focus on just display data.

4. First let’s output the first name. Around line 36 notice that the value="" is empty. We’ll fill it with the user info. Add the bold code:

   <input name="firstName" type="text" id="firstName" size="40" 
   value="<?php echo $firstName; ?>">
   

5. Save the page and then in a browser go to:

   • Mac: localhost:8888/phpclass/update/userList.php
   • Windows: localhost/phpclass/update/userList.php

6. Click one of the Edit links. You’ll be taken to the form, and the first name should already be filled out.

7. Let’s do the same for the last name. Switch back to your text code editor.

8. Around line 40 add the bold code:

   <input name="lastName" type="text" id="lastName" size="40" 
   value="<?php echo $lastName; ?>">
   

9. And the same for the email. Around line 44 add the bold code:

   <input name="email" type="text" id="email" size="40" value="<?php echo $email; ?>">
   

10. Do the same for the comments. Around line 54 add the bold code:

    <textarea name="comments" id="comments" 
    cols="38" rows="5"><?php echo $comments; ?></textarea>
    

    Note that this should actually all be on one line! It’s just a bit too long to display in one line in this book, but because white space in a <textarea> matters, there shouldn’t be any space between the <textarea> tags.

11. Save the page and then in a browser go to:

    • Mac: localhost:8888/phpclass/update/userList.php
    • Windows: localhost/phpclass/update/userList.php

12. Click one of the Edit links. You’ll be taken to the form, and the first name, last name, email, and comments should be filled out.

Display Checkboxes

Displaying whether checkboxes have been checked or not is pretty easy. We just have to test if the user had selected a particular option and then add a checked to the <input> tag. Let’s do it for the subscribe tag first.

1. Switch back to your code editor.

2. In userForm.php, around line 59, find the subscribe input. Add an if statement that checks to see if $subscribe is true and if it is, output checked. Add the bold code:

   <input name="subscribe" type="checkbox" id="subscribe" 
   value="1" <?php if ($subscribe) {echo 'checked';}?>>
   

3. Save the page and then in a browser go to:
   • Mac: localhost:8888/phpclass/update/userList.php
   • Windows: localhost/phpclass/update/userList.php

4. Click one of the Edit links and make a note of whether the subscribe input is checked or not.

5. The same principle will work for the publications checkboxes, except that first we must search through the $subscribe string to see if they selected the checkbox earlier. Switch back to your code editor.

6. In userForm.php, around line 49, find the checkbox input for Daily Racing Form. Add the bold code:

   <input name="publications[]" type="checkbox" id="publications_drf" value="Daily Racing Form" <?php if () { echo 'checked';} ?>> Daily Racing Form</label>
   

   This is a blank if statement. In the next step we will add the function that searches through a string to see if the user selected the option.

7. In between the empty parentheses of the if () add the bold code:

   <?php if ( stristr($publications,'Daily Racing Form') ) { echo 'checked';} ?>
   

   stristr() is a case-insensitive search function. In this case we are searching through the $publications variable for the string ‘Daily Racing Form’. If it finds it, it outputs checked.

8. Add the same code for the Elle input, except replace Daily Racing Form with Elle. Inside the Elle input, add the bold code:

   <input name="publications[]" type="checkbox" id="publications_elle" 
   value="Elle" <?php if ( stristr($publications,'Elle') ) { echo 'checked';} ?>> Elle</label>
   

   This searches through the $publications variable for the string 'Elle' and outputs checked if it finds it.

9. Save the page and then in a browser go to:

   • Mac: localhost:8888/phpclass/update/userList.php
   • Windows: localhost/phpclass/update/userList.php

10. Click one of the Edit links making note of which publications the user selected. When you are taken to the form the publications checkboxes should correspond.

Adding a Hidden Field

The update form is nearly done, but the user id still needs to be passed along with the rest of the user information. We do that by adding it to a hidden field in the form. This way the user cannot accidentally change the id, thereby making sure that the proper user info is updated.

1. Switch back to your code editor.

2. In userForm.php, around line 32, find the opening <form> tag.

3. Make a new line below it and add the following bold code:

   <form action="form-action.php" method="post" name="signup" id="signup">
   <input type="hidden" name="id" value="">
   

   This is a hidden input field with the name id.

4. To add the PHP that echoes the user’s id, add the following bold code:

   <input type="hidden" name="id" value="<?php echo $id ?>">
   

5. Save the page and then in a browser go to:

   • Mac: localhost:8888/phpclass/update/userList.php
   • Windows: localhost/phpclass/update/userList.php

Building the Update SQL

1. Switch back to your code editor.

2. Open form-action.php from the update folder.

   The update form submits to the page form-action.php. This is similar to the action page we’ve used in previous exercises. It loops through the form and validates the input. It also renames the $_POST values to the values we give in the $expected array.

3. Scroll down to line 66 and find the comment:

   //update user in database
   

4. Underneath that comment add the bold code:

   //update user in database
   require_once('updateUser.php');
   

5. Save the page.

6. Open updateUser.php from the update folder.

   To save some time, we’ve already written most of the PHP code for you. All the connection code is already written. We just need to write the SQL and then bind the parameters.

7. Around line 13 find the empty $sql variable and add the following bold code:

   $sql = "UPDATE users
           SET 
             firstName = ?,
             lastName = ?,
             email = ?,
             publications = ?,
             comments = ?,
             subscribe = ?
   
          WHERE
             id = ?
          ";
   

   This says to update the users table and set the following values where the id is equal to a certain value.

8. Around line 28 find the comment:

   //bind params here
   

9. We will bind the parameters there. Replace the comment by adding the following bold code:

   $stmt->bind_param('ssssssi', $firstName, $lastName, $email, $publications, $comments, $subscribe, $id);
   

   Note that all of the variables are marked as a s tring, except for the id which is an i nteger.

10. Save the page and then in a browser go to:

    • Mac: localhost:8888/phpclass/update/userList.php
    • Windows: localhost/phpclass/update/userList.php

11. Click Edit next to one of the users.

12. Make some changes in the form and click Update User. You’ll be taken back to the list of users and should see the updates!

13. Switch back to your code editor.

14. Close any open files. We’re done for now.