Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
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);
}
RANDOM Functions in Popular Databases
| 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 upRelated 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 upYou need to sign up for Treehouse in order to set up Workspace
Sign up