What is PDO
It is an extension of the PHP language for database access. Fully object-oriented, it has several important features, in addition to supporting several database mechanisms.
Main Features:
- Parameterized queries
- Different types of return
- Different types of exception handling
- Transaction handling
PDO supported databases
PDO has support for the main relational databases on the market. This is possible because of the Driver concept it uses. Basically a driver is an extension that we install in PHP and that indicates how PDO will communicate with that specific database.
Main Available Drivers:
- MySQL
- SQL Server
- PostgreSQL
- Oracle
- SQLite
- FireBird
Compatibility between different databases
An advantage of PDO in relation to other database extensions for PHP is the possibility of executing queries in different DBMSs using the same classes and methods.
Theoretically, it would be possible to take any application that uses PDO and change the DBMS without problems, since the SQL language has standards that should be followed.
However, many databases implement specific features for their platform and as PDO does not abstract the SQL language, when we try to change we will have to deal with the differences.
How to enable PDO in PHP
The PDO main extension is already activated by default in PHP. What we actually need is to activate the DBMS driver extension that we will use.
When you open the php.ini file in the extensions section, you will probably see the following list of extensions with the prefix pdo_:
;extension=pdo_firebird
;extension=pdo_mysql
;extension=pdo_oci
;extension=pdo_odbc
;extension=pdo_pgsql
;extension=pdo_sqlite
If on Windows just uncomment the line, removing the ; from the front and restart the service. In Linux you need to check the distribution you are using how to do it, in Ubuntu for example, you could use apt-get to install and activate the database driver you are going to use:
sudo apt install php-sqlite3
for SQLite
sudo apt install php-pgsql
for PostgreSQL
sudo apt install php-mysql
for MySQL
Connecting
Create a Connection
To MySQL:
// open connection
$conn = new PDO("mysql:host={$host}; dbname={$name}; port={$port}", "{$user}", "{$pass}");
// set exception control
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
To PosgreSQL:
// open connection
$conn = new PDO("pgsql:dbname={$name}; user={$user}; password={$pass}; host={$host}; port={$port}");
// set exception control
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
To SQLite:
// open connection
$conn = new PDO("sqlite:{$file_name}");
// set exception control
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
PDO CRUD
Inserting a row into a table example
<?php
$conn = new PDO("sqlite:database/pdo.db");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try
{
$name = 'Zero';
$age = 23;
$sql = "INSERT INTO person(name,age) VALUES(:name,:age)";
$result = $conn->prepare($sql);
$result->execute([
':name' => $name,
':age' => $age,
]);
print "Person was inserted";
}
catch(Exception $e)
{
print $e->getMessage();
}
How it works.
- First, create a connect to the person database.
- Next, construct an SQL INSERT statement. The :name and :age are the placeholder that will be replaced by a publisher name.
- Then, prepare the INSERT statement for execution by calling the prepare() method of the $conn instance. The prepare() method returns an instance of the PDOStatement class.
- After that, execute the prepared statement by passing the values to the execute() method.
Inserting multiple rows into a table example
<?php
$conn = new PDO("sqlite:database/pdo.db");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try
{
$person = [
'name' => [
'Zero',
'Ace',
],
'age' => [
23,
27,
],
];
$sql = "INSERT INTO person(name,age) VALUES(:name,:age)";
$result = $conn->prepare($sql);
for ($i=0; $i < count($person) ; $i++)
{
$result->execute([
':name' => $person['name'][$i],
':age' => $person['age'][$i],
]);
}
}
catch(Exception $e)
{
print $e->getMessage();
}
Updating data from PHP using PDO
<?php
$conn = new PDO("sqlite:database/pdo.db");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try
{
$person = [
'id' => 1,
'name' => 'Zero',
'age' => 25,
];
$sql = "UPDATE person SET age = :age WHERE id = :id";
// prepare statement
$result = $conn->prepare($sql);
// bind params
//PDO::PARAM_INT (int) Represents the SQL INTEGER data type.
$result->bindParam(':id', $person['id'], PDO::PARAM_INT);
$result->bindParam(':age', $person['age']);
// execute the UPDATE statment
if( $result->execute() )
{
echo 'The person age has been updated successfully!';
}
}
catch(Exception $e)
{
print $e->getMessage();
}
PDO Querying Data
<?php
$conn = new PDO("sqlite:database/pdo.db");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try
{
$sql = "SELECT * FROM person";
$result = $conn->prepare($sql);
$result->execute();
$people = $result->fetchALL(PDO::FETCH_ASSOC);
if( $people )
{
foreach( $people as $person )
{
print $person['name'] . ' ' . $person['age'] . '<br>';
}
}
}
catch(Exception $e)
{
print $e->getMessage();
}
PDO Querying One Data
<?php
$conn = new PDO("sqlite:database/pdo.db");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try
{
$id = 2;
$sql = "SELECT * FROM person WHERE id = :id";
$result = $conn->prepare($sql);
$result->bindParam(':id', $id, PDO::PARAM_INT);
$result->execute();
$person = $result->fetch(PDO::FETCH_ASSOC);
if( $person )
{
print $person['name'] . ' ' . $person['age'] . '<br>';
}
}
catch(Exception $e)
{
print $e->getMessage();
}
Delete one row from a table
<?php
$conn = new PDO("sqlite:database/pdo.db");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try
{
$id = 2;
// construct the delete statement
$sql = "DELETE FROM person WHERE id = :id";
// prepare the statement for execution
$result = $conn->prepare($sql);
$result->bindParam(':id', $id, PDO::PARAM_INT);
// execute the statement
if( $result->execute() )
{
print 'person id ' . $id . ' was deleted successfully.';
}
}
catch(Exception $e)
{
print $e->getMessage();;
}
Delete multiple rows from a table
<?php
$conn = new PDO("sqlite:database/pdo.db");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try
{
$id = 2;
$sql = "DELETE FROM person WHERE id >= :id";
$result = $conn->prepare($sql);
$result->bindParam(':id', $id, PDO::PARAM_INT);
if( $result->execute() )
{
print $result->rowCount() . ' row(s) was deleted successfully.' ;
}
}
catch(Exception $e)
{
print $e->getMessage();
}
Final considerations
PDO is extremely important in the PHP ecosystem, many applications use it directly to access relational databases and most applications use it without knowing it, through components such as Laravel's Eloquent and Doctrine's ORM.
Top comments (0)