Section 12.5. Updating Data

12.5. Updating Data

Since you've been inputting table data, you can change existing records. You'll probably only do this if there are errors in your data, or in the instance that user data has changed and needs to be updated in the database. Updates are handled as shown in Example 12-8.

Example 12-8. Updating a field

 <?php require_once('db_login.php'); require_once('DB.php'); $connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database"); if (DB::isError($connection)){ die ("Could not connect to the database: <br />". DB::errorMessage($connection)); } $query = "UPDATE `books` SET `pages`=558 WHERE `title_id`=2"; $result = $connection->query($query); if (DB::isError($result)){ die("Could not query the database: <br />".$query." ".DB::errorMessage($result)); } echo "Updated successfully!"; $connection->disconnect(); ?> 

If you have multiple columns to edit in one record at a single time, you'd separate the code with a comma (,). Again, you could have used a dynamic value such as a form input in the WHERE clause. If you use a WHERE clause, you'd have to specify what rows the update affects; otherwise, the change applies to every row.

Updates and deletions are two of most important reasons to use a primary key. The primary key number, which never should change, can be a point of reference in the WHERE clause. In Figure 12-9, you see the new value in the books table from the mysql client.

Figure 12-9. The new page count of 558 appears in the table

Since updates and deletions are so important, you'll perform a delete operation. As a precaution against accidentally updating too many rows, apply a limit clause with your update.

Never update a primary key column. This value should never change. If you change a primary key in one table, it could affect the data in another table.