Updating and Deleting Records in Tables

Let's say that one of your registered users asks you to change his name, complaining that it contains a typo. Instead of replacing a whole record for that user with a new value for the username field, you'd probably just want to change his name in the existing record. As you've seen, this is where the UPDATE command comes into play. You'd use a statement like this:

UPDATE user SET username = 'Darren Ebbs'
            WHERE username = 'Daren Ebbs'

This changes all instances of the value Daren Ebs in the username field to Darren Ebbs.

If you want to throw away an existing record from a table, use the DELETE command:

DELETE FROM access_log WHERE page = 'who.html'

This deletes any record with page = ' who.html'.

The following UPDATE query is an example of what you should not do. It updates every record in the user table in such a way that any user can log on to your site using the password comeonin:

UPDATE user SET userpassword = password('comeonin')

Worse still, the following query empties the user table:

DELETE FROM user

Even an experienced database manager can make this kind of blunder—it just takes a bit of carelessness or the accidental dropping of a pen onto the Enter key while typing in a query, so do be careful when using these commands.

Suppose you want to delete all access log records from the year 1999 because they're no longer needed. You'd use a query that looks like this:

mysql> DELETE FROM access_log WHERE accessdate LIKE '1999%';

A user might want to change his password from time to time. The following query changes his password to guessit:

mysql> UPDATE user SET userpassword = password('guessit')
    -> WHERE userid = 'Greeny';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

The server reports that one record matched the specified condition and has therefore been changed. If the server finds no matching record, no update operation is performed:

mysql> UPDATE user SET userpassword = password('guessit')
    -> WHERE userid = 'Ginger';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

You can utilize existing values to update a record. For example, the visitcount field keeps track of how many times a specific user has visited a certain Web page. If the user visits that page again, you need some way to increment its value. You might do this by fetching the current value from the access_log table, incrementing it, and putting it back again. However, you can make the job a lot simpler by using the existing value in your UPDATE query. The following query does exactly what you want—it increments the visitcount field for user Dodge on the /score.html page:

mysql> UPDATE access_log SET visitcount = visitcount + 1
    -> WHERE user_id = 'Dodge' AND page = '/score.html';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

You'll need to update the lastaccesstime field separately. We'll discuss this later on in the chapter.

String values can be modified in a similar way:

mysql> UPDATE user SET userid = concat(userid, '_1');
Query OK, 1 row affected (0.00 sec)
Rows matched: 6 Changed: 6 Warnings: 0

This query uses the MySQL server function to join strings, appending the string _1 to every userid field value. The user ID Dodge, for example, becomes Dodge_1. This method comes in handy when a new user requests a user ID that is already in use, and you want to suggest an alternative.

If you were to use SET userid = userid + '_1', it would set the userid to 0, rather than bundy_1.

Multiple assignments involving the same field are also possible:

mysql> UPDATE access_log
    -> SET visitcount = visitcount + 1, visitcount = visitcount * 2;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6 Changed: 6 Warnings: 0

Assignments are evaluated from left to right, so the second assignment will use the incremented visitcount value from the first assignment. That is, it increments and then doubles every value in the visitcount field.

By now you're probably used to seeing a query report at the MySQL command line. If you use a DELETE or UPDATE query, the server automatically tells you how many rows were affected, how long it took to execute the query, counts for rows matched and changed, and any warnings produced.

You can get at the first of these from your PHP scripts by using the function mysql_affected_rows(). This takes a link identifier and returns the number of rows affected by the previous SQL query—a DELETE query in the following example:

$link_id = db_connect("sample_db");
mysql_query("DELETE FROM access_log
             WHERE page = '/penalties.html'");
$num_rows = mysql_affected_rows($link_id);
echo "$num_rows user(s) have been deleted.";

If just one user had visited the penalties page, this code would echo: