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 comprehensive exercise will demonstrate the SQL syntax for updating database records and guide you through building a professional update form with advanced features including checkboxes and hidden fields. You'll learn essential techniques for user data management that form the backbone of modern web applications, from content management systems to user account portals.

Update Syntax

Updating database records is straightforward, but precision is critical. When updating a user in the users table, the syntax follows this pattern:

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

This statement updates the users table, setting the firstName, lastName, and email to their corresponding values for the record where id equals 231. The WHERE clause is absolutely essential—without it, you would update every row in the database, potentially causing catastrophic data loss. Always specify which particular row you want to update. This is why implementing a unique id field in every table is not just best practice—it's a fundamental requirement for data integrity and safe operations.

A Simple Example

Let's implement a basic update operation to see these concepts in action. First, we need to identify a specific record in the users table to modify.

1. Access phpMyAdmin through your development environment:

   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 sidebar, click phpclass_yourname to access your database.

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

4. Review all records in the database and select one you'd like to modify, noting its id value. For consistency, you can choose the first record with id = 1.

5. Open update-easy.php from the phpclass folder in your code editor.

6. Initialize the variables for our update operation. At the top of the page, add this PHP block (replace the $id value with the id you selected):

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

7. Include the database connection script by adding this line:

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

8. Now we'll construct the UPDATE statement using prepared statement placeholders for security:

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

   Notice we're using ? placeholders—this is essential for prepared statements, which protect against SQL injection attacks.

9. Initialize and prepare the statement object. This follows the standard prepared statement pattern:

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

10. Bind the parameters to their respective placeholders:

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

11. Execute the statement and implement proper error handling:

    $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 file and test it in your browser:

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

    A blank page indicates successful execution—no news is good news in this case.

13. Return to phpMyAdmin and Browse the users table to verify the changes.

14. Locate the record with the id you updated—you should see your changes reflected in the database.

Update Form

Now we'll move beyond basic updates to build a sophisticated, real-world update form suitable for user administration systems. This approach mirrors what you'd find in professional applications like WordPress admin panels or customer relationship management systems.

1. Open userList.php from the update folder.

2. Load the page in your browser:

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

3. You'll see a comprehensive list of all records in the users table. The first column contains "Edit" links that route users to an update form. Notice the Subscribe column displays raw 1s and 0s—we'll transform these into user-friendly "Yes" or "No" values.

4. Return to your code editor.

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

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

   Instead of a traditional if-else statement, we'll use PHP's ternary operator—a powerful shorthand that's perfect for simple conditional displays like this.

6. Replace the existing code with this ternary operator implementation:

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

   The ternary operator uses three components: a test expression in parentheses, followed by a question mark, then two possible return values separated by a colon. The first value is returned if the test is true, the second if false. This creates cleaner, more readable code for simple conditions.

   Next, we'll create dynamic links that pass user IDs to our update form. The resulting anchor tag will look like this:

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

   The question mark initiates the query string, making everything after it available as URL variables through PHP's $_GET superglobal array.

7. Around line 43, find the static Edit link:

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

8. Add the query string parameter structure:

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

9. Complete the dynamic link by adding the PHP that outputs each user's unique ID:

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

10. Save your changes and reload the page:

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

11. Click several Edit links and observe how each directs you to userForm.php with the corresponding user ID in the URL—this is the foundation of record-specific editing.

Display Data in the Update Form

The next step is pre-populating form fields with existing user data—a crucial user experience feature that allows users to see current values before making changes.

1. Return to your code editor.

2. Open userForm.php from the update folder.

3. Examine the code at the top of the page—we've provided the SELECT statement and PHP logic to retrieve the user record. Note line 9 where we capture the URL variable:

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

   This binds the user ID from the URL to our prepared statement parameter, demonstrating how data flows from the user list through the URL to the database query.

4. Let's populate the first name field. Around line 36, you'll see an empty value attribute. Fill it with the user data:

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

5. Test this change by saving the file and navigating to:

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

6. Click any Edit link—the first name field should now display the existing user data.

7. Return to your code editor to implement the same pattern for the remaining fields.

8. Around line 40, populate the last name field:

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

9. Around line 44, populate the email field:

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

10. For the comments textarea (around line 54

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

    Important: Keep this on a single line in your code. Whitespace inside textarea elements is preserved and displayed to users, so any extra spaces or line breaks will appear in the form.

11. Save your changes and test the complete form:

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

12. Click any Edit link—all text fields should now display the existing user data, providing a seamless editing experience.

Display Checkboxes

Handling checkboxes requires a different approach than text fields. We need to conditionally add the checked attribute based on the user's previous selections. This is where conditional logic becomes essential for maintaining form state.

1. Return to your code editor.

2. In userForm.php, locate the subscribe checkbox around line 59. Add a conditional statement that checks the subscription status:

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

3. Save and test your changes:
   • Mac: localhost:8888/phpclass/update/userList.php
   • Windows: localhost/phpclass/update/userList.php

4. Click an Edit link and observe whether the subscribe checkbox reflects the user's current subscription status.

5. The publications checkboxes require a more sophisticated approach since they're stored as a delimited string. We need to search within that string for specific values. Return to your code editor.

6. Around line 49, find the Daily Racing Form checkbox and add this conditional framework:

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

7. Complete the condition using PHP's stristr() function, which performs case-insensitive string searches:

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

   The stristr() function searches through the $publications variable for the specified string and returns the matched portion if found, making it perfect for checking multiple selections stored in a single field.

8. Apply the same logic to the Elle checkbox, adapting the search string:

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

9. Test the complete checkbox functionality:

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

10. Click various Edit links, paying attention to which publications each user previously selected. The checkboxes should accurately reflect their saved preferences.

Adding a Hidden Field

Hidden fields play a crucial security and functionality role in update forms. The user ID must travel with the form data to ensure we update the correct record, but it should remain invisible and unmodifiable by users to prevent data corruption or malicious manipulation.

1. Return to your code editor.

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

3. Add a hidden input field immediately after the form opening tag:

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

4. Populate the hidden field with the current user's ID:

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

5. Save your changes and verify the form structure:

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

Building the Update SQL

The final step involves processing the submitted form data and executing the database update. This requires careful coordination between form validation, data processing, and SQL execution.

1. Return to your code editor.

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

   This file handles form submission, validates input data, and processes the $_POST values. It includes security measures and data sanitization that you'd find in production applications.

3. Navigate to line 66 and locate this comment:

   //update user in database

4. Include the update processing script:

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

5. Save the file and open updateUser.php from the update folder.

   This file contains the database connection logic and prepared statement framework. We need to complete the SQL statement and parameter binding.

6. Around line 13, find the empty $SQL variable and add the complete UPDATE statement:

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

   This statement updates all user fields while using the WHERE clause to target the specific record by ID.

7. Around line 28, find the parameter binding comment:

   //bind params here

8. Replace the comment with the parameter binding code:

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

   Note the parameter types: six strings ('s') for text fields and one integer ('i') for the ID field. This type specification is crucial for data integrity and security.

9. Save your changes and test the complete update system:

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

10. Click Edit next to any user record.

11. Make several changes to different form fields and click Update User. The system should return you to the user list with all changes properly saved and displayed.

12. Return to your code editor and close all open files—you've successfully built a complete database update system.