mysql_affected_rows() returns -1

Question

When executing an SQL statement, such as INSERT INTO table ... ON DUPLICATE KEY UPDATE ... I rely on mysql_affected_rows() to determine if an insert or an update was performed. as the mysql site on http://dev.mysql.com/doc/refman/5.1/en/mysql-affected-rows.html , it states:

For INSERT ... ON DUPLICATE KEY UPDATE statements, the affected-rows value is 1 if the row is inserted as a new row and 2 if an existing row is updated.

All was working Ok until we upgraded to MySQL 5.1.16, when many queries now had mysql_affected_rows() returning MINUS ONE (-1)

The suggested solution on the MySQL site on http://dev.mysql.com/doc/refman/5.1/en/mysql-store-result.html is to call the function mysql_store_result() after each execution of the function mysql_query() on a select query.

However, PHP does not seem to have such a function defined.

How do I get mysql_affected_rows() to work correctly using PHP and MySQL 5.1.16?

Answer 1

In general, if mysql_affected_rows() returns -1, then it means the query failed (see the manual page for more info). You can check what the error was by calling mysql_error(), but you should have picked up the error already when you called mysql_query() before you get to the point of finding out how many rows were affected.

Since you're asking about the store_result function, let me also answer that part of your question:

PHP's MySQL module may not have a store_result function, but the newer MySQLi module does. See http://php.net/manual/en/mysqli.store-result.php

It is generally recommended to switch from using the mysql_xxx functions to the equivalent mysqli_xxx functions anyway, so this would be a perfect opportunity for you to do so.

You don't need to upgrade your PHP version for this, as the MySQLi module was introduced with PHP 5.0. However I would suggest considering an upgrade nonetheless -- if you're using PHP 5.1, you are using a version which has been out of support for over five years already.

Answer 2

mysql_affected_rows returns -1 if the last query failed. I would check your queries and/or database connection. See this link.