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.