← Getting started with CouchDB and NodeJS on Ubuntu 10.10

Submit a JQuery Mobile form via AJAX with PHP →

Posted on March 28, 2011 by Chad Lung

My recent PHP PDO article introduced you to using PHP PDO. One thing I didn’t cover was how to actually get some error information back from PDO if perhaps you had buggy SQL or something.

Let assume I have a simple database called MyDB along with a table called MyTable.

Here is a MySQL script for creating the table:

CREATE TABLE `MyDB`.`MyTable` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`value1` VARCHAR( 20 ) NOT NULL
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;

Perhaps there is a row in there I want to update so I craft my PHP like so:

<?php
    error_reporting(E_ALL);
    ini_set("display_errors", 1);

	try
	{
		$db = new PDO("mysql:host=localhost;dbname=MyDB", 'user', 'password');

		$stmt = $db->prepare("UPDATE MyTable SET value1 = :value1 WHERE id = :id)");
		$id = 100;
		$value1 = 'Hello World';
		$stmt->bindParam(':value1', $value1);
		$stmt->bindParam(':id', $id);
		echo($stmt->execute());
	}
    catch(Exception $e)
    {
        echo($e->getMessage());
    }
?>

If you have a sharp eye you will have noticed on the end of my SQL statement I have a bracket which will make the SQL fail. Unfortunately though even with the debugging turned on and a try/catch I won’t know the update failed. I even checked for any return value from the execute function.

So, how do I figure out what happened? Well, you can use PDOStatement::errorInfo(). Let me rewrite it now so we get back a message on what went wrong. I’ll add the following line:

print_r($stmt->errorInfo());

Here is the full listing:

<?php
    error_reporting(E_ALL);
    ini_set("display_errors", 1);

	try
	{
		$db = new PDO("mysql:host=localhost;dbname=MyDB", 'user', 'password');

		$stmt = $db->prepare("UPDATE MyTable SET value1 = :value1 WHERE id = :id)");
		$id = 100;
		$value1 = 'Hello World';
		$stmt->bindParam(':value1', $value1);
		$stmt->bindParam(':id', $id);
		echo($stmt->execute());
		print_r($stmt->errorInfo());
	}
    catch(Exception $e)
    {
        echo($e->getMessage());
    }
?>

The output is:

Array ( [0] => 42000 [1] => 1064 [2] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1 )

Ok, so mixed into that we finally find out what went wrong. If you want just the error message you can just do this:

$arr = $stmt->errorInfo();
echo($arr[2]);

Result:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

There are a few other functions you can call as well to get error codes or error information:

• PDO::errorInfo
• PDO::errorCode
• PDOStatement->errorCode
• PDOStatement->errorInfo

This entry was posted in PHP. Bookmark the permalink.

← Getting started with CouchDB and NodeJS on Ubuntu 10.10

Submit a JQuery Mobile form via AJAX with PHP →