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!


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


UPDATE Queries

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.

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:

$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.


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!