PHP and Databases: PDO

Unit: 13 of 18

In the previous lesson we familiarized ourselves with the MySQLi extension and how to use it when it comes to object-oriented programming in the PHP programming language. In this lesson we will familiarize ourselves with the PDO extension, which is used to work with MySQL databases, but also with many others, which we will see to be a huge advantage.

What is PDO?

PDO (PHP Data Objects) is a PHP library that provides improved functionality in database management compared to the standard PHP built-in functionality for the same purpose (mysql_, pg_…).

The biggest advantage of this library is that it has broad support for different database operating systems. In addition to MySQL databases, PDO supports 11 more types. This is important, especially if there is a need to change the interface for working with the database. How is this achieved? PDO allows a layer of abstraction between data and its access, meaning there is no direct connection to the type of database you are using. Always use the same PDO functions to create queries and retrieve data. This automatically means you don’t have to change the entire logic by which you access the data if you’ve changed the database type.

If we use a WAMP server, this extension is loaded by default during installation. Apart from the php_pdo extension, the extension for the base we intend to use is also active. In our case it will be MySQL, so the php_pdo_mysql extension:

Image 13.1. The php_pdo_mysql extension within the WAMP program

Instead of using the GUI, we can also start the extensions manually by uncommenting the following lines in the php.ini file, no matter what type of PHP installation we use (XAMPP, WAMPP, LAMP, MAMP, Laragon, etc.), because we always have a php.ini for configuring the PHP programming language:

extension=php_pdo.dll
extension=php_pdo_mysql.dll

Establishing and closing the connection

Establishing a database connection also creates a PDO object. The connection string is a required part of the PDO constructor. This connection string is also called DSN, or database source name:

$pdo = new PDO("mysql:host=localhost;dbname=test", "root", "");

On the previous line you can see the creation of the PDO object, within the brackets we specify the database parameters, the MySQL service host, the database name, the username and the password. The mysql code is always in the form:
“mysql:host=server_address;dbname=database_name”, “user_name”, “password”.

Let’s look at some more correctly spelled DSNs:

$pdo = new PDO("mysql:unix_socket=/tmp/mysql.sock;dbname=testdb"); 
$pdo = new PDO("oci:dbname=//localhost:1521/mydb");
$pdo = new PDO("mysql:host=localhost;port=3307;dbname=testdb");
$pdo = new PDO("sqlite2:/opt/databases/mydb.sq2");

The previous lines show one of the previously mentioned PDO advantages; even if we use different databases, MySQL, SQLite, OCI, we create the connection almost the same way. We see that the strings in the constructor of the PDO class are not identical, but they are based on almost the same logic. All possible differences end here, because further, over whatever basis we have chosen, we can use exactly the same methods thanks to the mentioned abstraction layer.

After opening the connection, let’s see how we can close the connection. To close the previously open connection like this, we destroy the PDO object:

$pdo=null; ili unset($pdo);

Next, we will assume that in the MySQL database myDatabase there is a table with the following structure (as we already had in the previous lesson):

create table users (id int primary key auto_increment, name varchar(50))

Executing SQL queries

To enable the SQL query, if of course the connection is already established, we use the PDO method. executive

$pdo->exec("insert into users values (null,'John')");

(This example, like the next one, assumes that the connection has already been opened and closed.) The exec method returns the number of lines entered, so it is possible to execute the line as follows:

$rowNumber = $pdo->exec("insert into users values (null,'john')");

We can execute update and delete queries in the same way :

$pdo->exec("update users set name = 'jane' where ime = 'john'");

Regarding deletion:

$pdo->exec("delete from users where name = 'john'");

Basically, we can see that in the case of insert, update and delete queries we can use the exec method everywhere, because here we are not waiting for the data to be downloaded from the database, but only a certain data manipulation (insertion, editing and deletion), which will return us whether the operation was successful or not.

However, for queries from which we also want feedback in the form of a ResultSet, i.e. we want to obtain certain data from the database, we use the Query method :

$result = $pdo->query("select * from users");

This method returns the PDOStatement object, which contains, among other things, all the rows of the ResultSet (objects of the PDORow class). After getting those rows, we could examine all the names with the following loop:

foreach($result as $row)
         echo $row["name"];

In addition to rows, it is possible to retrieve another collection of data as a result of the query, using the fetch method (and its variations). For example, we can get the associative string as follows:

$asNiz = $result->fetchAll(PDO::FETCH_ASSOC);
foreach($asArray as $k=>$v)
echo "Row: " . $k . " id: " . $v["id"] . " Name: " . $v["name"] . "<br>";            

We can even get a direct class with this method:

