Page 1 of 1

HELP NEEDED - MySQL UPDATE query with zero affected rows

Posted: Tue Oct 26, 2004 1:02 pm
by RobertGonzalez
I just ran into this today and was wondering if someone can offer some assistance. I have a form (PHP 4+) that when processed runs an UPDATE to one table, then after it successfully completes the update, runs an UPDATE on another table. I have it set up in my database in a way that after the first update is done, it error checks using an IF-ELSE statement:

Code: Select all

<?php
$sql = "UPDATE ...";
if ( !$db->sql_query($sql) || !$db->sql_affectedrows())
{
    //run error reporting
}
else
{
    //run second update query
}
?>
I did this to prevent the second table from being updated if the first table had problems. Now I have a new issue.

If there are no changes to the data used in the first UPDATE query, the script returns an affected row count of 0. When that happens, it runs the error script and does not update the second portion of data. My question is: how do you check your result to see if the first update had zero affected rows because the data did not change AND NOT because of an SQL error?

Any insight on this issue would be most appreciated. Up until today I had no idea that MySQL did not return an affected row count if the data in the UPDATE query did not change.

Posted: Wed Oct 27, 2004 2:53 am
by phpScott
why not simply break apart your if statment like so

Code: Select all

<?php
$sql = "UPDATE ...";
if (!$db->sql_query($sql)) 
{
    //run error reporting
}
else
{
    //run second update query
}
?>
that way it test first to see if there was and sql error and if there isn't then run your update query.
That is of course if you don't need the affected row's some where else in your query.

Posted: Wed Oct 27, 2004 6:21 am
by CoderGoblin
OK don't know MySQL (I use Postgres) but does it have transaction functionality?
This would be useful in a situation like this so if the second update fails, the first is also removed.

Posted: Wed Oct 27, 2004 6:32 am
by phpScott
we are all awaiting the new release so that transactions will be handled gracefully. I can't wait :D

Posted: Wed Oct 27, 2004 12:46 pm
by RobertGonzalez
Thanks for all your help with this. I changed the code and will be testing it this morning.

As far as the transactional stuff, I think MySQL 5 is going to have a lot more functionality in it, like transactions, stored procedures and added SQL functions. Not sure about it though.

Posted: Thu Oct 28, 2004 10:44 am
by Weirdan
phpScott wrote:we are all awaiting the new release so that transactions will be handled gracefully. I can't wait :D
from what I know transactions were available in MySQL starting from v3.23.17 (released more than four years ago). What are you waiting for, huh? ;)

Posted: Thu Oct 28, 2004 10:55 am
by phpScott
your right I never looked to far past the myisam tables which are the default for mysql but your are right some db types theat mysql does support, how about views and stored procedures?

Posted: Thu Oct 28, 2004 11:24 am
by Weirdan
phpScott wrote:how about views and stored procedures?
http://dev.mysql.com/doc/mysql/en/Roadmap.html

Posted: Fri Oct 29, 2004 3:01 am
by phpScott
I waiting for the stabel release as I don't want to get bloody along the way.
Heck I have only looked at php5 but that is only because of contraints at work.