My PDO Statement doesn't work

Question

This is my PHP sql statement and it's returning false while var dumping

$password_md5 = md5($_GET['password']);
$sql = $dbh->prepare('INSERT INTO users(full_name, e_mail, username, password, password_plain) VALUES (:fullname, :email, :username, :password, :password_plain)');
$result = $sql->execute(array(
                    ':fullname' => $_GET['fullname'], 
                    ':email' => $_GET['email'], 
                    ':username' => $_GET['username'],
                    ':password' => $password_md5,
                    ':password_plain' => $_GET['password']));

Answer 1

If PDO statement returns FALSE, it means that query failed. You have to set PDO in the proper error reporting mode to be aware of the error.

Put this line in your code right after connect

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

After getting the error message, you have to read and comprehend it. It sounds too obvious, but learners often overlook the extreme helpfulness of the error message. Yet most of time it explains the problem pretty straightforward. Say, if it says that particular table doesn't exist, you have to check spelling, typos, letter case, credentials and such. Or, if it says there is an error in SQL syntax, then you have to examine your SQL. And the problem spot is right before the query part cited in the error messaage.

You have to also trust the error message. If it says that number of tokens doesn't match 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 advise 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 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.