Solved

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

Posted on 2013-05-17
16
301 Views
Last Modified: 2013-07-02
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.
0
Comment
Question by:CrisThompsonUK
  • 6
  • 5
  • 2
  • +2
16 Comments
 
LVL 15

Expert Comment

by:Jagadishwor Dulal
ID: 39174988
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
 
LVL 82

Expert Comment

by:hielo
ID: 39174989
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
 
LVL 34

Expert Comment

by:gr8gonzo
ID: 39174992
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
 
LVL 34

Expert Comment

by:gr8gonzo
ID: 39174999
The same answer three times in 38 seconds. Hehe.
0
 

Author Comment

by:CrisThompsonUK
ID: 39175003
the thing is...  I don't know what "on mysqli or another extension" means...
0
 
LVL 34

Expert Comment

by:gr8gonzo
ID: 39175013
If you can share your code where you're performing a query, any of us can tell you the proper function to use.
0
 

Author Comment

by:CrisThompsonUK
ID: 39175140
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
 
LVL 34

Expert Comment

by:gr8gonzo
ID: 39175148
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:CrisThompsonUK
ID: 39175173
buy I want the system to work as it is upgraded past version 5...
0
 
LVL 34

Accepted Solution

by:
gr8gonzo earned 167 total points
ID: 39175225
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
 
LVL 82

Assisted Solution

by:hielo
hielo earned 166 total points
ID: 39175275
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
 

Author Comment

by:CrisThompsonUK
ID: 39175301
thanks guys...

I'll go through these properly on monday.
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 167 total points
ID: 39177129
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39177266
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
 

Author Closing Comment

by:CrisThompsonUK
ID: 39228587
Thanks Guys.

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

I will come back to your solutions later.
0
 

Author Comment

by:CrisThompsonUK
ID: 39292768
Thanks Ray for your above and beyond comments...

mysql banished.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now