How many rows did a query MATCH (not affect)?

[jhsachs]

I've got a PHP function that many scripts use to perform MySQL queries. One of its parameters determines whether a query that matches no rows is considered an error. If the parameter is true and a query matches no rows, the function creates a log entry and kills the script.

 

I've discovered a bug which appears to be based on an unexpected quirk in the mysql_affected_rows function: it appears to return the number of rows in which at least one column value was changed, rather than the number of rows that matched the WHERE condition.

 

For example, if I execute a query like

 

UPDATE xyz SET abc=1 WHERE recno=5

 

and a row with recno=5 exists but its value of abc is already 1, mysql_affected_rows returns 0, and my function thinks an error occurred.

 

This is very inconvenient because I'm really trying to test for consistency errors: the absence of a row that should exist. I really couldn't care less if the value in the row is actually being changed.

 

As far as I can see, there are only two airtight ways to fix this problem.

 

One would be to pass the function two queries: one to test for the existence of the record, and another to perform the update. That's wrong in multiple ways: it's inefficient, it would require source code changes everywhere the function is called, and it would create a new source of bugs (writing the existence query incorrectly).

 

The other way would be to parse the UPDATE query and construct an existence query from its table name and WHERE clause. I like a challenge as well as the next fellow, but I'm not prepared to add a SQL parser to my application.

 

What I really want is a mysql_rows_matched function, but I can't find such a thing. Are there any other possibilities here?

[Pikachu2000]

That isn't a bug at all. The query affected zero rows if it didn't actually change a value. The MySQL documentation says if an UPDATE query tries to insert duplicate values, the record is ignored. The best suggestion I can make is if the number of rows affected === 0, then run a SELECT COUNT() query to see if the record exists or not.

[jhsachs]

I called it a "quirk," because I wasn't sure if it was a bug in MySQL or not. I alluded to a "bug" in my code, caused by the quirk.

 

You're right, the documentation does describe this as correct behavior. I missed it because it's in the description of the return value rather than the description of the function.

 

It appears that mysql_info will tell me the number of rows matched, although I have to pick the information out of a string that is written for human consumption. Not the most elegant solution possible, but far, far better than what I thought I'd have to deal with. I'll consider this solved.

[Pikachu2000]

Now I see by the phrasing, it isn't what it first appeared . . . Glad you found a solution though.