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 have all the variables set for our pagination, so let’s start adding our limits. We are going to be limiting the number of products we show on a page, so we want to add limits to our catalog calls. This will tell the database to only return a limited number of items. Again, reducing the amount of data the page has to handle.
Example Code
function category_catalog_array($category, $limit = null, $offset = 0) {
include("connection.php");
$category = strtolower($category);
try {
$sql = "SELECT media_id, title, category,img
FROM Media
WHERE LOWER(category) = ?
ORDER BY
REPLACE(
REPLACE(
REPLACE(title,'The ',''),
'An ',
''
),
'A ',
''
)";
if (is_integer($limit)) {
$results = $db->prepare($sql . " LIMIT ? OFFSET ?");
$results->bindParam(1,$category,PDO::PARAM_STR);
$results->bindParam(2,$limit,PDO::PARAM_INT);
$results->bindParam(3,$offset,PDO::PARAM_INT);
} else {
$results = $db->prepare($sql);
$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
We have all the variables set for
pagination.
0:00
So let's start adding our limits.
0:03
We're going to be limiting the number
of items we show on each page.
0:05
So we want to add the limits
to our catalog calls.
0:09
This will tell the database to only
return a limited number of items.
0:12
Again, reducing the amount of
data the page has to handle.
0:17
Let's go into our functions page.
0:22
In our catalog array we'll need to
pass some optional arguments again.
0:25
We'll do this with limit = null and
0:29
offset = 0.
0:33
Remember by giving
a parameter a default value
0:37
we do not require that
the argument be passed.
0:40
If we were to call the function full
catalog array without passing these
0:44
arguments, we would not have a limit and
our offset would be set to 0.
0:48
By setting the offset default to 0, we
can then pass a limit without an offset.
0:53
And it would start at the very first item.
0:58
If we don't have a limit, we should
return all the items in our catalogue.
1:01
We'll need to add these
limits to our SQL statement.
1:05
So let's move our current
query into a SQL variable.
1:08
We'll then change our method
to prepare and add an execute.
1:20
We haven't changed any functionality yet,
but now we can use a conditional and
1:30
add our limits.
1:34
We could use MP again here, but
let's be a little more specific.
1:35
We'll use a new built in function
called is_, And we'll pass our limit.
1:40
We're going to add a limit,
so let's duplicate this line.
1:47
We use this line in our else-statement.
1:52
And we'll leave our execute outside.
1:58
Let's go back up and
at our limit with placeholders.
2:01
Limit tells us how many items we want to
return and offset tells us where to start.
2:11
We'll use our PDO::PARAM_INT to filter for
an integer.
2:29
And the same thing for our offset.
2:50
We need to make these changes to our
category catalog array function as well.
2:53
We'll start by adding our optional
parameters, limit and offset.
2:57
Again, we'll move our SQL
statement out of our prepare.
3:07
And then we can add to it.
3:26
Let's copy the if statement from
our full catalog and paste it here.
3:27
We need to change our bind params
a little because this statement already
3:42
has a parameter.
3:45
We'll copy this line for 1.
3:51
And then we'll change these to 2 and 3.
3:55
We close out our else before the execute,
so both of these will get executed.
3:58
Great, our functions are now set up.
4:02
So we need to pass in the limit and
the offset.
4:05
Let's go back to the catalog.php page.
4:07
Let's scroll down to where we're
sending our catalog variables.
4:10
We'll pass in the items per page
which is our limit, and our offset.
4:16
Will also add this to our
category catalog array.
4:23
Now, let's go back to our browser and
refresh our full catalog page.
4:38
Perfect.
4:45
Well except for
4:47
the fact that we can't see any more
than eight items yet, let's fix that.
4:48
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