Preview

Start a free Courses trial
to watch this video

Sign up for Treehouse

Getting Specific with Queries

4:38 with Alena Holligan

We've successfully moved our media catalog into a database. All the pages are now working as they did before. However, we have a couple places where our queries are not as efficient as they could be. We have a number of functions that are taking the full catalog array before using PHP to narrow down the items to a smaller list.

Teacher's Notes
Video Transcript
Downloads

functions.php

function random_catalog_array() {
    include("connection.php");

    try {
       $results = $db->query(
         "SELECT media_id, title, category,img 
         FROM Media
         ORDER BY RANDOM()
         LIMIT 4"
       );
    } catch (Exception $e) {
       echo "Unable to retrieved results";
       exit;
    }

    $catalog = $results->fetchAll();
    return $catalog;
}

function get_item_html($item) {
    $output = "<li><a href='details.php?id="
        . $item["media_id"] . "'><img src='" 
        . $item["img"] . "' alt='" 
        . $item["title"] . "' />" 
        . "<p>View Details</p>"
        . "</a></li>";
    return $output;
}

index.php

$random = random_catalog_array();
foreach ($random as $item) {
    echo get_item_html($item);
}

RANDOM Functions in Popular Databases

SQLite	RANDOM()
MySQL	RAND()
PostgreSQL	RANDOM()
SQL Server	RAND()
Oracle	DBMS_RANDOM.RANDOM

We've successfully moved our media catalog into a database. 0:01

All the pages are now working as they did before. 0:08

However, we have a couple places where queries are not 0:11

as efficient as they could be. 0:15

We have a number of functions that are taking the full catalog array 0:17

before using PHP to narrow down the items to a smaller list. 0:20

Instead, we should modify these functions to use their own queries to retrieve 0:25

only the items we need in the first place. 0:30

For example, retrieve just the books or just the movies. 0:33

As we do this, 0:38

we'll look at a number of different ways to use sequel specific features. 0:39

Like limits, ordering, and aggregate functions to fine tune our queries. 0:43

Let's start with using sequel to grab the four random items for our home page. 0:49

>> Launch the workspace associated with this video and open index.php. 0:54

We're only displaying four random items on the home page. 0:58

But we pulled the entire catalog from the database before using PHP 1:03

to select the random items. 1:07

Instead let's create a new function that uses a SQL query to pull for 1:09

random items directly from the database. 1:14

Let's open the functions.php file from within the includes folder. 1:16

We'll start by duplicating the full catalog array. 1:20

Then we'll rename it random catalog array. 1:27

This function is going to work exactly the same way as our full catalog array, 1:34

with two small changes to the SQL query. 1:38

We include our database connection, query our database, fetch all results, 1:41

then return those results back to our function call. 1:46

Let's break this query line up by statement types. 1:50

This will make it a little easier to read as we add more statements. 2:00

We'll start by adding an ORDER BY. 2:03

Then we'll use the SQLite function, RANDOM, just like in php 2:08

when using a function, make sure you add the opening and closing parenthesis. 2:12

Then we'll add a limit and tell it 4. 2:17

Now we're only selecting four random items. 2:19

If you're following along using a local environment with my sequel, 2:25

the function is called random. 2:28

If you're using another database, make sure you look up the specific syntax for 2:31

your database. 2:35

Now we need to use this function on the index page. 2:36

We'll save this file and go back into index.php. 2:40

We can remove this catalog. 2:43

That calls our full catalog array, since we're no longer using our full catalog. 2:46

Then we go down to where we called the built in function array_rand. 2:51

Will change this to our new random catalog. 2:56

We're selecting the information from our database within our new function. 3:02

So we don't need to pass any arguments. 3:06

If you want to take this as step further, you could pass the number of random items. 3:09

Make sure that you use a prepared statement when accepting 3:13

outside information. 3:15

Our random variable now contains a full array of items, instead of just the ids. 3:17

So let's change id to item in this foreach loop. 3:22

We've already changed or get_item_html to use the media id instead of the array key, 3:28

so let's modify this function to just pass the single variable Item. 3:33

We also need to update our function so it only receives one argument. 3:41

Go back to functions, and will scroll down to our get item HTML. 3:46

We'll remove the ID and just keep the item. 3:53

Let's take a quick peek in the browser to make sure our home page is still working. 3:58

We refresh and the four random items change, but if we try to go to the catalog 4:03

page, we see that our changes to the get HTML function broke our catalog pages. 4:08

So let's fix those. 4:14

Open catalog.php. 4:15

Scroll down. 4:19

And where we're using the get_item_html, 4:21

we're going to pass just the item and not the ID. 4:25

Now we go back to our browser and refresh our page. 4:29

Great, our catalog pages are working again. 4:34

You need to sign up for Treehouse in order to download course files.

Sign up