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.
CrisThompsonUKAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jagadishwor DulalSenior Web DeveloperCommented:
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
0
hieloCommented:
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
0
gr8gonzoConsultantCommented:
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
0
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

gr8gonzoConsultantCommented:
The same answer three times in 38 seconds. Hehe.
0
CrisThompsonUKAuthor Commented:
the thing is...  I don't know what "on mysqli or another extension" means...
0
gr8gonzoConsultantCommented:
If you can share your code where you're performing a query, any of us can tell you the proper function to use.
0
CrisThompsonUKAuthor Commented:
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

0
gr8gonzoConsultantCommented:
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

0
CrisThompsonUKAuthor Commented:
buy I want the system to work as it is upgraded past version 5...
0
gr8gonzoConsultantCommented:
Next, if no rows get updated, then that is not an I/O error. An UPDATE query is still considered successful even if it doesn't match any rows or perform an update.

If you do a mysql_query($sql) or die(...), then what you're telling PHP is: "If mysql_query() returns with a FALSE (meaning there was actually a true error), then abort the entire script immediately and tell me what the error was!"

This is fine for personal / small applications, but it's probably not a good way to deal with a database error, because you end up showing an ugly database error to people who have no idea what it is or what it means or what to do about it. In my applications, I typically make use of exceptions and try/catch blocks to perform a big chunk of processing and cancel it all gracefully if anything goes wrong:

try
{
   $query = "UPDATE some table";
   if(!mysql_query($query))
   {
     throw new Exception("A database update failed!");
   }

   $query = "UPDATE some table";
   if(!mysql_affected_rows())
   {
     $query = "INSERT INTO some table";
     if(!mysql_query($query))
     {
       throw new Exception("A database insert  failed!");
     }
   }
}
catch(Exception $ex)
{
   echo $ex->getMessage();
   file_put_contents("errors.log",time() . " :: " . $ex->getMessage() . " :: " . mysql_error() . "\n",FILE_APPEND);
}
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hieloCommented:
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?
you are not required to use "or die(...)".  Having said that, if you execute an sql statement that doesn't affect any rows, then that is NOT an error.  For example, let's say you $play_game_id equals 1000, but there are no rows/records with GAME_PLAY_ID=5.  In that case your UPDATE statement will NOT affect any rows because there were no matches.

$result = mysql_query($sql); 
if( !$result )
{
   echo 'It appears that the system is experiencing technical difficulties. We apologize for the incovenience.';
   /* and here you send your self (the admin) an email.  You can find the details of the error from mysql_error() */
   $message='';
   $message.= 'Line: ' .  __LINE__ . PHP_EOL;
   $message.= 'File: ' .  __FILE__ . PHP_EOL;
   $message.= 'Error: ' .  mysql_error() . PHP_EOL;
   $message.= '$_POST: ' .  print_r($_POST,true) . PHP_EOL;
   $message.= '$_SESSION: ' .  print_r($_SESSION,true) . PHP_EOL;
   $to='admin@company.com';
   $headers='From: admin@company.com'.PHP_EOL;
   mail($to,'Technical Problems',$message, $headers);
}
else
{ 
   if(mysql_affected_rows() == 0)
   {
       // INSERT query here
   }
}

Open in new window

0
CrisThompsonUKAuthor Commented:
thanks guys...

I'll go through these properly on monday.
0
Ray PaseurCommented:
Couple of other things to consider.  A match on WHERE does not mean the same thing as an UPDATE.  Let me try to explain that and show you why it matters.

Let's say you have three rows that match the WHERE clause: WHERE myColumn = 'Ray'
Let's also say that these rows have a column named myValue and they look like this:

myValue = 1, myColumn = 'Ray'
myValue = 2, myColumn = 'Ray'
myValue = 3, myColumn = 'Ray'


Your script runs an update query like this:

UPDATE myTable SET myValue = 2 WHERE myColumn = 'Ray'

The number of affected rows will be two, not three, even though three rows matched the WHERE clause.  This is because no change was needed in the row with myValue = 2.

The result of running that query will be this:

myValue = 2, myColumn = 'Ray'
myValue = 2, myColumn = 'Ray'
myValue = 2, myColumn = 'Ray'


If you rerun that query, the number of affected rows will be zero.

It's a separate matter, but you will probably want to get off the MySQL extension sooner rather than later.  MySQL is going away in PHP.  This article explains why and shows what you must do to make the transition to another data base extension.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0
Ray PaseurCommented:
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.
0
CrisThompsonUKAuthor Commented:
Thanks Guys.

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

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

mysql banished.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.