Php Pdo Exception Handling - Error mode in Php Pdo

PDO has 3 types error handling strategies,in php application development.

• PDO::ERRMODE_SILENT
PDO will simply set the error code by default  to inspect using the PDO::errorCode() and PDO::errorInfo() on both  database objects and statements; if the error resulted from a call on a statement object, you should use the PDOStatement::errorCode() or PDOStatement::errorInfo( method on that object. If the error resulted from a call on the database object, we should use those methods on the database object.

• PDO::ERRMODE_WARNING
PDO will show a traditional WARNING message if we use ERRMODE_WARNING . This setting is useful while debugging/testing the code, if we want to see what problems occurred without disturbing the application.This will show errorcode also in warning.

• PDO::ERRMODE_EXCEPTION
PDO will throw a PDO Exception and set its properties to reflect the error code and error information. This setting is while debugging the code, as it will effectively point to the error very quickly in the code (Transactions are automatically roll back if the exception occurs).  

Exception mode is also usefult o structure error handling clearly than with normal PHP warnings and errors.

PDO Hanling Exception Example

<?php
    $host = 'mysql:dbname=testdb;host=localhost';
    $username = 'root';
    $password = '';
    //try catch block for handling pdo exception
    try {
        $pdoObj = new PDO($host, $username, $password);
        $pdoObj->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch (PDOException $ex) {
        echo 'Connection failed: ' . $ex->getMessage();
    }
  
    //The example shown above is handling the exception
    //if  the connection using pdo is failed then it will
    //echo connection faild adn the errormessage which is  generated by PDOException class
?>

Php PDO Transactions - BeginTransaction,RollBack,Commit

In transaction management query  is not actually applied to database untill we does not commit the transaction.
And if any error occurs then we can rollback the transaction to the previous state.

Understanding Basic Transaction Flow 

<?php
// Begin transaction
If(success)
{
   If(success)
   {      // Commit the transaction   }
   else
   {      // Roll back the transaction   }
}
else
{
   // Rollback the transaction
}
?>

Using Php PDO we can manage transaction easily.
Here is some Explanation of How Php PDO transactions works.

PDO::beginTransaction– commit the transaction

PDO::beginTransaction() used to begin the transaction. This function will return true on success and false on error. By calling this function autocommit mode will gets turned off.

<?php
// Begin a transaction 
$obj->beginTransaction();
?> 

PDO::commit – commit the transaction

PDO::commit() is used to commit any changed made to DB via PDO object. This will return true on success and false on error.The return type is boolean. Basically this will commit a transaction and autocommit the database connection until the next beginTransaction call.

<?php
    // Begin a transaction
    $obj->beginTransaction();
    // Change the database schema
    $obj->exec("DROP TABLE books");
    // Commit the changes 
    $obj->commit();
    // Database connection is now back in autocommit mode 
?> 

PDO::rollBack – Roll back a Transaction

PDO::rollback() will rollback all changes made to database after calling beginTransaction statement and return the connection to autocommit mode.
The return type is boolean.This also return true on success and false on error.

<?php
// Begin a transaction, turning off autocommit 
$obj->beginTransaction();
// Change the database schema and some data 
$obj->exec("UPDATE books SET name = 'PhpBooks'");
// Recognize mistake and roll back changes 
$obj->rollBack();
// Database connection is now back in autocommit mode 
?>

Php PDO: Prepared Statement

Prepare statement is the method of Php PDO.
The prepare() statement is used to build the query while we are using Php PDO.

Once the prepare statement is declared with query then The values in  that query is passed  as array format using execute method of PDO. The example below shows the use of prepare statement in Php PDO.

Example :

<?php
    // database connection
    $connection = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);

    $title = 'echonomic%';
    $author = 'Hiren%';
    // query
    $sql = "SELECT * FROM books WHERE title like ? AND author like ? ";
    $query = $connection->prepare($sql);


    //Now passsing the variables to query and execute it
    $query->execute(array($title,$author));    
    query->setFetchMode(PDO::FETCH_BOTH);

    // fetch
    while ($result = query->fetch()) {
       print_r($result);
     }
?>

In the above example, query depends on a variabel (?).
This script will output the books which has title starts with echonomics and author starts with Hiren.

Click to view More Examples using PDO prepare() statement.

Note : Php Extension for mysql and SQLite does not support this kind of functionality.

Php PDO Fetch methods - FETCH_ASSOC,FETCH_NUM,FETCH_BOTH,FETCH_OBJ

Php PDO has fetch methods which are explain as below We generally use mysql database methods for php to retrieve data from database.Like mysql_fetch_assoc ,mysql_fetch_row ,mysql_fetch_array ,mysql_fetch_obj.

 In Php PDO  we have to use different methods to fetch data from database.

Lets consider the connection and we are fetching books from books table

<?php
    // database connection
    $conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);
    $sql = "SELECT title FROM books ORDER BY title";
    $query = $conn->query($sql);

 ?> 

PDO fetch method FETCH_ASSOC

Example : 

<?php
    // Fetch using associative array.This method is similar to mysql_fetch_assoc
    $query->setFetchMode(PDO::FETCH_ASSOC);
    while($result = $query->fetch()){
    print_r($result);
}
?>

Result:

Array ( [title] => Book 1)
Array ( [title] => Book 2)

PDO fetch method FETCH_NUM

Example :

 <?php
    // Fetch as numeric array.
    $query->setFetchMode(PDO::FETCH_NUM);
    while($result = $query->fetch()){
    print_r($result);
}
?>

Result:

Array ( [0] => Book 1 )
Array ( [0] => Book 2 )

PDO fetch method FETCH_BOTH

Example : 

<?php
    // Fetch as associative array and numeric array both.This method 
       is similar to mysql_fetch_array 
    $query->setFetchMode(PDO::FETCH_BOTH);
    while($result = $query->fetch()){
    print_r($result);
}
?>

Result:

Array ( [title] => Book 1 [0] => Book 1 )
Array ( [title] => Book 2 [0] => Book 2 )

PDO fetch method FETCH_OBJ

Example :

<?php
    // Fetch as object.This method is similar to mysql_fetch_obj 
    $query->setFetchMode(PDO::FETCH_OBJ);
    while($result = $query->fetch()){
    print_r($result);
}
?>

Result :

stdClass Object
(
[title] =>Book 1
)
stdClass Object
(
[title] =>Book 2
)

Connect to Database using Php PDO

Database Connection using Php PDO 

Connect to Database with PDO Line of code for connect to database.
When we want to change database, we just rewrite one line code.
Not all code. Below is Php script to connect to several database:

   <?php
                // for MySQL
                $conn = new PDO("mysql:host=$host;dbname=$db", $user, $pass);    
                // for SQLite
                $conn = new PDO("sqlite:$db");   
                // for postgreSQL
                $conn = new PDO("pgsql:host=$host dbname=$db", $user, $pass);
        ?>

Looking the script below:

      <?php
                // configuration variables
                $dbtype     = "sqlite";
                $dbhost     = "localhost";
                $dbname     = "test";
                $dbuser     = "root";
                $dbpass     = "admin";
                $dbpath     = "c:/test.db";      
                // switching for database selection
                switch($dbtype){
                  case "mysql":
                    $dbconn = "mysql:host=$dbhost;dbname=$dbname";
                    break;

                  case "sqlite":
                    $dbconn = "sqlite:$dbpath";
                    break;

                  case "postgresql":
                    $dbconn = "pgsql:host=$dbhost dbname=$dbname";
                    break;
                }        
                // database connection using PDO
                $connection = new PDO($dbconn,$username,$password);
     
        ?>
   

This is sample script to create database connection using PDO.
It is more portable, because when we change database, we just change at configuration part .

What is Php PDO ?

PHP Data Object is a Database Connection Abstraction Library for PHP 5.

PDO is extension written in a compiled language (C/C++).
It is a Lightweight Database abstract library.

Why to use PDO?

• PDO Supports number of database systems supported by PHP
• You need not to code for many database in php. Just write one and run anywhere
• PDO is written in compiled language so its speed is more than PHP libraries (ADOdb, PEAR DB) which are written in an interpreted language.
• It is very easy to install.

When to use PDO?

when we  develope Application from scratch, and also for getting speed we should use PDO.

How to Activate Php PDO ?

If php PDO extension is not enable in your php configuration then 

Go to php.ini and  remove comment from directives as below.

extension = php_pdo.dll
extension = php_pdo_mysql.dll 

Now Restart the Apache. And Done.