Link to home
Start Free TrialLog in
Avatar of CrisThompsonUK
CrisThompsonUKFlag for United Kingdom of Great Britain and Northern Ireland

asked on

MySQL - how to detect how many rows affected on UPDATE or, error detection.

Doing an UPDATE, what the best way (least system impact) to work out of no rows were affected?

Should I SELECT first, and then UPDATE or INSERT?

Quick answer = max points.
Avatar of Jagadishwor Dulal
Jagadishwor Dulal
Flag of Nepal image

You can use mysql_affected_rows but beware it's depricated in php 5.5
http://php.net/manual/en/function.mysql-affected-rows.php
use XXX_affected_rows() where XXX is mysql OR mysqli (depending on what you are using):

http://www.php.net/manual/en/function.mysql-affected-rows.php

http://www.php.net/manual/en/mysqli.affected-rows.php
After running your update, use mysql_affected_rows() (or the newer versions of the same call, if you're on mysqli or another extension):

http://php.net/manual/en/function.mysql-affected-rows.php
The same answer three times in 38 seconds. Hehe.
Avatar of CrisThompsonUK

ASKER

the thing is...  I don't know what "on mysqli or another extension" means...
If you can share your code where you're performing a query, any of us can tell you the proper function to use.
Thanks Gonzo.

Is this enough?  I would to know if no rows are affected so I can do an insert instead.

I think it's part of a larger question about the proper way to handle i/o errors.  I have always just used "or die(mysql_error())" - but is this the best way - can I not detect the error and do something gracefully?

Thanks.
Cris.

$sql= "UPDATE $GAMES_PLAYED_TABLE SET SCORE_VALUE='$score', SECONDARY_SCORE_VALUE='$secondary_score', TIEBREAKER='$tiebreaker' WHERE GAME_PLAY_ID = $game_play_id";
		
	
$result = mysql_query($sql) or die(mysql_error().'<br/>'.$query); 
		

Open in new window

First, the code:
$result = mysql_query($sql) or die(mysql_error().'<br/>'.$query); 
if(mysql_affected_rows() == 0)
{
  // INSERT query here
}

Open in new window

buy I want the system to work as it is upgraded past version 5...
ASKER CERTIFIED SOLUTION
Avatar of gr8gonzo
gr8gonzo
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks guys...

I'll go through these properly on monday.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Regarding this: "... detect the error and do something gracefully?"

Yes, of course.  Not all SQL run-time errors need to be fatal.   For example, MySQL will throw errno() == 1062 when an attempt is made to put duplicate data into a UNIQUE index.  This would probably not be a reason to die().  In fact, there would not really be any reason to die() so you might want to consider using trigger_error() instead.  Your error handler could make the appropriate programmatic decision about how to proceed.

You might also consider using try/catch to handle exceptions.
Thanks Guys.

sorry I went quiet, concentrating on other areas of the project.

I will come back to your solutions later.
Thanks Ray for your above and beyond comments...

mysql banished.