Error Checking for Pdo Prepared Statements

Error Checking for PDO Prepared Statements

I preffer setting the error mode to throwing exceptions like this:

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

right after I connect to the database. So every problem will throw an PDOException
So your code would be:

$selectQuery = '
                SELECT 
                    ' . implode($allFields, ', ') . ' 
                FROM 
                    People 
                WHERE 
                    ' . $fieldName . ' = :value
';
try
{ 
    $selectQueryResult = $db->prepare($selectQuery);
    selectQueryResult->bindParam(':value', $fieldValue);
    $selectQueryResult->execute();
}
catch(PDOException $e)
{
    handle_sql_errors($selectQuery, $e->getMessage());
}

where the function would be:

function handle_sql_errors($query, $error_message)
{
    echo '<pre>';
    echo $query;
    echo '</pre>';
    echo $error_message;
    die;
}

In fact I am using a general function that also has something like

$debug = debug_backtrace();
echo 'Found in ' . $debug[0]['file'] . ' on line ' . $debug[0]['line'];

to tell me where was the problem if I am running multiple queries

Why does this PDO statement silently fail?

TL;DR

Always have set PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION in your PDO connection code. It will let the database tell you what the actual problem is, be it with query, server, database or whatever. Also, make sure you can see PHP errors in general.
Always replace every PHP variable in the SQL query with a question mark, and execute the query using prepared statement. It will help to avoid syntax errors of all sorts.

Explanation

Sometimes your PDO code produces an error like Call to a member function execute() or similar. Or even without any error but the query doesn't work all the same. It means that your query failed to execute.

Every time a query fails, MySQL has an error message that explains the reason. Unfortunately, by default such errors are not transferred to PHP, and all you have is a silence or a cryptic error message mentioned above. Hence it is very important to configure PHP and PDO to report you MySQL errors. And once you get the error message, it will be a no-brainer to fix the issue.

In order to get the detailed information about the problem, either put the following line in your code right after connect

$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

(where $dbh is the name of your PDO instance variable) or - better - add this parameter as a connection option. After that all database errors will be translated into PDO exceptions which, if left alone, would act just as regular PHP errors.

After getting the error message, you have to read and comprehend it. It sounds too obvious, but learners often overlook the meaning of the error message. Yet most of time it explains the problem pretty straightforward:

Say, if it says that a particular table doesn't exist, you have to check spelling, typos, letter case. Also you have to make sure that your PHP script connects to a correct database
Or, if it says there is an error in the SQL syntax, then you have to examine your SQL. And the problem spot is right before the query part cited in the error message.

You have to also trust the error message. If it says that number of tokens doesn't match the number of bound variables then it is so. Same goes for absent tables or columns. Given the choice, whether it's your own mistake or the error message is wrong, always stick to the former. Again it sounds condescending, but hundreds of questions on this very site prove this advice extremely useful.

Note that in order to see PDO errors, you have to be able to see PHP errors in general. To do so, you have to configure PHP depends on the site environment:

on a development server it is very handy to have errors right on the screen, for which displaying errors have to be turned on:
```
  error_reporting(E_ALL);
  ini_set('display_errors',1);
```
while on a live site, all errors have to be logged, but never shown to the client. For this, configure PHP this way:
```
  error_reporting(E_ALL);
  ini_set('display_errors', 0);
  ini_set('log_errors', 1);
```

Note that error_reporting should be set to E_ALL all the time.

Also note that despite the common delusion, no try-catch have to be used for the error reporting. PHP will report you PDO errors already, and in a way better form. An uncaught exception is very good for development, yet if you want to show a customized error page, still don't use try catch for this, but just set a custom error handler. In a nutshell, you don't have to treat PDO errors as something special but regard them as any other error in your code.

P.S.

Sometimes there is no error but no results either. Then it means, there is no data to match your criteria. So you have to admit this fact, even if you can swear the data and the criteria are all right. They are not. You have to check them again. I've short answer that would help you to pinpoint the matching issue, Having issue with matching rows in the database using PDO. Just follow this instruction, and the linked tutorial step by step and either have your problem solved or have an answerable question for Stack Overflow.

PDO prepared statement syntax warning

Use backticks instead of single-quotationmarks for tablenames (and columnnames):

$newUserStmt = $DBH->prepare("INSERT INTO `userbasicinfo` (`email`, `passHash`, `birthday`, `phoneNumber`) VALUES (?, ?, ?, ?)");

With single-quotationsmarks your Database-Server interpretate the tablename as a string.

PHP PDO Prepared Statement parameter causing error

Your $limit parameter is being escaped as one parameter, where it should be escaped as two. Your sql will currently look something like "limit '0, 8';" where it should look like "limit 0, 8";

To solve this, you should split your limit parameter into two. Edit the end of your SQL to look like:

LIMIT :offset, :limit

And your parameter list to look like:

$query_params = array (
    ':offset' => ($pagenum - 1) * $page_rows,
    ':limit' => $page_rows
);

As Mr Smith mentioned, you'll also have to add the line:

$db->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );

To ensure the limit parameters are correctly handled as integers rather than strings.

PDO error: $pdo-prepare()-execute() throws err Call to a member function fetch() on boolean

You need a PDOStatement to fetch, execute only returns true or false. You should only execute the PDOStatement. That will give you back a result object you can fetch or an error. For PDO error handling see My PDO Statement doesn't work.

$stmt = $pdo->prepare('select timezone from wo_users where user_id=?');
$stmt->execute(array($wo['user']['user_id']));
while($row = $stmt->fetch()){
     var_dump($row);
     die;
}

As you can see from the manual query works because:

PDO::query() returns a PDOStatement object, or FALSE on failure.

where as execute:

Returns TRUE on success or FALSE on failure.

the prepare is what we need:

If the database server successfully prepares the statement, PDO::prepare() returns a PDOStatement object. If the database server cannot successfully prepare the statement, PDO::prepare() returns FALSE or emits PDOException (depending on error handling).

and fetch (this one is the description, not the return):

Fetches a row from a result set associated with a PDOStatement object

Is there a need to test execute() result in PDO?

It depends on PDO::ATTR_ERRMODE, as explained in the Errors and error handling chapter.

If you configure PDO to throw exceptions (PDO::ERRMODE_EXCEPTION) then no, PDO will automatically throw an exception on error.

In any other case (use the default PDO connection options or explicitly set PDO::ERRMODE_SILENT or PDO::ERRMODE_WARNING) then yes, you need to verify manually the success of each individual operation.

It isn't useful that PDO::ERRMODE_EXCEPTION is not the default but, you know, PHP has a history of hiding helpful error messages for some misguided user-friendliness.

Error while using PDO prepared statements and LIMIT in query

Regarding to post LIMIT keyword on MySQL with prepared statement , the code below could solve my problem.

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);

Thanks Álvaro G. Vicario and Maerlyn

PHP PDO - Using prepared statements causes server error

As @chris85 alluded to, if you change to:

$temp->execute(array("theusername"));

this may cure the problem.

Error Checking for Pdo Prepared Statements