In order to connect to the database, it is good practice to keep the credentials—that is, the user and password—separated from the code in a configuration file. We already have this file as config/app.json from when we worked with the Config class. Let's add the correct credentials for our database. If you have the configuration by default, the configuration file should look similar to this:

{
  "db": {
    "user": "root",
    "password": ""
  }
}

Developers usually specify other information related to the connection, such as the host, port, or name of the database. This will depend on how your application is installed, whether MySQL is running on a different server, and so on, and it is up to you how much information you want to keep on your code and in your configuration files.

In order to connect to the database, we need to instantiate an object from the PDO class. The constructor of this class expects three arguments: Data Source Name (DSN), which is a string that represents the type of database to use; the name of the user; and the password. We already have the username and password from the Config class, but we still need to build DSN.

One of the formats for MySQL databases is <database type>:host=<host>;dbname=<schema name>. As our database system is MySQL, it runs on the same server, and the schema name is bookstore, DSN will be mysql:host=127.0.0.1;dbname=bookstore. Let's take a look at how we will put everything together:

$dbConfig = Config::getInstance()->get('db');
$db = new PDO(
    'mysql:host=127.0.0.1;dbname=bookstore',
    $dbConfig['user'],
    $dbConfig['password']
);
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

Note also that we will invoke the setAttribute method from the PDO instance. This method allows you to set some options to the connection; in this case, it sets the format of the results coming from MySQL. This option forces MySQL to return the arrays whose keys are the names of the fields, which is way more useful than the default one, returning numeric keys based on the order of the fields. Setting this option now will affect all the queries performed with the $db instance, rather than setting the option each time we perform a query.

The easiest way to retrieve data from your database is to use the query method. This method accepts the query as a string and returns a list of rows as arrays. Let's consider an example: write the following after the initialization of the database connection—for example, in the init.php file:

$rows = $db->query('SELECT * FROM book ORDER BY title');
foreach ($rows as $row) {
    var_dump($row);
}

This query tries to get all the books in the database, ordering them by the title. This could be the content of a function such as getAllBooks, which is used when we display our catalog. Each row is an array that contains all the fields as keys and the data as values.

If you run the application on your browser, you will get the following result:

The query function is useful when we want to retrieve data, but in order to execute queries that insert rows, PDO provides the exec function. This function also expects the first parameter as a string, defining the query to execute, but it returns a Boolean specifying whether the execution was successful or not. A good example would be to try to insert books. Type the following:

$query = <<<SQL
INSERT INTO book (isbn, title, author, price)
VALUES ("9788187981954", "Peter Pan", "J. M. Barrie", 2.34)
SQL;
$result = $db->exec($query);
var_dump($result); // true

This code also uses a new way of representing strings: heredoc. We will enclose the string between <<<SQL and SQL;, both in different lines, instead of quotes. The benefit of this is the ability to write strings in multiple lines with tabulations or any other blank space, and PHP will respect it. We can construct queries that are easy to read rather than writing them on a single line or having to concatenate the different strings. Note that SQL is a token to represent the start and end of the string, but you could use any text that you consider.

The first time you run the application with this code, the query will be executed successfully, and thus, the result will be the Boolean true. However, if you run it again, it will return false as the ISBN that we inserted is the same but we set its restriction to be unique.

It is useful to know that a query failed, but it is better if we know why. The PDO instance has the errorInfo method that returns an array with the information of the last error. The key 2 contains the description, so it is probably the one that we will use more often. Update the previous code with the following:

$query = <<<SQL
INSERT INTO book (isbn, title, author, price)
VALUES ("9788187981954", "Peter Pan", "J. M. Barrie", 2.34)
SQL;
$result = $db->exec($query); 
var_dump($result); // false
$error = $db->errorInfo()[2];
var_dump($error); // Duplicate entry '9788187981954' for key 'isbn'

The result is that the query failed because the ISBN entry was duplicated. Now, we can build more meaningful error messages for our customers or just for debugging purposes.

The previous two functions are very useful when you need to run quick queries that are always the same. However, in the second example you might note that the string of the query is not very useful as it always inserts the same book. Although it is true that you could just replace the values by variables, it is not good practice as these variables usually come from the user side and can contain malicious code. It is always better to first sanitize these values.

PDO provides the ability to prepare a statement—that is, a query that is parameterized. You can specify parameters for the fields that will change in the query and then assign values to these parameters. Let's consider first an example, as follows:

$query = 'SELECT * FROM book WHERE author = :author';
$statement = $db->prepare($query);
$statement->bindValue('author', 'George Orwell');
$statement->execute();
$rows = $statement->fetchAll();
var_dump($rows);

The query is a normal one except that it has :author instead of the string of the author that we want to find. This is a parameter, and we will identify them using the prefix :. The prepare method gets the query as an argument and returns a PDOStatement instance. This class contains several methods to bind values, execute statements, fetch results, and more. In this piece of code, we use only three of them, as follows:

If you try this code, you will note that the result is very similar to when using query; however, this time, the code is much more dynamic as you can reuse it for any author that you need.

There is another way to bind values to parameters of a query than using the bindValue method. You could prepare an array where the key is the name of the parameter and the value is the value you want to assign to it, and then you can send it as the first argument of the execute method. This way is quite useful as usually you already have this array prepared and do not need to call bindValue several times with its content. Add this code in order to test it:

$query = <<<SQL
INSERT INTO book (isbn, title, author, price)
VALUES (:isbn, :title, :author, :price)
SQL;
$statement = $db->prepare($query);
$params = [
    'isbn' => '9781412108614',
    'title' => 'Iliad',
    'author' => 'Homer',
    'price' => 9.25
];
$statement->execute($params);
echo $db->lastInsertId(); // 8

In this last example, we created a new book with almost all the parameters, but we did not specify the ID, which is the desired behavior as we want MySQL to choose a valid one for us. However, what happens if you want to know the ID of the inserted row? Well, you could query MySQL for the book with the same ISBN and the returned row would contain the ID, but this seems like a lot of work. Instead, PDO has the lastInsertId method, which returns the last ID inserted by a primary key, saving us from one extra query.