This course will be retired on June 1, 2025.

Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

Integrating PHP with Databases

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.

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

Related Discussions

Have questions about this video? Start a discussion with the community and Treehouse staff.

Sign up

    Related Discussions

    Have questions about this video? Start a discussion with the community and Treehouse staff.

    Sign up

    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

      You need to sign up for Treehouse in order to set up Workspace

      Sign up