class users
    {
     public $id;
     public $name;   
    }
$asNiz = $result->fetchAll(PDO::FETCH_CLASS, 'users');
foreach($asArray as $k)
     echo "Id: " . $k->id . " Name: " . $k->name . "<br>";

We can see from the example that for this approach we need to have a class already prepared.

You can find all fetch parameters (constants) at:

http://php.net/manual/en/pdo.constants.php

Query preparation (prepared statements)

Preparing a PDO query involves creating a separate query object instead of sending the query directly to the database (something similar I’ve seen before in MySQL and the object-oriented programming approach).

So far, I’ve enabled queries like this:

$pdo->exec("insert into users values (null,'john')");

The same query, activated by a query (statement) PDO object, would look like this:

$query = $pdo->prepare("insert into users values (null,:name)");
$name="john";
$query->bindParam(":name",$name);
$query->execute();

Query preparation is a highly secure procedure and should be used when using the PDO library in a production environment. In addition to this, we also get a more transparent query that we can also loop through, changing only the values that go into the bindParam method.

If we make an entry in the database, with the help of the PDO object we can always get information about the last inserted ID, using the lastInsertId() function:

echo $pdo->lastInsertId();

What PDO method do we use to start a simple query, from which we don’t need a ResultSet?

exec

prepare

query

Transaction Management

We have familiarized ourselves with transactions in previous lessons. PDO has the ability to execute transactions in the sense of working with databases. Let’s remember that the transactions were executed according to the “all or nothing” system. In PDO, a transaction begins with the beginTransaction() method of the PDO object and ends with the rollBack() or commit() method of the same object. Rollback, of course, rolls back the transaction, while Commit commits it:

$pdo->beginTransaction();
$pdo->query("insert into users values (null,'thomas')");
$result = $pdo->query("select * from users where name = 'thomas'");
$resultFetch = $result->fetch(PDO::FETCH_ASSOC);
echo $resultFetch["name"];
$pdo->rollBack(); 

This example will first insert a user named thomas into the database, then execute a select query, which will return the inserted row ( thomas ). At the end of the example the rollBack() method is called and the transaction is rolled back. As a result, the data entered in the transaction process will not be accepted.

For data to actually be committed to the database, the transaction must be committed using the commit() method.

PDO transactions should always be used in combination with the PDOException class, i.e. with any exception handler:

$pdo->beginTransaction();
try
{
$pdo->query("insert into users values (null,'thomas')");
$result = $pdo->query("select * from users where name = 'thomas'");
$resultFetch = $result->fetch(PDO::FETCH_ASSOC);
echo $resultFetch["name"];
$pdo->commit();
}
catch(PDOException $ex)
{
    $pdo->rollBack(); 
}

Here we can see that all queries are executed within a try block. If an error occurs, the catch block is activated, in which the rollBack() function is called, and in this way, the data entered in the transaction process will not be accepted.

Other ways to work with PDO

In addition to everything we mentioned in this lesson, here we will also provide short examples with descriptions of how you can create some code for some of the specific needs (creating a table, inserting data, etc.) to have a single reference point for standard programming needs when working with the database through the PDO extension.

Creating a connection

<?php
$hostname = "localhost";
$username = "username";
$password = "password";
try {
  $connection = new PDO("mysql:host=$hostname;dbname=myDB", $username, $password);
  $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  
  echo "Connected successfully!";
} catch(PDOException $e) {
  echo "Connection to server has failed: " . $e->getMessage();
}
?>

Creating a database

<?php
$hostname = "localhost";
$username = "username";
$password = "password";
try {
  $connection = new PDO("mysql:host=$hostname", $username, $password);
  $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $sql = "CREATE DATABASE my_database";
  $connection->exec($sql);
  echo "Database my_database created successfully";
} catch(PDOException $e) {
  echo $sql . "<br>" . $e->getMessage();
}
$connection = null;
?>

Creating a table

<?php
$hostname = "localhost";
$username = "username";
$password = "password";
try {
  $connection = new PDO("mysql:host=$hostname", $username, $password);
  $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $sql = "CREATE TABLE User (
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(30) NOT NULL,
  last_name VARCHAR(30) NOT NULL,
  email VARCHAR(50),
  password VARCHAR(200),
  datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  )";
  $connection->exec($sql);
  echo "Table User created successfully";
} catch(PDOException $e) {
  echo $sql . "<br>" . $e->getMessage();
}
$connection = null;
?>

Inserting data

