How to use PHP Data Object - Beginners PHP PDO Tutorial

In this tutorial we will see the basics of PDO(PHP Data Object) the getting started guide for beginners, first and foremost guys if you are still using old and deprecated MySQL extension then please stop using them right away and start use MySQLi or PDO(Recommended) cause MySQL extension is now completely deprecated and got removed from PHP7. Since mysql extension is deprecated it is recommended to switch over from mysql to pdo extension. PDO enables you to write neat and secure code because it has more secure ways which gives extra layer of security in web applications. PDO has it's own fundamental functions and methods which provides the easiest way to access one or more different databases. so let's get started.

 

Download Script

What is PDO ?

PDO is improved MySQL extension which is a neat and consistent way for accessing database, not only MySQL database it also supports many different databases like Microsoft SQL Server, Oracle, ODBC, PostgreSQL, SQLite and many more.

Why PDO ?

Well there are lot's of reasons to use PDO but the main is it reduces the SQL injection possibility because PDO support prepared statement with named parameters and it also has object oriented approach which makes it better performer over other extensions.

PDO MySQL Connection

Following is the connection string to access MySQL database, it is also called DSN(Data Source Name) a simple connection string to connect MySQL database, you can also connect other databases like oracle, sqlite, odbc etc… to get the available drivers use this : print_r(PDO::getAvailableDrivers());

$dbPDO = new PDO("mysql:host=localhost;dbname=database_name",$dbuser,$dbpass);

what if you get error, it also easy to handle error or exceptions

$dbCon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Wrap within try/catch

you should always wrap your PDO connection string in a try/catch to handle exceptions

<?php
 
    define("DB_DRIVER", "mysql");    // database driver, mysql
    define("DB_HOST", "localhost");  // db host
    define("DB_NAME", "dbpdo");      // database name
    define("DB_USER", "root");       // database username
    define("DB_PASS", "");           // database password
 
    try {
       $dbCon = new PDO(DB_DRIVER . ':host=' . DB_HOST . ';dbname=' . DB_NAME, DB_USER, DB_PASS);
       $dbCon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch(PDOException $ex) {
       echo $ex->getMessage();
       die();
    }

connection can be closed by setting $dbCon = null

Create Database and Table :

run the following SQL in your phpmyadmin to create database table.

CREATE DATABASE`dbpdo`;
CREATE TABLE `dbpdo`.`tbl_test` (
`id` INT( 5 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 25 ) NOT NULL ,
`email` VARCHAR( 25 ) NOT NULL
) ENGINE = MYISAM ;

Database connection

dbcon.php
PHP PDO connection to MySQL database in try/catch block for error handling.

<?php

$DB_host = "localhost";
$DB_user = "root";
$DB_pass = "";
$DB_name = "dbpdo";

 try
 {
     $DBcon = new PDO("mysql:host={$DB_host};dbname={$DB_name}",$DB_user,$DB_pass); 
     $DBcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 }
 catch(PDOException $e)
 {
     echo "ERROR : ".$e->getMessage();
 }

Create, Update and Delete :

crud.php
in this file you can get the code for insert, update and delete the rows.

<?php

require_once 'dbcon.php';

if(isset($_POST['save']))
{
 $uname = $_POST['uname'];
 $umail = $_POST['umail'];
 
 
 $stmt = $DBcon->prepare("INSERT INTO tbl_test(username,email) VALUES(:uname, :umail)");
 
 $stmt->bindparam(':uname', $uname);
 $stmt->bindparam(':umail', $umail);
 $stmt->execute();

}


if(isset($_GET['delete_id']))
{
 $id = $_GET['delete_id'];
 $stmt = $DBcon->prepare("DELETE FROM tbl_test WHERE id=:id");
 $stmt->execute(array(':id' => $id));
 header("Location: index.php");
}

if(isset($_GET['edit_id']))
{
 $stmt = $DBcon->prepare("SELECT * FROM tbl_test WHERE id=:id");
 $stmt->execute(array(':id' => $_GET['edit_id']));
 $editRow=$stmt->FETCH(PDO::FETCH_ASSOC);
 
}

if(isset($_POST['update']))
{
 $uname = $_POST['uname'];
 $umail = $_POST['umail'];
 $id = $_GET['edit_id'];
 
 $stmt = $DBcon->prepare("UPDATE tbl_test SET username=:uname, email=:uemail WHERE id=:id");
 $stmt->bindparam(':uname', $uname);
 $stmt->bindparam(':uemail', $umail);
 $stmt->bindparam(':id', $id);
 $stmt->execute();
 header("Location: index.php");
}


?>

Data read

index.php
this file contains HTML form to insert new records and read the data from database.
above created 'crud.php' file are included in this file.

<?php
require_once 'crud.php';
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<style type="text/css">
input
{
 width:100%;
}
</style>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>PDO CRUD Tutorial - cleartuts</title>
</head>
<body>
<center>
<form method="post">
<table border="1" width="40%" cellpadding="15">
<tr>
<td><input type="text" name="uname" placeholder="Username" value="<?php if(isset($_GET['edit_id'])){ print($editRow['username']); } ?>" /></td>
</tr>
<tr>
<td><input type="text" name="umail" placeholder="Email" value="<?php if(isset($_GET['edit_id'])){ print($editRow['email']); } ?>" /></td>
</tr>
<tr>
<td>
<?php
if(isset($_GET['edit_id']))
{
 ?>
    <button type="submit" name="update">update</button>
    <?php
}
else
{
 ?>
 <button type="submit" name="save">save</button>
 <?php
}
?>
</td>
</tr>
</table>
</form>

<br />

<?php

$stmt = $DBcon->prepare("SELECT * FROM tbl_test ORDER BY id DESC");
$stmt->execute();
?>
<table border="1" cellpadding="15" width="40%">
<?php
if($stmt->rowCount() > 0)
{
 while($row=$stmt->FETCH(PDO::FETCH_ASSOC))
 {
  ?>
     <tr>
     <td><?php print($row['username']); ?></td>
     <td><?php print($row['email']); ?></td>
        <td><a onclick="return confirm('Sure to Edit ? ')" href="index.php?edit_id=<?php print($row['id']); ?>">EDIT</a></td>
        <td><a onclick="return confirm('Sure to Delete ? ')" href="index.php?delete_id=<?php print($row['id']); ?>">DELETE</a></td>
     </tr>
     <?php
 }
}
else
{
 ?>
    <tr>
    <td><?php print("nothing here...");  ?></td>
    </tr>
    <?php
}
?>
</table>
</center>
</body>
</html>

that's it we have created here CRUD operations using PHP Data Object (PDO).
hope this was helpful for you.