How to implement PDO in PHP?

PHP
1

Hi,

I am creating scripts using traditional mysql functions and though to shift to PDO. I searched google and I am reading so many articles on PDO on the internet and all are telling same thing. Like some article guy is copy/pasting articles for every dev. site.

Anyways the question is that all say that for error handling you do: try and catch. Okay but do I do that for every query ??? I have like 10 queries in a single php script and if I do try / catch for all those 10 queries then it will so messed up.

I am doing like this:

I create config.php file and put this in it:

$conn = new PDO('mysql:host=localhost;dbname=myDatabase', $username, $password);
try {
    $conn = new PDO('mysql:host=localhost;dbname=myDatabase', $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}

Now i create index.php

<?php
require_once ("config.php");

$id = 5;
try {
    $conn = new PDO('mysql:host=localhost;dbname=myDatabase', $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $conn->prepare('SELECT * FROM myTable WHERE id = :id');
    $stmt->execute(array('id' => $id));

    while($row = $stmt->fetch()) {
        print_r($row);
    }
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}

Now in the above code only 1 query is being executed. What if i have to run 3 more queries do i put that code 3 more times ??? No wonder people are using old mysql code still. I have checked so many sites and no one is giving real world PDO example. They just tell how to get single record, how to add etc. but no REAL WORLD sample.

Please guide me how to do PDO.

Thanks.

2

Couple of things,

[list=1]
[]You don’t need to constantly redefine your connection ($conn) if you have it in your config.php file. So leave that inside the try/catch of your config and remove the one outside your try/catch. Then remove all $conn assignments in your index.php (along with the $conn->setAttribute)
[]That should leave your index.php queries to start with the $stmt lines. If you need to run multiple queries, do them all in the same try/catch (if you can, more on that latter)[/list]

The first thing you need to really grasp is the concept of Exceptions and what they mean to your application. The thing you need to realize is by catching an exception you are allowing your program to continue executing (potentially, unless you call die() or something similar in the catch).

So you first need to ask yourself, can my program successfully continue to run if this query fails? If your answer is yes, then you should put it in a try/catch and do whatever alternate path you want to take in your catch so the program will continue to run properly. If your answer is no, your try/catch is useless, just let the exception bomb the page on its’ own (the only refutal to this statement is if you wanted to log the exception in a specific way, you’d still want the try/catch, the catch will have logic to log the exception and then you can use throw to cause the exception to bubble up or you can use die to kill the processing immediately).

Either way, I’d start with learning Exceptions before fully diving into PDO, as it will help you out greatly.
http://php.net/manual/en/language.exceptions.php

http://ralphschindler.com/2010/09/15/exception-best-practices-in-php-5-3

I listed a few resources I did a quick search for and after scanning them, felt they may help you understand exceptions.

3

Hi,

Thanks for your response. Now atleast I am starting correctly / going in correct direction.
Regarding the ques: “can my program successfully continue to run if this query fails?”
My answer is no, but i do not want the default php error to show up. Like we used to do die(mysql_error()) i would like some specific message to show up that is specified by me for each query when it fails so I know what has failed and what to check / fix.

How to achieve that ?

Thanks.

4

You could use set_exception_handler to capture any exceptions thrown and output them in whatever way you’d like
http://php.net/manual/en/function.set-exception-handler.php

5

In addition, you may want to look at a logger, like Monolog. The nice thing about Monolog is that you can log to multiple outputs. For example, at work we log normal information to MongoDB, warnings and errors go to MongoDB as well, but fatal errors trigger a Pushover message to be sent to the devs so we can investigate. Works very well, I’m very happy with it!

6

Hi,

Thanks by using that I am able to centralized the errors, but if I have say 3 different queries in a single php page and 1st executed fine and 2nd has error then I will get the generic error. How to throw custom error for that specific query so I quickly know what failed and where to check ? And now my code looks like this:

dbconfig.php file:

<?php
$db_host = 'localhost';
$db_name = 'store';
$db_username = 'root';
$db_password = '';

config.php file:

<?php
session_start();
$PHPSESSID = session_id();
putenv ("TZ=Europe/London");

$root_dir = dirname(__FILE__) . "/";
require_once $root_dir . "dbconfig.php";

try 
{
	$conn = new PDO("mysql:host=localhost;dbname=" . $db_name, $db_username, $db_password);
	$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    
} 
catch(PDOException $db_error) 
{
	die ("WARNING: CONNECTION FAILED.");
}

function exception_handler($exception) {
	echo "Uncaught exception: " , $exception->getMessage(), "\
";
	die("Oops! Something bad happened");
}

set_exception_handler('exception_handler');
?>

index.php file:

<?php
require_once $root_dir . "config.php";

$name = "Category 5";

// To retireve single records
echo "<h4>Fetch single record</h4>\
";

$stmt = $conn->prepare("SELECT * FROM caddtegories WHERE catg_name = :name LIMIT 1");
$stmt->bindParam(':name', $name, PDO::PARAM_STR); $stmt->execute();
$catg = $stmt->fetch(PDO::FETCH_OBJ);

if (!empty($catg))
{
	echo $catg->catg_name . "<br />\
";
}
else
{
	// To add new record using PDO
	echo "<h4>Insert record</h4>\
";
	
	$stmt = $conn->prepare('INSERT INTO categories SET catg_name = :name');
	$stmt->bindParam(':name', $name, PDO::PARAM_STR); $stmt->execute();
}

// To retireve multiple records etc.
echo "<h4>Fetch multiple records</h4>\
";

$stmt = $conn->prepare("SELECT * FROM categories GROUP BY catg_name");
$stmt->execute();
 
while($catgs = $stmt->fetch(PDO::FETCH_OBJ)) 
{
	echo $catgs->catg_name . "<br />\
";
}

echo "PHPSESSID: " . session_id();
?>

1 more quick ques: Do i have to filter user input data before using them in queries or bindParam will take care of all that ?

Thanks.

7

Well, you can always extend the Exception to apply a custom message (http://php.net/manual/en/language.exceptions.extending.php)

class ApplicationException extends Exception
{
  protected $applicationMessage;

  public function __construct($exception, $applicationMessage)
  {
     $this->applicationMessage = $applicationMessage;
     parent::_construct($exception->getMessage(), $exception->getCode(), $exception);
  }

  public function getApplicationMessage()
  {
     return $this->applicationMessage;
  }
}

Then in your catch

catch (PDOException $e)
{
   throw new ApplicationException($e, "My custom message");   
}

In your exception handler

function exception_handler($exception) {
	echo "Uncaught exception: " , $exception->getMessage(), "\
";

        if ($exception is ApplicationException)
            die($exception->getApplicationMessage());
        else
           die("Oops! Something bad happened"); 
}

All of that is untested and done from memory (there could be a better way, I’m just reliving things I know I’ve seen in .NET and applying them to PHP).

As for your second question: bindParam will sanitize the data for you. However, you may want to read the following url so you understand the differences between bindParam and bindValue (read the answer from lonesomeday, which has a good example).
http://stackoverflow.com/questions/1179874/pdo-bindparam-versus-bindvalue

8

Hi,

I am not sure if i get your code. How will i be able to specify error message for each query using your code ?
I am trying to achieve this:

$r1 = mysql_query($q0) or die("Query Failed: Q0");
$r1 = mysql_query($q1) or die("Query Failed: Q1");
$r1 = mysql_query($q2) or die("Query Failed: Q2");

So in this you know that any query which fails will show its respective specified error. I want to achieve the same with the PDO implementation.

Thanks.