PHP PDO Tutorial

The database is a very important part of any web application to store and retrieve the data from the application. The database can be accessed in multiple ways in PHP. PDO and MySQLi are the PHP extensions to access the databases using PHP. The PDO (PHP Data Objects) is introduced first in PHP version 5.1 to make the connection with the database to perform the different types of database tasks. It provides a lightweight interface to access the database through a PHP script. The data abstraction of the database is not provided by PDO but the data can be accessed from multiple databases more easily using PDO. Most of the popular databases are supported by PDO such as MySQL, SQLite, Oracle, MS SQL Server, PostgreSQL, etc. The benefits of using PDO, the difference between PDO and MySQLi, and the way of connecting and accessing the MySQL database using PDO are described in this tutorial.

Benefits of PDO

Some benefits of using PDO to access the databases are mentioned in the following:

Many helper functions exist in PDO to automate the different types of development tasks.
It provides security from hackers using prepared statements while accessing the data from the database.
It contains better error handling features to handle the different types of errors more efficiently.
Multiple databases can be accessed using the unified API of PDO.

Differences Between PDO and MySQLi

PDO	MySQLi
It works in an object-oriented manner.	It can work in both object-oriented and procedural manner.
It supports many database drivers.	It supports MySQL only.
It supports the client-side prepared statement.	It does not support the client-side prepared statement.
It is more secure than MySQLi.	It is less secure than PDO.
It supports the named parameters.	It does not support the named parameters.

Create the MySQL Database

Run the following SQL command to create a database named “test” to check the uses of PDO to perform the different types of database tasks:

CREATE DATABASE test;

Create a Table

Run the following SQL command to create a table named “books” inside the “test” database:

CREATE TABLE books(
id INT(255) NOT NULL PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(150) NOT NULL,
author VARCHAR(150) NOT NULL
)ENGINE=InnoDB;

Make a Database Connection Using PDO

Create a PHP file with the following script that makes the connection with the “test” database using PDO:

<?php
//Initialize connection variables
$hostname = "localhost";
$database = "test";
$user = "root";
$password = "12345";

try
{
//Create database connection using PDO
$db_connection= new PDO("mysql:host=$hostname;dbname=$database",$user, $password);

}
catch(Exception $e){ $statement->execute([

//Print error message
echo "Unable to connect with the database." . $e->getMessage();
}

?>

Insert a Row Into a Table

Create a PHP file with the following script that inserts a record into the books table using a PDO connection. The database connection file is included at the beginning of the script using the include_once() function. A prepared statement for insert query is created to insert a new record into the table.

<?php
//Create a database connection
include_once 'pdo_connect.php';

//Set values for inserting one record to the books table
$title = 'The Joy of PHP Programming: A Beginner’s Guide';
$author = 'Alan Forbes';

//Set the insert query
$query = 'INSERT INTO books(id, title, author) VALUES(:id, :title, :author)';

//Create prepared statement
$statement = $db_connection->prepare($query);

//Execute the insert query
$statement->execute([
':id' => NULL,
':title' => $title,
':author' => $author
]);

echo 'One record is inserted successfully';
?>

Output:
The following output appears after executing the previous script if a new record is inserted successfully:

Select a Data from a Table

Create a PHP file with the following script that selects all records of the books table using a PDO connection. The database connection file is included at the beginning of the script using the include_once() function. A prepared statement for a select query is created to read all records from the table.

<?php

//Create a database connection
include_once 'pdo_connect.php';

//Set query to read all records
$query = 'SELECT * FROM books';

//Create prepared statement
$statement = $db_connection->query($query);

//Read all records from the books table
$books = $statement->fetchAll(PDO::FETCH_ASSOC);

if ($books) {
//Iterate and print each record from the table
foreach ($books as $book) {
echo "Book name: ".$book['title'] . " ";
echo "Author name: ".$book['author'] . " ";
}
}

Output:
The following output appears after executing the previous script if the table contains one record:

Update a Data from the Table

Create a PHP file with the following script that updates a record of the books table using a PDO connection. The database connection file is included at the beginning of the script using the include_once() function. A prepared statement for an update query is created to update a record of the books table that contains the id value of 1.

<?php

//Create a database connection
include_once 'pdo_connect.php';

//Set new values to update
$book = [
'id' => 1,
'title' => 'PHP & MySQL Novice to Ninja',
'author' => 'Kevin Yank'
];

//Set the update query
$query = "UPDATE books
SET title = :title, author = :author
WHERE id = :id";

//Create prepared statement
$statement = $db_connection->prepare($query);

//Bind params for the prepared statement
$statement->bindParam(':id', $book['id'], PDO::PARAM_INT);
$statement->bindParam(':title', $book['title']);
$statement->bindParam(':author', $book['author']);

//Execute the update query
if ($statement->execute()) {
echo 'The book information has been updated.';
}

Output:
The following output appears after executing the previous script if the record of the table is updated successfully:

Delete a Row from the Table

Create a PHP file with the following script that deletes a record of the books table using a PDO connection. The database connection file is included at the beginning of the script using the include_once() function. A prepared statement for the delete query is created to delete a record of the books table that contains the id value of 1.

<?php

//Create a database connection
include_once 'pdo_connect.php';

//Set query for delete
$query = 'DELETE FROM books WHERE id = :id';

$id = 1;

//Set prepare statement
$statement = $db_connection->prepare($query);
$statement->bindParam(':id', $id, PDO::PARAM_INT);

//Execute the delete query
if ($statement->execute()) {
echo 'One record has been deleted.';
}

Output:
The following output appears after executing the previous script if the record of the table is deleted successfully:

Conclusion

Different features of PDO and the way of using PDO for making database connections and implementing the CRUD operation are shown in this tutorial by creating the MySQL database with a table.