<?php
$hostname = "localhost";
$username = "username";
$password = "password";
$database = "User";
try {
  $connection = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);
  $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $sql = "INSERT INTO User (first_name, last_name, email)
  VALUES ('Bruce', 'Wayne', 'bruce@wayne.com')";
  $connection->exec($sql);
  echo "User created successfully";
} catch(PDOException $e) {
  echo $sql . "<br>" . $e->getMessage();
}
$connection = null;
?>

Get the last ID entered in the database

<?php
$hostname = "localhost";
$username = "username";
$password = "password";
$database = "User";
try {
  $connection = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);
  $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $sql = "INSERT INTO User (first_name, last_name, email)
  VALUES ('Peter', 'Parker', 'peter@parker.com')";
  $connection->exec($sql);
  $last_inserted_id = $connection->lastInsertId();
  echo "User created successfully. Last inserted ID is: " . $last_inserted_id;
} catch(PDOException $e) {
  echo $sql . "<br>" . $e->getMessage();
}
$connection = null;
?>

Inserting multiple rows at once

<?php
$hostname = "localhost";
$username = "username";
$password = "password";
$database = "User";
try {
  $connection = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);
  $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $connection->beginTransaction();
  $connection->exec("INSERT INTO User (first_name, last_name, email)
  VALUES ('Bruce', 'Wayne', 'bruce@wayne.com')");
  $connection->exec("INSERT INTO User (first_name, last_name, email)
  VALUES ('Peter', 'Parker', 'peter@parker.com')");
  $connection->exec("INSERT INTO User (first_name, last_name, email)
  VALUES ('Klark', 'Kent', 'klark@kent.com')");
  $connection->commit();
  echo "New Users created successfully";
} catch(PDOException $e) {
  $connection->rollback();
  echo "Error: " . $e->getMessage();
}
$connection = null;
?>

Data selection

<?php
$hostname = "localhost";
$username = "username";
$password = "password";
$database = "User";
try {
  $connection = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);
  $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $sql = $connection->prepare("SELECT id, first_name, last_name FROM User");
  $sql->execute();
  $result = $sql->setFetchMode(PDO::FETCH_ASSOC);
  $results = $sql->fetchAll();
  
  foreach($results as $column => $value) {
    echo $value . "<br>";
  }
} catch(PDOException $e) {
  echo "Error: " . $e->getMessage();
}
$connection = null;
?>

Using the WHERE option in PDO

<?php
$hostname = "localhost";
$username = "username";
$password = "password";
$database = "User";
try {
  $connection = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);
  $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $sql = $connection->prepare("SELECT id, first_name, last_name FROM User WHERE first_name = 'Bruce'");
  $sql->execute();
  $result = $sql->setFetchMode(PDO::FETCH_ASSOC);
  $results = $sql->fetchAll();
  
  foreach($results as $column => $value) {
    echo $value . "<br>";
  }
} catch(PDOException $e) {
  echo "Error: " . $e->getMessage();
}
$connection = null;
?>

Using the ORDER BY option in PDO

<?php
$hostname = "localhost";
$username = "username";
$password = "password";
$database = "User";
try {
  $connection = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);
  $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $sql = $connection->prepare("SELECT id, first_name, last_name FROM User ORDER BY first_name");
  $sql->execute();
  $result = $sql->setFetchMode(PDO::FETCH_ASSOC);
  $results = $sql->fetchAll();
  
  foreach($results as $column => $value) {
    echo $value . "<br>";
  }
} catch(PDOException $e) {
  echo "Error: " . $e->getMessage();
}
$connection = null;
?>

Deleting data in the table

<?php
$hostname = "localhost";
$username = "username";
$password = "password";
$database = "User";
try {
  $connection = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);
  $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $sql = "DELETE FROM User WHERE id = 1";
  $connection->exec($sql);
  echo "User deleted successfully";
} catch(PDOException $e) {
  echo $sql . "<br>" . $e->getMessage();
}
$connection = null;
?>

Updating the data in the table

<?php
$hostname = "localhost";
$username = "username";
$password = "password";
$database = "User";
try {
  $connection = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);
  $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $sql = "UPDATE User SET first_name = 'Thomas' WHERE id = 1";
  $result = $connection->prepare($sql);
  $result->execute();
  echo $result->rowCount() . " records updated!";
} catch(PDOException $e) {
  echo $sql . "<br>" . $e->getMessage();
}
$connection = null;
?>

Exercise 13.01

Using the knowledge gained in this lesson, create a table in the database named user.

The table can have id, name, password and status.

After that, create a connection to the database and the code itself, which will input 3 users, change the data of the second user, and then print all users on exit.

After the user download is finished, delete all entered users from the database.

Of course, you use the PDO library for work, in an object-oriented manner.