Connecting to Databases
PHP and Databases
PHP is often combined with a database system so that a website or app can store and manipulate data.
PHP Data Objects
PHP Data Objects (PDO) provides an abstraction layer for communicating with many types of database systems using a common set of functions.
PDO Database Connections
PDO can connect to a database server hosted locally using an IP address or hostname. It can also connect to a remote database server using an IP address or domain name.
PDO Database Object
A database object is created using the new keyword to invoke the PDO class’s constructor.
$db = new PDO();
PDO Database Connection
A connection to a database is initiated by passing a DSN, username, and password, in that order, as arguments to the new PDO() call.
$db = new PDO($dsn, $username, $password);
Data Source Name
A Data Source Name (DSN) is written with a database driver, then a colon, followed by a hostname and a database name in property=value format separated by a semi-colon.
$hostname = 'cutekittens.amazonaws.com';$dbname = 'kittens';$dsn = "pgsql:host=$hostname;dbname=$dbname";
Terminating a Database Connection
To terminate a database connection, set the database object and any references to it, to null.
$db = null;
The fetch() Method
To fetch one result from the database, create the query using the database object’s query() method, then call the query object’s fetch() method.
// Create the query$kittenQuery = $db->query('SELECT * FROM kittens');// Execute the query, and assign the result to $kitten$kitten = $kittenQuery->fetch();
The fetchAll() Method
To fetch all matching results from the database, create a query using the database object’s query() method, then call the query object’s fetchAll() method.
// Create the query$kittensQuery = $db->query('SELECT * FROM kittens');// Execute the query and assign the result to $kittens$kittens = $kittensQuery->fetchAll();
Fetch Modes
A fetch mode can be provided to the fetch() and fetchAll() methods to control how data is returned.
Default Fetch Mode
If no mode is passed to fetch() or fetchAll(), the mode defaults to PDO::BOTH, which returns an array containing both column names and numbers as keys.
The FETCH_ASSOC Mode
The PDO::FETCH_ASSOC mode is passed as an argument to the fetch() and fetchAll() functions to return an associative array containing column names as keys.
// Create the query$kittensQuery = $db->query('SELECT * FROM kittens');// Execute the query, and assign the result to $kittens$kittens = $kittenQuery->fetchAll(PDO::FETCH_ASSOC);
Dangerous Queries
SQL statements that take user-defined variables are vulnerable to SQL injection.
// WARNING: Don't do this!$kittenQuery = $db->query('SELECT * FROM kittens WHERE id = ' . $id);
Prepared Statements
Prepared statements offer protection against SQL injection by separating data from SQL commands.
Using Prepared Statements
Use a prepared statement to safely include information sent by an end-user in a SQL statement.
// Set $name to value sent by user$name = $_POST['name'];// Create query using ':name' placeholder$kittenQuery = $db->prepare('SELECT * FROM kittens WHERE name = :name');// Bind placeholder to variable and execute query$kittenQuery->execute(['name' => $name]);// Fetch result and assign it to $kitten$kitten = $kittenQuery->fetch(PDO::FETCH_ASSOC);
Exception Handling
Exception handling is used to rescue an application that has encountered an unexpected error by providing alternative instructions.
Exception Handling Blocks
Exceptions in PHP can be handled using try/catch blocks.
try {// Try running this code first}catch (Exception $e) {// Run this code if there's an exception}
Exception Handling Flow
The PHP interpreter first attempts to execute code in the try block. If it encounters an exception, it stops and begins executing code in the catch block instead.
try {// Try running this code first}catch (Exception $e) {// Run this code if there's an exception}
Exception Subclasses
The catch block can take the exception class (Exception) as a parameter or a subclass of the exception class, such as a more specific exception type.
try {// Run checkout code} catch (CheckoutException $e) {// Handle checkout exception with a custom CheckoutException class}
