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
In the last video, we refactored our index page to pull only 4 random items from the database instead of pulling all items and allowing php to narrow down the list. This can really speed up our response time as our database grows. We never want to pull more information than you need. Let’s apply this same principle to the category pages. Instead of pulling all the items from the database, let’s pull only the items in a specific category.
Example Code
function category_catalog_array($category) {
include("connection.php");
$category = strtolower($category);
try {
$results = $db->prepare(
"SELECT media_id, title, category,img
FROM Media
WHERE LOWER(category) = ?
ORDER BY
REPLACE(
REPLACE(
REPLACE(title,'The ',''),
'An ',
''
),
'A ',
''
)"
);
$results->bindParam(1,$category,PDO::PARAM_STR);
$results->execute();
} catch (Exception $e) {
echo "Unable to retrieved results";
exit;
}
$catalog = $results->fetchAll();
return $catalog;
}
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
In the last video we refactored our
index page to pull only the four random
0:00
items from our database,
instead of pulling all of the items and
0:05
allowing PHP to narrow down the list.
0:08
This can really speed up our
response time as our database grows.
0:12
We never want to pull more
information than you need.
0:16
Let's apply this same principle
to the category pages.
0:20
Instead of pulling all
the items from the database,
0:24
let's pull only the items
in a specific category.
0:27
Back in workspaces, we'll be
working in the functions.php file.
0:30
Let's duplicate our full
catalog array again.
0:35
This time we'll name it
category_catalog_array.
0:42
The first thing we need to do
is filter this by category.
0:51
We're going to be passing
the category variable, so
0:54
let's add that as a parameter.
0:57
Next, we need to change
this to a prepare method.
1:02
This way we can be sure to filter
the category data we are accepting as our
1:09
variable.
1:13
We'll space out these SQL commands so
1:14
that they're easier to read
when we add our new statement.
1:16
And we'll add our where.
1:22
Where category equals our placeholder.
1:25
Next, we'll use our bindParam again.
1:29
We use 1 as the first argument
to specify the placeholder.
1:36
And for the second argument
we use the category variable.
1:40
The category in the database
is stored capitalized.
1:46
And the category we
are passing is lowercase.
1:49
We don't want to check the case at all, so
we'll add two functions just to be safe.
1:52
One in SQL and one in PHP.
1:57
The one in PHP we've seen before.
1:59
String to lower.
2:02
We'll add this to the top of our function
to make sure that our category that we're
2:03
passing in is lowercase.
2:08
The SQL version of this
function is just lower.
2:15
We'll place that around our category.
2:18
Finally, let's specify our data type.
2:24
We'll use PDO::PARAM_STR, for string.
2:28
And now we're ready.
2:35
Let's call this new function from our
catalog page, and test things out.
2:42
We're still going to use the call to
the full catalog array if no category is
2:46
specified, so let's move that catalog
line down below the first conditional.
2:50
Now we'll add a new conditional,
to check our section.
3:00
If (empty($section)), then we want
to use our full catalog array.
3:03
Else, if our section variable is set,
3:14
we're going to use our
category_catalog_array.
3:16
And we'll pass it the section.
3:28
Now we need to adjust our foreach
statement, and simplify it.
3:33
We already have the items we
want in our catalog array, so
3:38
we can remove this call to
our array_category function.
3:41
Then we'll change our foreach loop,
to loop through our catalog.
3:45
We'll name each item, item, and then we'll
pass that item to our get_item_html.
3:49
Great, now let's go back to our
browser and check this out.
3:58
On the Books page, we see just the books.
4:03
On the Movies page, just the movies.
4:07
And on the Music, just the music.
4:11
The only problem is that our items
are no longer sorted alphabetically.
4:14
Our array category
function was doing that.
4:19
So let's add sort in the form
of a SQL order by statement.
4:22
Back in our functions.php, we're going
to add a new SQL command, ORDER BY.
4:26
We want to order by title.
4:37
We were also removing the prepositions a,
an, and the.
4:39
So, let's do that with SQL as well.
4:43
For our purpose,
a simple REPLACE should work.
4:45
REPLACE is case sensitive.
4:49
So, we'll replace any occurrence of the
capitalized 'The ', with an empty string.
4:52
We can then create a nested replace for
a, and an, as well.
4:59
We use the previous replace as
the item upon which to perform
5:05
the next replace operation.
5:08
Nesting can get pretty complicated, so
5:11
make sure you use spaces
to your advantage.
5:13
Let's check this out
once more in the browser.
5:32
Perfect, our categories are all
filtered and sorted alphabetically.
5:36
Since we're no longer using
the function that sorted our catalog,
5:42
let's go add this ORDER BY
to our full catalog array.
5:45
We'll copy this entire ORDER BY
with all three REPLACEs.
5:50
Now let's go back and
check out our full catalog.
6:16
And this too is sorted alphabetically.
6:21
Great, now we're ready to add
our first feature, pagination.
6:24
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