Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.

Preview

Start a free Basic 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.

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);
}
SQLite RANDOM()
MySQL RAND()
PostgreSQL RANDOM()
SQL Server RAND()
Oracle DBMS_RANDOM.RANDOM
  • 0:01

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

  • 0:08

    All the pages are now working as they did before.

  • 0:11

    However, we have a couple places where queries are not

  • 0:15

    as efficient as they could be.

  • 0:17

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

  • 0:20

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

  • 0:25

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

  • 0:30

    only the items we need in the first place.

  • 0:33

    For example, retrieve just the books or just the movies.

  • 0:38

    As we do this,

  • 0:39

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

  • 0:43

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

  • 0:49

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

  • 0:54

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

  • 0:58

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

  • 1:03

    But we pulled the entire catalog from the database before using PHP

  • 1:07

    to select the random items.

  • 1:09

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

  • 1:14

    random items directly from the database.

  • 1:16

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

  • 1:20

    We'll start by duplicating the full catalog array.

  • 1:27

    Then we'll rename it random catalog array.

  • 1:34

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

  • 1:38

    with two small changes to the SQL query.

  • 1:41

    We include our database connection, query our database, fetch all results,

  • 1:46

    then return those results back to our function call.

  • 1:50

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

  • 2:00

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

  • 2:03

    We'll start by adding an ORDER BY.

  • 2:08

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

  • 2:12

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

  • 2:17

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

  • 2:19

    Now we're only selecting four random items.

  • 2:25

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

  • 2:28

    the function is called random.

  • 2:31

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

  • 2:35

    your database.

  • 2:36

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

  • 2:40

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

  • 2:43

    We can remove this catalog.

  • 2:46

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

  • 2:51

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

  • 2:56

    Will change this to our new random catalog.

  • 3:02

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

  • 3:06

    So we don't need to pass any arguments.

  • 3:09

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

  • 3:13

    Make sure that you use a prepared statement when accepting

  • 3:15

    outside information.

  • 3:17

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

  • 3:22

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

  • 3:28

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

  • 3:33

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

  • 3:41

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

  • 3:46

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

  • 3:53

    We'll remove the ID and just keep the item.

  • 3:58

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

  • 4:03

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

  • 4:08

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

  • 4:14

    So let's fix those.

  • 4:15

    Open catalog.php.

  • 4:19

    Scroll down.

  • 4:21

    And where we're using the get_item_html,

  • 4:25

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

  • 4:29

    Now we go back to our browser and refresh our page.

  • 4:34

    Great, our catalog pages are working again.

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

Sign up