Overview of This Lesson

Refresh this page because I am probably still making changes to it.

In an earlier lesson you learned how to do INSERT queries to add or insert new records into a database table. An UPDATE query can be used to edit an existing record by setting one or more of the record's columns to new/different values. Running an UPDATE query in a PHP program is almost exactly the same as an INSERT query: the only thing that's different is the SQL!

Pre-Requisites

Make sure you've completed the lessons on CRUD Operations - Create and Read before doing this lesson.

Resources

MySQL Manual: UPDATE Statement
W3Schools: SQL UPDATE Statement

UPDATE Queries

Note

Need to back up your data on cPanel or your local server? You might wish to take a copy of your records just in case you really screw up. One easy way to take a copy of your data that can easily be restored is to get PHPMyAdmin to create an SQL query for you that adds all the records back into the table:

Go to the Browse button of your table.
Below the list of records, click the "Check All" checkbox to select all the records, then look to the far right and click on the "Export" link.
In the "Format" drop-down list, select SQL (it should already be selected).
Click the GO button.
You'll be prompted to save the query as an .sql file: save it somewhere safe where you won't forget it!

To restore your records, make sure your table is empty, and then use Import button to import your saved data. You only need to select the file on your computer (the .sql file you saved), the rest of the default options should be fine.

Editing existing records involves performing an UPDATE query, which has the following syntax:

UPDATE tableName SET column1=value1, column2=value2, ...
    WHERE someColumn=someValue;

In this statement, you're setting one or more columns to a new value. colum1=value1 sets column1 to a new value (value1). column2=value2 sets column2 to a new value (value2).

You must include the WHERE clause so that SQL knows which record to update. If you're editing one specific record, this will likely be the primary key column, since it allows you to uniquely identify a specific record. For example:

UPDATE Inventory SET price=2.95 WHERE id=2;

This statement updates the price field of the record with the id value of 2. Note that this example sets only one column's value. If you were getting your new column values from form input, you wouldn't know which fields the user edited and which they left alone, so you would update all the columns:

$sql = "UPDATE Inventory SET name=:name, price=:price, quantity=:qty
   WHERE id=:id;

Notice also that we're using parameters for all the "unknown values", even the id value. You generally don't allow the user to ever edit the primary key field, but it's good practice to use parameters for all variable-values in an SQL query.

If there is no record that matches the WHERE clause, or any other error occurs while attempting to perform the update query, the "rows affected" will be 0.

Important!

Watch Out!!

If you forget to use the WHERE clause in an UPDATE query, the query will update ALL of your records! For example, if you use the query

UPDATE Music SET artist='Various Artists';

then ALL records in your Music table will be given the value "Various Artists" in the artist field!

You can update several records at the same time, if appropriate. For example, this query updates the price of all items that have a price of $4 and changes the price to 3.99:

UPDATE Inventory SET price=3.99 WHERE price=4.0;

This will update every single record in the Inventory table that has a current price of 4.0, so if you have more than one record with a price of 4.0, you'll notice that it indicates several records were updated.

Practice Update Queries: use the SQL window to edit some existing records with UPDATE queries.

Updates in PHP

You can set up and execute an update query the same way you would for an insert query:

<?php
$sql = "$sql = "UPDATE Inventory SET name=:name, price=:price, quantity=:qty
    WHERE id=:id;";
$statement = $dbConn->prepare($sql);
$params = [
    ":name" => $name,
    ":price" => $price,
    ":qty" => $quantity
];
$result = $statement->execute($params);
if ($dbConn->rowCount() == 1) {
    // success
}
?>

As with an INSERT query, the execute() function returns true if the query succeeded and false if the query failed. You can also use $dbConn->rowCount() function if you need to know how many rows were successfully updated.

Exercise

In the previous lesson you created a page that loaded a list of Course records with radio buttons. Add the code to take the selected record and load it up in the your "Add Course" form so the user can edit it. The form's submit button should save the updated record.

This is trickier than it sounds: you're using the same form you used for adding records: how is your form processing code going to know if this is an add or an edit? For one thing, when adding a new record, it doesn't have a record ID. But an existing record that's being edited does have an ID! You can use a session variable to store your record and then use that to test if you're doing an edit or an add!