Connecting database with PDO [Part 1]

May 31, 2013 | | 2 comments
PDO database imageHello,

Today, i am going to show you how to connect database in PHP with PHP Data Objects or PDO. This is my first post on database and you will surprise why i am not using mysql_* functions as an early tutorial?
Because all mysql_* functions will be deprecated soon in later version of PHP. See page snippet from php.net.

php.net page

Definition of PDO

PHP DATA OBJECTS-is an object oriented library built into PHP that makes it easy for PHP scripts to talk with databases.

You may find PDO a little tough in beginning because we are habitual of too much easy mysql_* functions. I also faced this complexity but believe me you will be habitual after some practice. One of the benefits of PDO is that we don’t have to write separate functions to prevent SQL Injection because PDO driver do this itself.

Making connection with PDO

In PDO, we create new instance of class, specify database driver, database name and database password like below:
<?php
$conn = new PDO(‘mysql:host=$hostname;dbname=myDB’ $username,$password);
?>
Are you confused what we have written above? It is very easy, we specified the driver which is “mysql” here and followed with database connection parameters as we do in ‘mysql_connect()’ function.
If you don’t to use mysql driver. You can use other compatible drivers like SQlite etc.
Download: Fast, Fun, Awesome
Click and download firefox

Error handling

In mysql_* function we can write a die() or echo anything we want when connection does not establish. In the PDO we can use a try/catch block to handle errors like below:
<?php
try {
     $conn = new PDO(‘mysql:host=localhost;dbname=myDB’,$username,$password);
     $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e) {
          
         echo “ERROR:” getMessage();
 }
?>
In above code, the try block code will execute if connection establishes successfully and catch block code execute when connection fails. 
We have defined a constant (PDO uses predefined contants) to fire when they occur. In above code we have PDO::ERRMODE_EXCEPTION but we can use another:
  • PDO::ERRMODE_SILENT
  • PDO::ERRMODE_WARNING
  • PDO::ERRMODE_EXCEPTION

Prepared Statments

Prepared statement prevent SQL Injection. How? we will see below:
<?php
try {
     $conn = new PDO(‘mysql:host=localhost;dbname=myDB’,$username,$password);
     $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
     $st->prepare(“SELECT * FROM users WHERE id=:id”);
     $st=bindValue(“:id”,$id,PDO::PARAM_INT);

     $st->execute();
    while( $row=$st->fetch())
   {
    echo $row;
    }
    
}
catch(PDOException $e) {
          
         echo “ERROR:” getMessage();
 }
?>
We have used prepared statement here with :id placeholder instead of direct value because this is safe.
Prepared statements are used by many other databases and it provide security and faster execution.
After that, we now bind :id into $id with bindValue() and execute the script.

You can also use id=? in place of :id but many people think these are less human readable.

This was the small introduction of PDO and i want to keep it small. I will soon publish its Part-2.

For any questions or feedback, comment below.

Thanks for reading!

Posted in: database, php

2 Responses

Leave a Reply

Your email address will not be published. Required fields are marked *