this page because I am probably still making changes
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.
Editing existing records involves performing an UPDATE query,
which has the following syntax:
UPDATE tableName SET column1=value1, column2=value2, ...
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
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:
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
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!