PHP Data Objects (PDO) Clearly Explained
Reading Time:
Reading Time:
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.
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();
}
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');
Data from the executes SQL statement through “query” method can be got through the PDOStatment objects methods.
// 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 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,
// 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();