ID: 20290 Updated by: [EMAIL PROTECTED] Reported By: [EMAIL PROTECTED] -Status: Open +Status: Bogus Bug Type: MySQL related Operating System: RedHat 7.3 PHP Version: 4.2.3 New Comment:
Please check possible errors first (mysql_errno), and don't use deprecated functions (mysql_db_query). If the problem still exists, set the status of this bug report back to open. Previous Comments: ------------------------------------------------------------------------ [2002-11-06 12:20:30] [EMAIL PROTECTED] I have a simple section of code that attempts to UPDATE a row in a MySQL table. If mysql_affected_rows() = 0 I assume the row didn't exist so I go ahead and INSERT it. This works about 95% of the time. The other 5% of the time, the row does exist and IS updated, but mysql_affected_rows() returns 0--so I go ahead and INSERT it. I then end up with a duplicate entry. I could define the appropriate columns as "unique" so that the INSERT would fail, but I feel that something else is wrong here and doing this would be a work-around for what might be a real problem. My table is entirely numeric, no strings. It's not a case of updating a row to its same values and so it's not counted as an affected row. The value DOES change. My code is: $SQL = "UPDATE UserStats SET MS=MS+$Var,MC=MC+1 WHERE idUser=$idUser AND Month=$Month"; $updateStats = mysql_db_query ($sDB, $SQL, $nConnection); if(mysql_affected_rows($nConnection) == 0) { $SQL = "INSERT INTO UserStats SET idUser=$idUser, Month=$Month, MS=$Var,MC=1"; $updateStats = mysql_db_query ($sDB, $SQL, $nConnection); } MS, MC, and idUser are all BigInts. Month is MediumInt. The $Var variable is always non-zero. I've dumped the SQL queries to a logfile--they are always valid queries, so it's not an issue of one of my variables not being defined and producing an improper SQL. I've also tried the mysql_affected_rows without the $nConnection paramter. In summary, the above UPDATE *ALWAYS* works in that the actual row in question is always updated correctly in the MySQL database. However, sometimes the mysql_affected_rows() returns 0 instead of 1; so my code continues to INSERT a new row and I end up with a duplicate. MySQL version is 3.23.49. Same UPDATE instruction works fine when executed manually multiple times in MySQL command-line, etc. Always returns the correct number of rows having been updated. I am not sure if this is a PHP problem or a MySQL problem, but I lean towards PHP since MySQL *IS* updating the row as requested and I can't duplicate the problem outside of PHP. I'm also open to it being a code problem on my end, but at this point I don't see how. ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=20290&edit=1