3

I've been messing around with the MySQLi object in PHP, and was getting used to preparing statements before executing them.

Thus far I've mostly been doing the following :

if($stmt = $mysqli->prepare($sql) == false){
    return $mysqli->error;
}

Which, when it failed, has always told me that my SQL was wrong basically.

Assume the following :

  • A check has been done to ensure MySQL connectivity
  • The SQL is correct, even if it returns 0 rows.
  • All relevant tables exist.

Under what circumstances could a prepared statement fail? I ask, mainly wondering if there is any point to if($stmt = $mysqli->prepare($sql)), or am I just wasting lines of code?

Gary
  • 1,120
  • 10
  • 14
  • 1
    near perfect duplicate of [this question](http://stackoverflow.com/questions/2552545/mysqli-prepared-statements-error-reporting) – kuroi neko Jan 10 '14 at 16:39
  • Oops, my apologies, I thought I was the only one not getting this...Thanks @kuroineko – Gary Jan 10 '14 at 16:45

2 Answers2

2

There are many ways a valid query can fail:

  • Your privileges to the tables could be revoked.

  • The correct SQL query could be formatted with parameter placeholders in invalid contexts.

  • The query could throw an error depending on server settings, for example division by zero returns NULL but someone could enable the SQL mode globally on the server to make division by zero throw an error. There are other cases too, where server settings can alter the meaning of a valid SQL query.

  • You say that the tables exist, but any table can be dropped or renamed. Also, columns may be altered, so your previously valid SQL query no longer finds the columns it names, or attempts to use them in invalid ways.

  • The connection can terminate unexpectedly.

So you do need to detect and respond to errors both when you prepare and when you execute a query.

Think of an analogy to a simple file-opening function like fopen(). You could have errors if you misspell the filename, or if the file is deleted right before you try to open it, or if someone changes the file privileges so you can't read it with the access mode you specified. This means you need to check for success after every fopen() call.

You may reduce the error-checking code if you configure mysqli to throw exceptions. See http://www.php.net/manual/en/mysqli-driver.report-mode.php

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
1

The first that come into my mind: The connectivity is not guaranteed to exist after your check. I'm sure there exists a plenty of more. But one reason should be enough.

Markus Malkusch
  • 7,738
  • 2
  • 38
  • 67