PDO (PHP Data Objects)

What is PDO?

PDO (PHP Data Objects) is a PHP extension used to access and work with different types of databases like MySQL, Oracle etc by writing same code.

It acts as a data abstraction layer that helps in issuing query and fetch data regardless of database.

How to connect to a database using PDO?

We call the PDO constructor to create database connection.

//constants
define('DB_DSN', 'mysql:host=localhost;dbname=mydb');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', 'root123');

try {
 //create pdo connection object
 $pdocon = new PDO(DB_DSN, DB_USERNAME, DB_PASSWORD);
} catch (PDOException $e) {
 printf("Error: " . $e->getMessage());
 die();
}

In the above code PDO constructor takes three arguments namely dsn, username and password.

The DB_DSN holds the Data Source Name which contains two items, a database driver name and the other is the database name. DB_USERNAME and DB_PASSWORD holds the username and password of the database respectively.

Throughout this tutorial we will use $pdocon as PDO object.

How to get PDO error code?

We use the errorCode() method to get the error code when error occurs. These error codes are standard SQLSTATE codes.

$pdocon->errorCode();

How to get PDO error message?

We use the errorInfo() method to get the error message when error occurs. This method will return an array. This array consists of three elements. The 0th element represents the SQLSTATE code. The 2nd element contains the database driver specific error code. And the 3rd element contains the database driver specific error message.

$pdocon->errorInfo();

Types of query execution

There are basically three type of query executions.

Query execution with no result set: Query like INSERT, UPDATE and DELETE returns no result set and only tells us about the number of row affected.

Query execution with result set: Query like SELECT generally returns a result set and also the number of rows returned.

Query executed multiple times: For instance, running INSERT query multiple time by passing different data.

INSERT, UPDATE and DELETE data

To insert, update and delete data we use the exec() method and it returns the number of rows affected.

In the following example we execute INSERT query.

//query
$query = sprintf("INSERT INTO student (`studentid`, `name`, `branch`) VALUES ('s001', 'Yusuf', 'CSE')");

//execute the query
$affectedRows = $pdocon->exec($query);

//number of rows affected
printf("Inserted Rows: %d", $affectedRows);  //this will print "Inserted Rows: 1"

In the following example we execute UPDATE query.

//query
$query = sprintf("UPDATE student SET name = 'Alice' WHERE studentid = 's2016'");

//execute the query
$affectedRows = $pdocon->exec($query);

//number of rows affected
printf("Updated Rows: %d", $affectedRows);  //this will print "Updated Rows: 1"

In the following example we execute DELETE query.

//query
$query = sprintf("DELETE FROM student WHERE studentid = 's101'");

//execute the query
$affectedRows = $pdocon->exec($query);

//number of rows affected
printf("Deleted Rows: %d", $affectedRows);  //this will print "Deleted Rows: 1"

SELECT data

To select data we use the query() method which returns a result set as PDOStatement object. To know about the total number of rows returned we use the rowCount() method.

//query
$query = sprintf("SELECT studentid, name, branch FROM student LIMIT 0,10");

//execute query
$result = $pdocon->query($query);

//total rows returned
printf("Selected Rows: %d", $result->rowCount());

//loop through the results
foreach ($result as $row) {
 printf("StudentID: %s Name: %s Branch: %s", $row['studentid'], $row['name'], $row['branch']);
}

Prepared Statement

We use prepared statement when we know that a same query will be executed multiple times with only change in parameters. For example, the INSERT query for a student table will always have the same template and only the insert data will change.

How to create a prepared statement?

We use the prepare() method to create a prepared statement. It is used to ready a query for execution.

$stmt = $pdocon->prepare($query);

How to execute a prepared statement?

We use the execute() method to execute a prepared statement. This method takes an array argument containing the placeholder and values. We will see that in the following example.

How to get the total number of affected rows?

To get the total number of affected rows we use rowCount() method.

UPDATE data using prepared statement

In the following example we execute an UPDATE query which has two placeholders :nameand :studentid. When we execute the prepared statement we call the execute() method with an array containing the placeholders and values.

//query
$query = sprintf("UPDATE student SET name = :name WHERE studentid = :studentid");

//prepare the statement
$stmt = $pdocon->prepare($query);

//execute the prepared statement
$stmt->execute(array(
 ":name" => "Alice",
 ":studentid" => "s101"
));

//affected rows
printf("Updated Rows: %d", $stmt->rowCount());

Bind parameters

This is another way to assign value to the placeholders in the query. We use the bindParam()and pass the placeholder, value and the datatype.

Following are the list of some of the data types that can be passed to the bindParam() method.

• PDO::PARAM_BOOL for boolean data
• PDO::PARAM_INT for integer data
• PDO::PARAM_STR for string data
• PDO::PARAN_NULL for null data

$stmt->bindParam(":name", $name, PDO::PARAM_STR);

UPDATE data using the bind parameter

//query
$query = sprintf("UPDATE student SET name = :name WHERE studentid = :studentid");

//prepare the statement
$stmt = $pdocon->prepare($query);

//bind parameter
$name = "Alice";
$studentid = "101";
$stmt->bindParam(":name", $name, PDO::PARAM_STR);
$stmt->bindParam(":studentid", $studentid, PDO::PARAM_STR);

//execute the prepared statement
$stmt->execute();

//affected rows
printf("Updated Rows: %d", $stmt->rowCount());

Retrieve data

We can use two methods fetch() and fetchAll() to retrieve data.

In the following example we are using the fetch() methods to retrieve data from the student table. We are passing the argument PDO::FETCH_ASSOC to the fetch() method. This will make the method return result as an associative array.

//query
$query = sprintf("SELECT studentid, name, branch FROM student LIMIT 0,10");

//execute the query
$stmt = $pdocon->query($query);

//returned rows
printf("Selected Rows: %d", $stmt->rowCount());

//loop through the result
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
 printf("StudentID: %s Name: %s Branch %s", $row['studentid'], $row['name'], $row['branch']);
}

In the following example we are using the fetchAll() method and passing the argument PDO::FETCH_ASSOC to get associative array as result.

//query
$query = sprintf("SELECT studentid, name, branch FROM student LIMIT 0,10");

//execute the query
$stmt = $pdocon->query($query);

//returned rows
printf("Selected Rows: %d", $stmt->rowCount());

//get the result
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

//loop through the result
foreach ($result as $row) {
 printf("StudentID: %s Name: %s Branch %s", $row['studentid'], $row['name'], $row['branch']);
}

How to begin a transaction?

To begin a transaction we use beginTransaction() method.

$pdocon->beginTransaction();

How to commit a transaction?

To commit a transaction we use the commit() method.

$pdocon->commit();

How to rollback?

To perform rollback we use the rollback() method.

$pdo->rollback();