Using PDO with MySQL

PDO is a Database Access Abstraction Layer, an application programming interface which unifies the communication between a computer application and different databases. In this chapter we will disscus how to use PDO with MySQL.

According to PHP.NET, PDO class is supported in PHP since version 5.1.0

To connect to MySQL, use the following code.
pdo_conn.php

<?php
$host = 'localhost';
$db='yourdb';
$charset = 'utf8';
$userdb = 'youruser';      // or root if you do not create user
$passdb = 'yourpassword';   //or blank if you did not set password


$pdo=null;
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";

$options = array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES   => false,

);

try {

$pdo = new PDO($dsn, $userdb, $passdb, $options);

} catch(PDOException $e) {
echo "Could not connect to database!";
}
?>

Create the usernames table. You can create it by "copy and paste" the following create table statement.

 CREATE TABLE `usernames` (
    `userid` int(11) NOT NULL AUTO_INCREMENT,
    `lastname` varchar(20) DEFAULT NULL,
    `firstname` varchar(20) DEFAULT NULL,
    `username` varchar(20) DEFAULT NULL,
    `password` varchar(50) DEFAULT NULL,
    `email` varchar(30) DEFAULT NULL,
    `role` varchar(10) DEFAULT NULL,
    `city` varchar(20) DEFAULT NULL,
    `country` varchar(20) DEFAULT NULL,
    `bio` text,
    `website` varchar(100) DEFAULT NULL,
    `active` tinyint(4) DEFAULT NULL,
    PRIMARY KEY (`userid`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Let´s insert a recod in our usernames table. In this code we will use bindParam method.
pdo_insert_user.php

<?php
// insert using bindParam method
include('pdo_conn.php');

$isql="insert into usernames(lastname, firstname, username, password, email, role, city, country, bio, website, active)
values (:lastname, :firstname, :username, :password, :email, :role, :city, :country, :bio, :website, :active)";

$lastname="Smith";
$firstname="John";
$username="johnsm";
$password="secret";
$email="johnsm@yahoo.com";
$role="user";
$city="Clearwater";
$country="USA";
$bio="I am a great guy!";
$website="http://myearth.today";
$active=1;

 try {

$stmt = $pdo->prepare($isql);

$stmt->bindParam(':lastname', $lastname, PDO::PARAM_STR);
$stmt->bindParam(':firstname', $firstname, PDO::PARAM_STR);
$stmt->bindParam(':username', $username, PDO::PARAM_STR);
$stmt->bindParam(':password', $password, PDO::PARAM_STR);
$stmt->bindParam(':email', $email, PDO::PARAM_STR);
$stmt->bindParam(':role', $role, PDO::PARAM_STR);
$stmt->bindParam(':city', $city, PDO::PARAM_STR);
$stmt->bindParam(':country', $country, PDO::PARAM_STR);
$stmt->bindParam(':bio', $bio, PDO::PARAM_STR);
$stmt->bindParam(':website', $website, PDO::PARAM_STR);
$stmt->bindParam(':active', $active, PDO::PARAM_INT);

$stmt->execute();

$insertId = $pdo->lastInsertId();

echo "LastID=".$insertId;

echo "New records inserted successfully";
    }
        catch(PDOException $e)
    {

   echo "Error: " . $e->getMessage();
    }
?>
OUTPUT:

LastID=1  New records created successfully
The is a second way to insert record with PDO. In this method we are using placeholder.

pdo_insert_user2.php

<?php
//insert using placeholder
include('pdo_conn.php');

$isql="insert into usernames(lastname, firstname, username, password,email, role, city, country, bio, website, active)
values (?,?,?,?,?,?,?,?,?,?,?)";

$lastname="Barry";
$firstname="John";
$username="johnb";
$password="secret";
$email="johnb@yahoo.com";
$role="user";
$city="Brooklyn";
$country="USA";
$bio="I am a great guy!";
$website="http://learn-coding.today";
$active=1;

 try {

$stmt = $pdo->prepare($isql);

//argument for execute method must be array[]
$stmt->execute([$lastname, $firstname, $username, $password, $email, $role, $city, $country, $bio, $website, $active]);

$insertId = $pdo->lastInsertId();

echo "LastID=".$insertId;

echo "New records inserted successfully";
    }
        catch(PDOException $e)
    {

   echo "Error: " . $e->getMessage();
    }
?>

OUTPUT:

LastID=2  New records inserted successfully

Updating record, using bindParameter method.

pdo_update_user.php

<?php
//update using bindParam method
include('pdo_conn.php');

$isql="update usernames set role=:role where username=:username";

$username='johnsm';

$role="ADMIN";

 try {

$stmt = $pdo->prepare($isql);

$stmt->bindParam(':username', $username, PDO::PARAM_STR);
$stmt->bindParam(':role', $role, PDO::PARAM_STR);

$stmt->execute();

echo $username."´s records updated successfully!";
    }
        catch(PDOException $e)
    {

   echo "Error: " . $e->getMessage();
    }
?>

OUTPUT:

johnsm´s records updated successfully!
Updating record using placeholder.

pdo_update_user2.php

<?php

//update using placeholder
include('pdo_conn.php');

$usql="update usernames set role=? where username=?";

$username='johns';

$role="OWNER";

 try {

$stmt=$pdo->prepare($usql);
$stmt->execute([$role, $username]);

echo $username."´s records updated successfully!";
    }
        catch(PDOException $e)
        {
        echo "Error: " . $e->getMessage();
        }
?>

 
OUTPUT:

johnsm´s records updated successfully!

Selecting users

pdo_select_user.php

<?php

include('pdo_conn.php');

$sql="select userid, firstname, lastname, username, role from usernames";


if(!$stmt=$pdo->query($sql))
{
echo mysql_errno() . ": ";
echo mysql_error() . "<br/>";
}
else
{
    while($row = $stmt->fetch(PDO::FETCH_NUM))
	{
	$userid=$row[0];
	$firstname=$row[1];
	$lastname=$row[2];
	$username=$row[3];
	$role=$row[4];

    echo $userid."|". $firstname. "| ".$lastname." | ".$username." | ".$role."<br/>";

	}
}
?>

OUTPUT:

11|Barry| John | johnb |  user

3|Silver| John | johnsl  | user

9|Smith| John | johnsm  | user

6|Silver| John | johnsl  | user

8|Snider| John | johns | ADMINN

Delete user using bindParam method.

pdo_delete_user.php

<?php
 //delete using bindParam method
include('pdo_conn.php');

$dsql="delete from usernames where lastname=:lastname";

$lastname="Johnsons";

 try {

$stmt = $pdo->prepare($dsql);

$stmt->bindParam(':lastname', $lastname, PDO::PARAM_STR);

$stmt->execute();

echo $lastname."´s records deleted successfully";
	}
	    catch(PDOException $e)
	    {
	   echo "Error: " . $e->getMessage();
	    }
?>

OUTPUT:

johns´s records deleted successfully!

Delete user using placeholder.

pdo_delete_user2.php

<?php
//delete using placeholder
include('pdo_conn.php');

$dsql="delete from usernames where lastname=?";

$lastname="Barry";

 try {

$stmt = $pdo->prepare($dsql);

$stmt->execute([$lastname]); //execute required array

echo $lastname."´s records deleted successfully";
	}
	    catch(PDOException $e)
	    {
	   echo "Error: " . $e->getMessage();
	    }
?>

OUTPUT:

Barry´s records deleted successfully!
Displaying column´s names

pdo_get_headers.php

<?php

include('pdo_conn.php');

    $rs = $pdo->query('SELECT * FROM usernames LIMIT 0');
	for ($i = 0; $i < $rs->columnCount(); $i++) {
	    $col = $rs->getColumnMeta($i);
	    echo $col['name']."<br/>";
	}

?>

OUTPUT:

userid

lastname

firstname

username

password

email

role

city

country

bio

website

active