HELP NEEDED - MySQL UPDATE query with zero affected rows

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

HELP NEEDED - MySQL UPDATE query with zero affected rows

Post 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.
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

Post 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.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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.
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

Post by phpScott »

we are all awaiting the new release so that transactions will be handled gracefully. I can't wait :D
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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? ;)
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

Post 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?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

phpScott wrote:how about views and stored procedures?
http://dev.mysql.com/doc/mysql/en/Roadmap.html
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

Post 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.
Post Reply