PHP Data Objects (PDO) Clearly Explained

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();