mysqli_affected_rows gotcha

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.