Filtering Data by Category

6:28 with Alena Holligan

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.

Teacher's Notes
Video Transcript
Downloads

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;
}

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

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

Integrating PHP with Databases

Filtering Data by Category

Example Code

Sign in