Error handling with PDO

MySQL database
#1

As I continue my rocky journey into understanding PDO_SQL queries and how they differ from MySQL queries, I have hit another roadblock.

I am trying to display an error message, if an error is encountered when adding a record. I had working code with MySQL, but now I am getting a different result with PDO. I know that PDO appears to handle errors differently through PDOException??? (I really don’t know what I’m talking about though and reading that dang PHP manual is NOT helpful – it might as well be written in an ancient language – it is not newbie friendly).

Here is the code I have from MySQL which I am trying to use as well for PDO:

$sql_add = “INSERT INTO tableCategories (cats_name, cats_rank) values (’” . $add_newName . “’, '” . $add_newRank . “’);”;
if ($pdo->query($sql_add) !== TRUE) {
echo "Error adding record: " . $pdo->errorInfo();
}

(I used to have $pdo->error; in the above line, but I thought changing it to $pdo->errorInfo(); would be correct based on what I tried to read in the PHP manual.)

While the query and insert is working fine, it is triggering my condition and outputting the following:
Error adding record: Array

I’m guessing I need to test and output the error message differently now? I can’t figure out how to do this though. Can someone please help.

#2

You never want to output internal errors to the user.

Here is a PDO tutorial to get you going.
https://phpdelusions.net/pdo

You can also download my PDO Bumpstart Database that will show you how things work.
https://github.com/benanamen/pdo_bumpstart_ver1.6

1 Like
#3

It works differently.

  1. You need to specify that you want to capture error info.
  2. You need a try catch to actually get the message.

And, for a production system, you want to log the error not display it to the user.

#4

Right now, I am creating the admin interface that I use to add data to the tables, so I do want to know the errors, but I do appreciate you mentioning that.

I’m still stuck at my roadblock though.

It must be great for regular people to read a manual and understand what needs to be done. My brain doesn’t work that way – the manual is gibberish to me until I find a specific area that applies to my issue.

When manuals don’t address my specific issue then I am stuck. I my case, I need to display any error codes that result when I try to insert/alter/delete data. This was so freaking simple to do in mysql and there were a lot of resources out there to help a person out. PDO and the available resources have not been very helpful to me. (Worse yet, all the resources provide very different instructions on how to do coding and queries. I’m trying to stick to just 1 source (which is the phpdelusions proper PDO manual) however it provides only very brief references and brief/limited examples to fairly complex issues. Very frustrating to say the least.

If someone could just show me a code example or link me to an example which does what I am trying to do, it would be greatly appreciated.

#5

Use exceptions to handle all the database statement errors (connection, query, prepare, and execute) and in most cases let php catch the exception where it will use its error_reporting, display_errors, and log_errors settings to control what happens with the actual error information. The exception to this rule is when inserting or updating user submitted data and a duplicate error occurs. This is a recoverable error that the user can correct. In this case, you would have a ‘local’ try\catch block around the execution of the query, detect if the error number was for a duplicate key error (it’s 1062) and setup a user error message alerting them to the problem. For all other error numbers, your catch logic would just re-throw the exception and let php handle it (which can include a custom exception handler of your own.)

When learning, developing, and debugging code/queries, you should display all php errors, which now include the database statement errors, and when on a live/public server, you would log all errors.

With exceptions your code only ‘sees’ error free execution (an exception transfers execution to the exception handling), so you can remove all the conditional logic you may have now.

The PDO connection automatically uses exceptions for connection errors. To enable exceptions for all the other PDO database statements, set the error mode to exceptions when you make the database connection. You do this either as an optional parameter in the connection statement or as a separate statement after making the connection.

Next, you should NOT put external/unknown data directly into the sql query statement. A BIG point of updating old code is to protect against sql special characters in data from breaking the sql query syntax, which is how sql injection is accomplished. You do this by using a prepared query, with place-holders in the sql statement for each data value, then supply the data when the query gets executed. In addition to protecting against sql injection, this actually simplifies the sql query statement since all the extra quotes, dot, and php variables get replaced with a simple ? place-holder where each value is being put now.

Short answer - if you use the php PDO extension, use prepared queries when supplying external/unknown data to the sql statement, and use exceptions to handle database statement errors, you will end up with the least amount of php code and the simplest sql query syntax.

#6

Read the PDF that Benanamen posted. It will show you how to do all of the various PDO calls to your database. It has examples of all of them and also error checking…

Look down the page a ways and find the section on " Error handling. Exceptions" (Click on #11 at top)
It tells you how to turn on errors just after you create the PDO connection.
Then, it shows you the try-catch example. But, in my humble opinion, you should first learn how to use
prepared statements as they are more secure and safer. To learn about these, click on #4…

Once you get your code rewritten to use these, post it again for us and we can help you fix it up.

#7

I’m trying to understand that manual, but its not really helping… especially when it starts talking about prepared statements… I’m pretty lost there.

I do understand the importance of using prepared statements, I just can’t figure out how to use them.

Take my code here:

$sql_add = $pdo->prepare("INSERT INTO tableCategories (cats_name, cats_rank) values ('" . $add_newName . "', '" . $add_newRank . "');");

The closest conversion I can get by following the manual is this:

$sql_add = $pdo->prepare('INSERT INTO tableCategories (cats_name, cats_rank) values (:catsName , :catsRank)');
$sql_add->execute(['catsName' => $add_newName, 'catsRank' => $add_newRank]);

However this just makes my page die.

#8

That is exactly why you should get my Bumpstart Database. It will show you the basics you need to know.

#9

Well, first you create the PDO connection. Here is an example from that PDO page:
$host = ‘127.0.0.1’;
$db = ‘test’;
$user = ‘root’;
$pass = ‘’;
$charset = ‘utf8mb4’;

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
     throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

You would need to enter the dsn, username, password to fit your database.
Then, you can use the $pdo object variable for the commands you converted.

( If you download Benanamen’s bumpstart, it shows a lot of the general uses of how to get started with PDO, hence the name…)

#10

Hi Ernie,

Thanks for all that, but I do have all the connection code in my page… and that part works fine.

I looked at another page on phpdelusions (https://phpdelusions.net/pdo_examples/insert), which gives some instructions that are a little different from the guide they provide, but I was able to mash together a working query:

$sql_add = 'INSERT INTO tableCategories (cats_name, cats_rank) VALUES (:catsName, :catsRank)';
$pdo->prepare($sql_add)->execute(['catsName'=>$add_newName, 'catsRank'=>$add_newRank]);

Now that I have that working though, I still can’t figure out how to incorporate error checking on the INSERT.

#11

I was looking at examples from another page and they used the following:

if ($pdo->query($sql_add) === false) {
        echo "Error adding record: " . $pdo->errorInfo();
}

It does seem to work, but I don’t see this method referenced on phpdelusions which makes me think that this is not the best way?

#12

I am using Cloud9 to develop my website. I can’t figure out how to set up benanamen’s stuff on it – the .sql I don’t think I have any ability to add onto it.

I guess I may have to add it to my shared web hosting service site, but being a newbie to all of this, I’m very reluctant.

#13

See my reply above in this thread on how to use exceptions for errors.

#14

Is your connection set up as I posted above? Does it include the error-mode set up for exceptions?
If so, you can use code like in the page from phpdelusions like below. You need to add this line at the top
of your PHP page so it shows errors.

ini_set('display_errors', 1);

Some errors in PHP servers don’t display on the page. Some are placed into an error log in the folder that contains the page you are running. Therefore, sometimes you have to look at that folder for the errors.
Here is a recap from that page for using try-catch versions of queries…

try {
    $pdo->prepare("INSERT INTO tableCategories (cats_name, cats_rank) VALUES (?,?)")->execute($add_newName, $add_newRank);
} catch (PDOException $e) {
    $existingkey = "Integrity constraint violation: 1062 Duplicate entry";
    if (strpos($e->getMessage(), $existingkey) !== FALSE) {

        // Take some action if there is a key constraint violation, i.e. duplicate name
    } else {
        throw $e;
    }
}

This example throws an error if you insert data that uses the same primary keys. If your database has a primary key created by using the name and rank combined, you could possibly create errors if you attempt to add the same combo again. A good process would be to check for the cat’s name and rank already exists first and if it does, then do not add it in again, but, throw an error for that to the user.

Nope this helps…

#15

I’m sorry phdr, I don’t see any reference on how to use exceptions for errors. I only see the reference to using prepared statements.

I can’t believe how hard it is, despite all the resources on the internet, that there is not one example of how to output an error with PDO when running an INSERT/UPDATE/DELETE query.

This reference does not help in any way:
“https://phpdelusions.net/pdo#errors”

I’ll keep looking though… I’ve already spent 8 hours trying to do what any of you can do in 30 seconds… very frustrating, but I am accepting it as a challenge.

#16

You do not get errors on insert except “key” errors. I showed you how to trap them.
PDO is designed so you can set up prepared statements and just bind them when needed.
When done that way, you do not need to repeat the preparing of the query, just bind new data to them.
Inserts just place new data into your database and it is only the primary-key’s that give you errors.
Do you have a primary key set up in your table? Is it just an “id” field? If so, you don’t need error checking for it at all as each ID would be unique.

Anyways, I gave you a starting script to test for inserts. Try it. It did include displays of exception errors.

#17

This is NOT hard. As I stated, if you enable exceptions for PDO errors and let php catch the exception, php will AUTOMATICALLY display or log the actual error information for you, based on its error_reporting, display_errors, and log_errors settings. You DON’T need any conditional logic in your code and should in fact remove any you have now. The only time you need to add logic in your code is if your code needs to deal with a specific error, such as the duplicate key error that I mentioned, in which case you would have a ‘local’ try/catch block around the execution of the query.

For the query you have posted in this thread, the following is all the logic you need, anything else is unnecessary or is doing this the hardest way possible -

$sql = "INSERT INTO tableCategories (cats_name, cats_rank) values (?,?)";
$stmt = $pdo->prepare($sql);

try { // a 'local' try/catch to handle a specific error type
	$stmt->execute([$add_newName,$add_newRank]);
} catch (PDOException $e) {
	if($e->getCode() == 1062){ // duplicate key error number
		// $errors is an array defined in your code to hold use error messages
		$errors[] = "set up an error message that tells the user exactly what was wrong with the data";
	} else {
		throw $e; // re-throw the pdoexception if not handled by this logic
	}
}

If your code doesn’t need to handle a duplicate error (either duplicate values are allowed or you have used the IGNORE keyword in the query so that they are simply ignored without producing an error), you would just have the three statements to build the query, prepare it, then execute it.

#18

Going back to your first post

While the query and insert is working fine, it is triggering my condition and outputting the following:
Error adding record: Array

When echoing an array php will simply echo “Array”. In order to see what this array contains you either need to use a function that supports arrays (ie var_dump, print_r) or you need to convert it to a string (ie json_decode).

You also must set PDO to exception mode, and you must set your local development environment up so it shows all errors.

A lot of your frustration seem to stem from fumbling blind just trying things. This will hopefully get better soon, but be more aware that if you do not get any errors or anything (ie the page just dies) then your environment isn’t configured correctly for development.

#19

Thank you phdr and Ernie for giving me code examples to learn from. I need to take a break and then figure out how to incorporate it all. Seems pretty straight forward. Once I figured out how to show errors, then I wanted to figure out how to deal with specific errors such as trying to insert a duplicate value into a unique column – but Ernie you have now also shown me the way on that, so that should go much easier.

I appreciate your time and patience in dealing with my inquiries. I am determined to figure this all out. I’ve been developing a fairly simple web page, but after 2 years of chipping away, I’m hardly at the finish line. I’ve been coding in very depreciated html for decades, and recently had to learn the basics of CSS to get up to speed with new formats. I’ve taken several night classes to learn CSS, MySQL and PHP (2 years ago), but when I start trying to apply any of that, I’m learning that everything I have learned is already old/depreciated (or unsafe). I just want to get a fricken working database up and running – albeit securely. 2 years and I’m still a long ways away it appears – hence my frustration. There was a time I could slap code together quicker than most and have a running project together in half the time it would take anyone else. Well, that was 30+ years ago and this old coder is just getting dumber and older by the minute it seems. Thank you again for your time and patience.

#20

@ErnieAlex, you dont need to jump through string parsing hoops to get the duplicate error. The error is held in $e->getCode() and the value is 23000.

@phdr showed the better example except (oops!) he has 1062 which is not a valid duplicate error code for $e->getCode(), it should be 23000.

OP, a duplicate check is about one of the only places you will want to use try/catch. (Another is the DB connection).

With PDO in exception mode the errors will bubble up the stack. If you want to do something custom with those errors you can use set_exemption_handler.

The PDO tutorial page I linked you to shows exceptions set in the $options

$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,];

OP, I highly suggest you install a local dev environment instead of working off your hosting servers. Install Laragon and work off that. Running my Bumpstart app is as simple as it can get. 1. Import the SQL to Mysql. Set the username and password in the database file if it is other than root with no password.

Sponsor our Newsletter | Privacy Policy | Terms of Service