6

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?

yirmi
  • 61
  • 1
  • 2
  • Are you using prepared statements? By the way, PHP have store_result function - http://php.net/manual/en/mysqli.store-result.php, but store_result is called after SELECTs, not after INSERTs. *-1 indicates that the query returned an error or that, for a SELECT query, mysql_affected_rows() was called prior to calling mysql_store_result()* – Maxim Krizhanovsky Jul 17 '11 at 09:00
  • I also have this problem... – Prof. Falken Mar 06 '13 at 13:51

2 Answers2

4

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.

HoLyVieR
  • 10,985
  • 5
  • 42
  • 67
Spudley
  • 166,037
  • 39
  • 233
  • 307
2

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

rockerest
  • 10,412
  • 3
  • 37
  • 67