Although PHP.net can be a useful documentation site to know what the functions are supposed to do, not always are there the comments that tell you how to use them. And I’m talking about proper implementation: one of the most difficult parts of programming, in my opinion. There are always better ways to implement something, if not over time when new methods come along.
With PDO, you can use mysql(for, example) in an object oriented manor. This gives better structure and more flexibility and security to your queries. However, it may be complicated at first to understand how it should be used. Sure, you can research the functions and what they do, but implementation isn’t always easy without some research on stack overflow or otherwise. But as a rule of thumb, we should be using parameterized queries where possible.
There are different scenarios of which to use transactions, error handling, and if-blocks.
In the following example, we can use an if-block to see if the statement has produced a successful result:
$stmt = $db->prepare("select * from users where id=:id");
$stmt->bindValue(":id",$the_user_id,PDO::PARAM_INT);
if ($stmt->execute())
{
// success
}
This does its job and with success we can begin to pull results from the database. However, there could be errors within the prepare statement all the way to the execute statement that are not handled and will be displayed as-is.
Here is an example of catching a PDO error. This time I used question mark placeholders instead of named placeholders like last time:
try
{
$stmt = $db->prepare("select * from users where id=?");
$stmt->bindValue(1,$the_user_id,PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
// Do something with result set
}
catch (PDOException $e)
{
var_dump($e);
}
Please note that selecting * and dumping the exception should probably only be used when trying this out. Now if we have any errors, such as a null user id number on a non-nullable column or an error with the query, it can be caught and processed with a specific error method. I like to have a method where I can choose to log the error to the database or simply report the error back with a consistent structure. Very useful in production where you would rather not have the users getting raw php or mysql errors sent back to them. They don’t know what to do with that information!
Finally, sometimes we want to do a few queries at once but do not want any errors happening halfway through the process, resulting in corrupted data. Transactions help keep the data consistent, and should be used where all of the queries in the transaction must all succeed or else all fail. Also note that error catching can be tricky, as putting the try-catch block around all of the statements can catch on any of the queries. Lets see how this works:
$db->beginTransaction();
try
{
$stmt = $db->prepare("UPDATE users SET value=1");
$stmt->execute();
$stmt = $db->prepare("INSERT INTO users (value,value2) VALUES(0,0)");
$stmt->execute();
$db->commit();
}
catch (PDOException $e)
{
$db->rollback();
var_dump($e);
}
Notice that each query inside of the try block could trigger the exception, and if it is triggered, the transaction will be rolled back instead of reaching the commit. Again, this is a crude use-case scenario but the implementation is the focus. With PDO, we can create the transaction and catch any errors and rollback all at once.
Now it is likely that a production project will be using some sort of framework to develop the application, which makes sense. Instead of the extra execute() and bindValue() or bindParam() functions over and over again, we can pipeline objects and values to functions within a framework to handle the process for us, and then pipe any errors back. It is still helpful to know these underlining procedures that run the features of popular frameworks.
As always, if there is anything to note, please leave a comment with any improvements on the code or procedures written here. This was written with regards to PHP 5.6, and PHP 7 Alpha has now been released.
alertstudios.wordpress.com 