PHP Data Objects (PDO) Clearly Explained

PHP Data Objects (PDO) is a lightweight interface for accessing multiple databases with a single interface. PDO does not use database specific extensions, instead you must specify the database driver while constructing the PDO object. PDO is a Abstract Layer for all the database, which mean for all the databases you use the same PDO interface like Querying and Fetching data.

Database Connection With PDO

PDO can be user to write code very efficiently and it also works faster. To Connect to a database you need to create a new PDO instance. You should provide the following values for constructing the PDO instance. The PDO constructor must be wrapped around the try catch block.

  • Database Source also know as (DSN). This is a string that specifies the database technology, the host and database name. e.g - 'mysql:host=localhost;dbname=testing'
  • The username with which the database to be connected
  • The password for the username

Note: PDO throws an PDOException. If you do not catch this exception the script will be terminated.

try {
    $db = new PDO('mysql:host=localhost;dbname=test', $username, $password);
} catch (PDOException $ex) {
  echo 'Error connecting to database: ' . $ex->getMessage();
}

Querying With PDO

With PDO we can execute the query using the "query" method of the PDO class. This function executes a query and returns a PDOStatement object or false when the execution fails. PDOStatment object is used to retrieve data from the executed query.

// Execute the sql command and store the returned PDOStatement object
$statmentObj = $db->query('SELECT name, email FROM users');

Fetching Data With PDO

Data from the executes SQL statement through "query" method can be got through the PDOStatment objects methods.

  • fetch - Retrieves a single row at a time
  • fetchAll - Retrieves all the row at once. Use this with LIMIT clause to fetch minimal data

// Loop through all the available rows
// Pass the pdo constant FETCH_ASSOC to return the associative array.
while ($row = $statmentObj->fetch(PDO::FETCH_ASSOC)) {
  echo "Name: {$row['name']}, email: {$row['email']}";
}

Prepared Statements With PDO

Prepared statements are used to execute a query multiple times with various parameters. Once you prepared a query the prepared query is stored in the memory. You can pass various parameters each time and you can execute the same query. Prepared statements prevent SQL Injection.

Note: Though PDO prevents SQL Injection it does not escape you data. You must escape the data explicitly.

For executing a statement using prepared statements,

  • Prepared the SQL Query with PDO "prepare" method
  • Bind the Parameters using the PDOStatmenets "bindParam" method
  • For fetching the data, you can either get a PDOResult statement object or use "bindValue" or "bindColumn"

// Preparing the query. User placeholders like ":columnName" or "?"
$stmtObj = $db->prepare('INSERT INTO users(name, email) VALUES (:name, :email);

$name = 'John';
$email = 'John@example.com';

// Binding the parameters
$stmtObj->bindParam(':name', $name, PDO::PARAM_STR);
$stmtObj->bindParam(':email', $email, PDO::PARAM_STR);

$stmtObj->execute();

// Executing multiple times with diff vars with the same prepared statement

$name2 = 'Scott';
$email2 = 'scott@example.com';

$stmtObj->bindParam(':name', $name2, PDO::PARAM_STR);
$stmtObj->bindParam(':email', $email2, PDO::PARAM_STR);

$stmtObj->execute();

// finally after inserting the rows close the cursor
$stmtObj->closeCursor();