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

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.

Integrating PHP with Databases

Getting Specific with Queries

functions.php

index.php

RANDOM Functions in Popular Databases

Sign in