February 24, 2024 5:01 am

How to use PDO database connection in PHP

As we all know that in PHP 5.5 MySQL is deprecated and we have to move new extensions like MyQLi or PDO (PHP Data Objects) for database integration, we have already publish 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, insert, delete, update and select queries with prepare statement, multiple insert and error handling.

How to use PDO database connection in PHP

Connection

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

<?php
# Connect
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 = 'PHPGang'; # 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.

while($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.

Result validation with count if there is no records found in given query:

$data->execute(array('user_id' => $id));
$result = $data->fetchAll();

#count records
if ( count($result) ) {
    foreach($result as $row) {
        print_r($row);
    }
} else {
    echo "data not found.";
}

Full code of fetching:

<?php
$id = 10;
try {
#connection
  $conn = new PDO('mysql:host=localhost;dbname=myDB', $db_username, $db_password);
  $data = $conn->prepare('SELECT * FROM users WHERE user_id = :user_id');
  $data->execute(array('user_id' => $id));

  $result = $data->fetchAll();

  if ( count($result) ) { 
    foreach($result as $row) {
      print_r($row);
    }   
  } else {
    echo "data not found.";
  }
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}
?>

Insert

This example shows you how to insert record in a table and also include multiple records insertion with different data.

<?php
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('INSERT INTO users VALUES(:user_name)');
  $data->bindParam(':user_name', $name);

  $name = 'Huzoor Bux';
  $data->execute();

  #if you want to insert multiple records add new code block like this repeat this for multiple records.
  $name = 'PHPGang.com';
  $data->execute();

  $name = 'demo.phpgang.com';
  $data->execute();

#exception handiling
} catch(PDOException $e) {
  echo $e->getMessage();
}
?>

Prepare query once and execute it multiple time as you required in your script.

Insert single record and show affected rows:

$data = $conn->prepare('INSERT INTO users VALUES(:user_name)');
  $data->execute(array(
    ':user_name' => 'Huzoor Bux'
  ));

  # get the number of affected rows.
  echo $data->rowCount(); // 1

Update

<?php
$id = 10;
$name = "Huzoor Bux";

try {
  $conn = new PDO('mysql:host=localhost;dbname=myDB', $db_username, $db_password);
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $data = $conn->prepare('UPDATE users SET user_name = :user_name WHERE user_id = :user_id');
  $data->execute(array(
    ':user_id'   => $id,
    ':user_name' => $name
  ));

  echo $data->rowCount(); // 1
} catch(PDOException $e) {
  echo 'Error: ' . $e->getMessage();
}
?>

Delete

<?php
$id = 10; // select id to delete from users table

try {
  $conn = new PDO('mysql:host=localhost;dbname=myDB', $db_username, $db_password);
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $data = $conn->prepare('DELETE FROM users WHERE user_id = :user_id');
  $data->bindParam(':user_id', $id); // we used bindParam method
  $data->execute();

  echo $data->rowCount(); // 1
} catch(PDOException $e) {
  echo 'Error: ' . $e->getMessage();
}
?>

If you are still using MySQL API then you must stop and start development with PDO or MySQLi these both extensions more secure.

Closing

I hope you like this explanation please comment your views and improvements in our upcoming tutorials.

Author Huzoor Bux

I am Huzoor Bux from Karachi (Pakistan). I have been working as a PHP Developer from last 5+ years, and its my passion to learn new things and implement them as a practice. Basically I am a PHP developer but now days exploring more in HTML5, CSS and jQuery libraries.


Tutorial Categories:

6 responses to “How to use PDO database connection in PHP”

  1. Sonelal says:

    To use PDO ,Do we need to include some library?

  2. Nigam Kumar Tiwari says:

    Really Interesting…….It will help us to create more secure & reliable projects.

  3. Gautam Sharma says:

    Hi sir..

    i created a simple crud app using PDO and

    When I trying to save record I get this error message

    Fatal error:
    Uncaught exception ‘PDOException’ with message ‘SQLSTATE[23000]:
    Integrity constraint violation: 1062 Dubbele ingang ” voor zoeksleutel
    ‘password” in C:wampwwwpdocrud.php on line 15

    PDOException:
    SQLSTATE[23000]: Integrity constraint violation: 1062 Dubbele ingang ”
    voor zoeksleutel ‘password’ in C:wampwwwpdocrud.php on line 15

    Dubbele ingang ” voor zoeksleutel. = in English means that this: Double input ” for search key
    But I have no password in there, only in dbcon. php is a password..

    how can i solve this…
    please help me..

  4. WP DEV says:

    Hi,
    I ‘m wp developer and need one question about the feedburner.

    Requirement : I want to adjust our daily feedburner post with category wise as well as want to add the some header and footer in feedburner.

    Any idea about this ?

    Thanks,

  5. Max John says:

    U are very good . Please can u do a tutorial on the difference between the procedural (fonctiom ) and PDO with a pratical example on handling a form or something like that ?
    I like ? ur articles. They tackle interesting subjects.

Leave a Reply

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