We help IT Professionals succeed at work.

Best method for testing a query result

tjyoung
tjyoung asked
on
Hi,
I'm grey on how to check if a query was successful or not. Below is what I'm doing but I am guessing there is a better way. Not even sure if my way is correct?

 
mysql_query("UPDATE subscribers SET SubscribeStatus = 'Pending' WHERE FromCaller = '$FromCaller'");
$affectedrows = mysql_affected_rows();
if ($affectedrows == 0){
do something;
}

Open in new window

Comment
Watch Question

Marco GasiFreelancer
Top Expert 2010

Commented:
Your method is good. As explained here: http://php.net/manual/en/function.mysql-query.php, you can do this way also:

if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);
}

Cheers
Most Valuable Expert 2011
Top Expert 2016
Commented:
Read the "Return Values" part here very carefully.  There is a gotcha (works as designed, just not as expected).
http://php.net/manual/en/function.mysql-affected-rows.php

Never, ever write a query call like this:
mysql_query("UPDATE subscribers SET SubscribeStatus = 'Pending' WHERE FromCaller = '$FromCaller'");

Instead, construct the query as a separate variable, so you can print it out.
$sql = "UPDATE subscribers SET SubscribeStatus = 'Pending' WHERE FromCaller = '$FromCaller' ";
$res = mysql_query($sql);

Do you expect multiple rows with identical values for FromCaller?  If not, consider adding LIMIT 1 to the query.  It will help you avoid a table scan on every update.

The code snippet contains the general method I use to test for query success.  Note that success does not mean anything was found - it just means that MySQL processed the query without errors.  In this example, I want to skip duplicate key errors, and die on all other errors (like MySQL connection failed, etc.)
$isql     = "INSERT INTO myTable ( rand_key ) VALUES ( '$rand_key' )";
if (!$i   = mysql_query($isql)) // IF QUERY ERROR
{
    $err   = mysql_errno();
    if ($err == 1062) // DUPLICATE UNIQUE FIELD ON rand_key
    {
        $rand_key = '';
    }
    else
    {
        $msg 
        = 'FAIL: ' 
        . $isql 
        . '<br/>' 
        . $err
        . ' ' 
        . mysql_error()
        ;
        die($msg);
    }
}

Open in new window

Commented:
If you want to know if any records were affected, your way is fine.

If you want to know if the query itself failed and why (testing), then use

echo mysql_error();

If you just want to stop when the query fails, use

mysql_query("UPDATE subscribers SET SubscribeStatus = 'Pending' WHERE FromCaller = '$FromCaller'") or die("query error");