Learn

Now that we’ve created a database object, $db, we can call its methods to fetch data from the database.

Let’s take a look at an example database. It contains a books table with a list of books with their id, title, author, and published year.

id title author year
1 Don Quixote Miguel de Cervantes 1605
2 Robinson Crusoe Daniel Defoe 1719
3 Pride and Prejudice Jane Austen 1813
4 Emma Jane Austen 1816
5 A Tale of Two Cities Charles Dickens 1859

We’ll begin with a query to fetch book titles. We’ll use the query() method on the $db object to create and execute the query. We can do this by referencing the $db object with the object operator (->) before calling the query() method.

// Create the query $bookQuery = $db->query('SELECT title FROM books');

Does the syntax in parenthesis look familiar? That’s because it’s a regular SQL query that is understood by the database.

Now let’s fetch the result of the query and assign it to the $book variable. We do this by calling the fetch() method on $bookQuery.

// Fetch the first book and assign it to $book $book = $bookQuery->fetch();

Although our SELECT statement above queries the database for all book titles, the fetch() method returns only one result.

To return a list of all book titles, we can use the fetchAll() method instead:

// Fetch all books and assign them to $books $books = $bookQuery->fetchAll();

If we use print_r to look at the value of $book, we’ll see that the data is returned as both an ordered (indexed) and associative array.

Array ( [title] => Don Quixote [0] => Don Quixote )

We can change that by setting a fetch mode, which tells PDO in what format to return our data. To get our book as an associative array, we pass PDO::FETCH_ASSOC as an argument to the fetch() method.

// Fetch the next row and assign the result to $book $book = $bookQuery->fetch(PDO::FETCH_ASSOC);

If we check $book now, we’ll see:

Array ( [title] => Don Quixote )

Instructions

1.

Let’s practice writing some queries!

Create a query to get the id, title, and author and save the query to the $bookQuery variable.

2.

Then fetch one book using the fetch() method with PDO::FETCH_ASSOC mode and assign it to the $book variable.

3.

Next, instead of fetching just one book, return all results by using the fetchAll() method. Assign the result to a new variable called $books.

4.

Next, generate a list of book titles by iterating over the list of $books using a foreach loop and echoing each book’s title followed by the newline character \n.

Take this course for free

Mini Info Outline Icon
By signing up for Codecademy, you agree to Codecademy's Terms of Service & Privacy Policy.

Or sign up using:

Already have an account?