Connecting to a PHP Database Program

To establish a connection to a database program, create a new PDO object. You pass the PDO constructor a string that describes the database you are connecting to, and it returns an object that you use in the rest of your program to exchange information with the database program.

Example 8-1 shows a call to new PDO() that connects to a database named restaurant in a MySQL server running on db.exanple.com, using the username penguin and the password top^hat.

Example 8-1. Connecting with a PDO object

$db = new PDO(‘mysql:host=db.example.com;dbname=restaurant’,’penguin’,’top^hat’);

The string passed as the first argument to the PDO constructor is called a data source name (DSN). It begins with a prefix indicating what kind of database program to con­nect to, then has a :, then some semicolon-separated key=value pairs providing information about how to connect. If the database connection needs a username and password, these are passed as the second and third arguments to the PDO constructor.

The particular key=value pairs you can put in a DSN depend on what kind of data­base program you’re connecting to. Although the PHP engine has the capability to connect to many different databases with PDO, that connectivity has to be enabled when the engine is built and installed on your server. If you get a could not find driver message when creating a PDO object, it means that your PHP engine installa­tion does not incorporate support for the database you’re trying to use.

Table 8-1 lists the DSN prefixes and options for some of the most popular database programs that work with PDO.

 

The host and port DSN options, as seen in Example 8-1, specify the host and net­work port of the database server. The charset option, available with some database programs, specifies how the database program should handle non-English characters. The user and password options for PostgreSQL and the UID and PWD options for ODBC provide a way to put the connection username and password in the DSN string. If they are used, their values override any username or password passed as additional arguments to the PDO constructor.

If all goes well with new PDO(), it returns an object that you use to interact with the database. If there is a problem connecting, it throws a PDOException exception. Make sure to catch exceptions that could be thrown from the PDO constructor so you can verify that the connection succeeded before going forward in your program. Example 8-2 shows how to do this.

Example 8-2. Catching connection errors

try {

$db = new PDO(‘mysql:host=localhost;dbname=restaurant’,’penguin’,’top^hat’);

// Do some stuff with $db here

} catch (PDOException $e) {

print “Couldn’t connect to the database: ” . $e->getMessage();

}

In Example 8-2, if the PDO constructor throws an exception, then any code inside the try block after the call to new PDO() doesn’t execute. Instead, the PHP engine jumps ahead to the catch block, where an error is displayed.

For example, if top^Ahat is the wrong password for user penguin, Example 8-2 prints something like:

Couldn’t connect to the database: SQLSTATE[HY000] [1045] Access denied

for user ‘penguin’@’client.example.com’

(using password: YES)

Source: Sklar David (2016), Learning PHP: A Gentle Introduction to the Web’s Most Popular Language, O’Reilly Media; 1st edition.