Connecting to a Database

PHP offers a few extensions to connect to various databases. The two most common are the following:

• MySQLi (MySQL Improved):
  • Supports MySQL databases.
  • Offers both a procedural and object-oriented interface.
  • Features prepared statements, multiple statements, and transactions.
• PDO (PHP Data Objects):
  • Database-agnostic, supporting multiple database systems.
  • Object-oriented.
  • Supports prepared statements, transactions, and custom data types.

Many PHP developers prefer using PDO for several reasons. PDO is database-agnostic, meaning it can connect to various database systems with minimal changes in code. This flexibility allows developers to switch between databases more effortlessly. It's also object-oriented, making it easier to use and integrate into modern PHP applications that follow OOP principles.

With PDO, functions, and methods are consistent regardless of the database being used. This uniformity simplifies the learning curve for developers. As the PHP language evolves, PDO tends to get updates and improvements, ensuring it remains compatible and feature-rich.

While PDO has many advantages, the choice between PDO, MySQLi, and other extensions ultimately depends on the specific requirements of the project, developer familiarity, and personal preference. However, given its broad range of features and flexibility, PDO is a compelling choice for many PHP developers. For this book, we'll be using PDO.

DSN (Data Source Name)

The first step to using PDO is to connect to the database with a DSN. A DSN, or Data Source Name, is a string that provides the information needed to connect to a database. It specifies the type of database, location, and other relevant attributes.

A DSN is typically made up of the following:

• PDO Driver: The name of the database driver to use. For instance, mysql for MySQL databases, pgsql for PostgreSQL, sqlite for SQLite, etc.
• Host: The hostname or IP address of the database server. This is usually specified using host=hostname.
• Database Name: The name of the database to which the connection should be made. This is often given using dbname=databasename.
• Port (Optional): If the database is running on a non-default port, this port number can be specified using port=portnumber.
• Charset (Optional): Specifies the character set to be used, for instance charset=utf8.
• Additional Driver-specific parameters (Optional): Some drivers may have additional parameters that can be set as part of the DSN.

The driver always appears first in a DSN. After the driver, we must add a colon (:) followed by name-value pairs separated with a semicolon (;).

Here's an example DSN for a MySQL database:

$driver = 'mysql';
$host = 'localhost';
$dbname = 'example';
$charset = 'utf8mb4';
$port = '3306'; // This is the default port for MySQL. It's optional if you're using the default.
 
// Construct the DSN string using the variables
$dsn = "{$driver}:host={$host};dbname={$dbname};charset={$charset};port={$port}";

In the example above, breaking the DSN into component variables can be helpful in scenarios where these values might change dynamically or be fetched from a configuration, making the code more modular and easier to manage.

It's important to note that the actual structure and components of the DSN can vary depending on the PDO driver in use. Always refer to the specific PDO driver's documentation to understand the correct format and available parameters.

Enable PDO_MyQL Driver

Before using PDO, you will want to enable the driver for the database you plan on using. PDO has various drivers. On many systems, the PDO MySQL driver comes pre-installed.

1. Locate your php.ini file (its location varies depending on the server setup and the OS).
2. Find the line ;extension=pdo_mysql or ;extension=php_pdo_mysql.dll (the exact line might vary based on your OS and PHP version).
3. Uncomment the line by removing the semicolon (;) at the beginning.
4. Save the php.ini file.
5. For the changes to take effect, you'll need to restart your web server.

You can verify the driver was enabled by creating a simple PHP script to check the enabled PDO drivers:

print_r(PDO::getAvailableDrivers());

When you run the script, you should see mysql in the list of available drivers if everything was set up correctly.

Remember, the exact steps and commands might vary depending on your operating system, PHP version, and server setup. Always refer to the official documentation or server instructions relevant to your environment.

PDO Object

Now that we've prepared the DSN string, we can use it to connect to our database by creating an instance of the PDO class.

The PDO class is a core part of the PHP Data Objects (PDO) database access layer. It represents a connection between PHP and a database server and provides methods to execute queries and fetch results.

Establish a connection to a database using the PDO class constructor. You'll typically need a Data Source Name (DSN) string, a username, and a password.

After you know the username and password, you can update the variables and then pass them into the new instance of the PDO class like so:

$user = 'username';
$password = 'password';
 
$pdo = new PDO($dsn, $user, $password);

Parameters

• DSN (Data Source Name): It's a string specifying the type of database, host, and other driver-specific connection parameters.
• Username: The username to use for the connection.
• Password: The password to use for the connection.
• Options (Optional): An associative array of driver-specific connection options. Common options include:
  • PDO::ATTR_ERRMODE: Error reporting level.
  • PDO::ATTR_DEFAULT_FETCH_MODE: Default fetch style.
  • PDO::ATTR_EMULATE_PREPARES: Enables or disables the emulation of prepared statements.

Handling Errors

One of the most common mistakes beginner developers make is not catching errors thrown by the PDO class. Let's say your database connection details are 100% correct. Despite that, it's possible for PHP to be unable to connect to your database. This can be because your database is temporarily down. If that happens, PHP outputs your database's credentials for debugging.

Exposing raw database errors might reveal internal information about the system's structure, potentially making it easier for attackers to exploit vulnerabilities. By catching these errors, you can log them internally while showing a generic message to the user.

For this reason, it's considered good practice to use try-catch blocks to prevent this information from being displayed.

$driver = 'mysql';
$host = 'localhost';
$dbname = 'example';
$charset = 'utf8mb4';
$port = '3306';
 
$dsn = "{$driver}:host={$host};dbname={$dbname};charset={$charset};port={$port}";
$user = 'username';
$password = 'password';
 
try {
    $pdo = new PDO($dsn, $user, $password);
    echo "Connected successfully!";
} catch (PDOException $e) {
  // Log the error message for internal tracking
  error_log($e->getMessage());
 
  // Display a generic message to the user
  echo "Sorry, we're experiencing connection issues. Please try again later.";
}

In the example above, if a connection error occurs, the detailed error message ($e->getMessage()) is logged internally using error_log(), but the user sees only a generic message. This ensures both graceful error handling and security.

Troubleshooting

Here are some common errors you may run into when connecting to a database.

If the MySQL driver is not enabled in the php.ini file, you will get the error message:

could not find driver

Invalid passwords produce the following message:

SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: YES)

Invalid database names or databases that don't exist produce the following message:

SQLSTATE[HY000] [1049] Unknown database 'example'

Invalid database hostnames produce the following message:

SQLSTATE[HY000] [2002] php_network_getaddresses: getaddrinfo failed: No such host is known.

Key Takeaways

• PDO (PHP Data Objects) is a database access layer in PHP. It provides a consistent interface for connecting to various databases.
• A DSN is a string that provides connection details to the database. Specifies database type, host, name, and other attributes.
• A connection is established using the PDO class constructor with DSN, username, and password.
• It's recommended to use a try-catch block for graceful error handling during connection.
• Ensure the relevant PDO driver is enabled in the php.ini file. Restart the web server after modifying the configuration.