How to create procedure of PDO database link in PHP

As we all know that in PHP 5.5 MySQL is deprecated and we have to move new extension like MyQLi or PDO (PHP Data Objects) for database integration, we have already distribute an article on How to use MySQLi_connect PHP and this article will explain you how to connect with MySQL database using PDO connection and we explain very few things in this tutorial like database connection, and error handling.

Connection

You all knows the old style of connecting to MySQL database:

<?php
 mysql_connect('localhost', 'database_user', 'database_password')
 or die('Could not connect: ' . mysql_error());
?>

Using PDO we create a new instance of the class, and describe the name of driver, database name, database user and database password:

<?php
#connect
$conn = new PDO('mysql:host=localhost;dbname=myDB', $db_username, $db_password);
?>

We specify mysql as name of driver in this case and followed by database credential required.

Errors

If there is an error in database connection how can we identify so here is try/catch for error handling.

<?php
try {
   $conn = new PDO('mysql:host=localhost;dbname=myDB',$db_username,$db_password);
   $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}
?>

PDO::ERRMODE_EXCEPTION will fire exceptions when occur. Using this procedure we can handle any exception.

Fetch

There is two ways to fetch data query and execute we explain both.

1. Query

<?php
$string = 'PHP4U'; # user submitted data
try {
   #connection
   $conn = new PDO('mysql:host=localhost;dbname=myDB',$db_username,$db_password);
   $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   $data = $conn->query('SELECT * FROM myTable WHERE name = '.$conn->quote($string));
   // $conn->quote used to protect SQL injection 
   foreach($data as $rows) {
         print_r($rows); 
      }
    } 
   catch(PDOException $e) {
      echo 'ERROR: ' . $e->getMessage();
   }
?>

In this code we used query and manually escaping data with $conn->quote this method is equivalent to mysql_real_escape_string; these both function escape quote user send in data.

2. Execute

<?php
$id = 10;
try {
#connection
   $conn = new PDO('mysql:host=localhost;dbname=myDB',$db_username,$db_password);
   $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    
   $data = $conn->prepare('SELECT * FROM users WHERE user_id = :user_id');
   $data->execute(array('user_id' => $id));
   while($rows = $data->fetch()) {
        print_r($rows);
    }
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}
?>

In this example we used prepare statement and execute it after that. In this example SQL injection is almost impossible, because $id never direct get into the query. we used :user_id as a placeholder.

$data->fetch() simply shows array of records and you can make it more stylish with FETCH_OBJ like below.

wile($row = $data->fetch(PDO::FETCH_OBJ)) {
    print_r($row);
}

Few fetch styling parameters:

• PDO::FETCH_ASSOC: return array with column names. 
• PDO::FETCH_BOTH: Default fetch style is BOTH it return array with column name and
  index start with 0. 
• PDO::FETCH_OBJ: return anonymous object with property name. 
• PDO::FETCH_NUM: return array with index by column number.