Recently I programmed the following screen in PHP:

• The user logs in
• The user has a subscription
• The subscription has a number of states (“terminate”, “auto-extend”, ..)
• There is a screen allowing the user to change this state
• The screen is a set of radio buttons – each radio button relates to one state
• The user clicks on the radio-button representing the state they wish, clicks “ok”, and the new state gets saved to the database

Not rocket science eh? Well, unbelievably my implementation of the above had a bug. How on earth was that possible?

The bug was the following: If you changed the state, everything worked fine. But if you chose the same state as is already selected, an Exception gets thrown.

Initially I suspected a simple coding mistake. When I looked at the code, everything looked right. I had used the following “algorithm”:

• Update the “subscription” row using SQL
• Check the result of the SQL statement, that exactly 1 row was updated (in case e.g. id referenced a non-existing subscription, which would be an error)

I used the PHP function mysqli_affected_rows for that and unbelievably that has the following functionality: it only returns the number of changed rows i.e. the number of rows:

• Matching the where clause, and
• Currently having values different to those values being written to the row.

I can’t imagine a case where one would want to know that. I couldn’t find any function to return the number of rows matching, independent of if the values were changed or not. (The older version mysql_affected_rows exhibits the identical functionality.)

So I had to write the following function:

/**
 * Returns the number of rows which matched the WHERE
 * clause on the last UPDATE statement. This is not the
 * same as mysqli_affected_rows, which only returns the
 * number of changed rows.
 */
public static function DbUpdatedRows() {
    $link = self::DbGetLink();  // mysqli object
    $info = mysqli_info($link);
    if (preg_match('/Rows matched: (\d+) +Changed/',
            $info, $matches))
        return $matches[1];
    throw new Exception("DbUpdatedRows called although ".
        "it doesn't look like an UPDATE was the ".
        "last statement: mysqli_info returned '$info'");
}

I’ve just checked, and in InnoDB inside a transaction, it’s good to see that (as with Oracle) write-locks are indeed placed on all matched rows not just updated rows.

And don’t get me started on using DB-specific function calls (i.e. functions named mysql_x) as opposed to using a DB-abstraction layer like DBI in Perl, JDBC in Java, etc. Nor why I’m using PHP or MySQL in the first place.

This entry was posted on Wednesday, October 8th, 2008 at 3:37 pm and is filed under Broken, PHP. You can subscribe to Adrian's blog. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.