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.