views:

385

answers:

3

I'm trying to get my head around MySQli and I'm confused by the error reporting. I am using the return value of the MySQLi 'prepare' statement to detect errors when executing SQL, like this:

$stmt_test =  $mysqliDatabaseConnection->stmt_init();
if($stmt_test->prepare("INSERT INTO testtable VALUES (23,44,56)"))
{
 $stmt_test->execute();
 $stmt_test->close();
}
else echo("Statement failed: ". $stmt_test->error . "<br>");

But, is the return value of the prepare statement only detecting if there is an error in the preperation of the SQL statement and not detecting execution errors? If so should I therefore change my execute line to flag errors as well like this:

if($stmt_test->execute()) $errorflag=true;

And then just to be safe should I also do the following after the statement has executed:

if($stmt_test->errno) {$errorflag=true;}

...Or was I OK to start with and the return value on the MySQLi prepare' statement captures all errors associated with the complete execution of the query it defines?

Thanks C

+1  A: 

Not sure if this answers your question or not. Sorry if not

To get the error reported from the mysql database about your query you need to use your connection object as the focus.

so:

echo $mysqliDatabaseConnection->error

would echo the error being sent from mysql about your query.

Hope that helps

andy-score
Thanks. So if I request the error for the actual connection object then it will give me the last error for that connection. Since the execution will only succeed if all the previous steps have succeeded then this will tell me if all went well. I suppose the same result could also be generated by just checking the error for the execute command as endorsed below by Col Shrapnel. Am I right in thinking therefore that checking the success/fail flag of the prepare statement serves no real purpose?
Columbo
I think you may already have your answer above, but will still reply out of courtesy.Essentially Prepare can fail, as VolekrK said, but it wont return a mysql error. So you need to find out why prepare failed by getting the mysql connection error which will give you an indication of where the query in your prepare statement failed.I'm not sure about the execute command error.
andy-score
+1  A: 

See what you said.

I am using the return value of the MySQLi 'prepare' statement to detect errors when executing SQL

If you want to check execute errors, you have to check execute results.
So, you are right with if($stmt_test->execute()) $errorflag=true; statement. though I'd make it this way:

$query="INSERT INTO testtable VALUES (23,44,56)";
$stmt_test->prepare($query);
$stmt_test->execute() or trigger_error($stmt_test->error); 
$stmt_test->close(); 
Col. Shrapnel
+1  A: 

I wrote this twice before in the last two days (so for me it's a duplicate even though the questions started a bit different).
Each method of mysqli can fail. You should test each return value. If one fails, think about whether it makes sense to continue with an object that is not in the state you expect it to be. (Potentially not in a "safe" state, but I think that's not an issue here.)
Since only the error message for the last operation is stored per connection/statement you might lose information about what caused the error if you continue after something went wrong. You might want to use that information to let the script decide whether to try again (only a temporary issue), change something or to bail out completely (and report a bug). And it makes debugging a lot easier.

$stmt = $mysqli->prepare("INSERT INTO testtable VALUES (?,?,?)");
// prepare() can fail because of syntax errors, missing privileges, ....
if ( false===$stmt ) {
  // and since all the following operations need a valid/ready statement object
  // it doesn't make sense to go on
  // you might want to use a more sophisticated mechanism than die()
  // but's it's only an example
  die('prepare() failed: ' . htmlspecialchars($mysqli->error));
}

$rc = $stmt->bind_param('iii', $x, $y, $z);
// bind_param() can fail because the number of parameter doesn't match the placeholders in the statement
// or there's a type conflict(?), or ....
if ( false===$rc ) {
  // again execute() is useless if you can't bind the parameters. Bail out somehow.
  die('bind_param() failed: ' . htmlspecialchars($stmt->error));
}

$rc = $stmt->execute();
// execute() can fail for various reasons. And may it be as stupid as someone tripping over the network cable
// 2006 "server gone away" is always an option
if ( false===$rc ) {
  die('execute() failed: ' . htmlspecialchars($stmt->error));
}

$stmt->close();
VolkerK
Thanks. I've just done some testing and can see what you are saying. If I create a quesry that inserts a duplicate primary key value into a table then checking the prepare only will not reveal that the insertion failed. On the other hand if I don't check the prepare then the execute will never happen (I'll get some warnings IF warnings are switched on). So I can see that you're right. Thanks.
Columbo
Oh yes, constraints are a perfect example. If possible I'd give _the question_ another +1 for that alone ;-)
VolkerK