PHP Data Objects (PDO) is the class that connects to the database and allows you to interact with it. This is the popular way to work with databases for PHP developers. PDO allows you to work with different database systems, so you are not tied to MySQL only.
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. Please refer to the previous article on PDO for example and explanation on PDO.
Performing Queries
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 we run the application on our browser, we 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 we
could use any text that we consider.
$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 first time we run the application with this code, the query will be executed successfully, and thus, the result will be the Boolean true. However, if we 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 = <<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.