Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Well done!
You have completed Integrating PHP with Databases!
You have completed Integrating PHP with Databases!
Preview
Pagination allows us to split our results across multiple pages, making our results easier to read. In this way, we give the data to our visitors in bite size chunks instead of overwhelming them with items.
Example Code
catalog.php
$items_per_page = 8;
if (isset($_GET["pg"])) {
$current_page = filter_input(INPUT_GET,"pg",FILTER_SANITIZE_NUMBER_INT);
}
if (empty($current_page)) {
$current_page = 1;
}
$total_items = get_catalog_count($section);
functions.php
function get_catalog_count($category = null) {
$category = strtolower($category);
include("connection.php");
try {
$sql = "SELECT COUNT(media_id) FROM Media";
if (!empty($category)) {
$result = $db->prepare(
$sql
. " WHERE LOWER(category) = ?"
);
$result->bindParam(1,$category,PDO::PARAM_STR);
} else {
$result = $db->prepare($sql);
}
$result->execute();
} catch (Exception $e) {
echo "bad query";
}
$count = $result->fetchColumn(0);
return $count;
}
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
Now that we have our data
stored in the database and
0:00
our catalog page pulling only the
specified categories from the database and
0:03
sorting the results in alphabetical order,
it's time to add pagination.
0:08
Pagination allows us to split our
results across multiple pages,
0:14
making our results easier to read.
0:18
In this way, we give the data to
our visitors in bite sized chunks.
0:21
Instead of overwhelming them with items.
0:25
The 36 items in our current
database may not be overwhelming.
0:27
But imagine hundreds or
even thousands of items.
0:32
Although the prospect of so
many items can be exciting, It can also be
0:35
daunting to wade through all those items
to find the one you're interested in.
0:40
pagination worked extremely well.
0:45
When teamed with the search
feature we'll be adding next.
0:48
There are three things we need to
know to add pagination to this site.
0:51
First, we need to know how
many items there are total.
0:56
This helps us figure out how
many pages of items we'll have.
1:00
In addition, we need to know how many
items we want to display per page.
1:04
Finally, as the visitor moves
through the pages of the item,
1:10
we need to keep track of
the page the visitor is viewing.
1:13
For example, are they on the first of
ten pages, or the fifth of five pages?
1:17
Let's start by defining
the number of items to display.
1:23
Launch the workspace associated with
this video, and open catalog dot .php.
1:27
The number of items per page is something
that we're going to define, once.
1:31
So I'm going to put that
right up at the top,
1:37
with the rest of our initial variables.
1:39
We'll set our items_per_page
to equal eight.
1:50
Next we're going to be passing
a page number using a GET variable.
1:52
So let's add a second conditional, for
1:57
checking GET, right after our first
conditional that checks our category.
1:59
If is set get,
2:03
and we'll just call this; P.G. for page.
2:09
If this is set then we want to add
a new variable named current page.
2:16
Again, we want to make
sure to filter our input,
2:25
so we'll use the filter_input.
2:30
The input type is (INPUT_GET)
2:34
the name is pg and for the filter.
2:39
Since this should be a page number,
we'll use filter,
2:46
sanitize, number, int.
2:51
If for any reason our current page is not
set, we always want to set it to one.
2:58
So we use another if statement.
3:05
[SOUND]
3:06
The last thing we need to know up front
is how many items there are total.
3:19
So let's create a new function
that queries the database and
3:23
just returns a count.
3:26
Let's go back into our functions.php file.
3:28
We'll add this new function
right up at the top.
3:32
When we make get_catalog_count.
3:38
We may be on a category page.
3:43
We may also be on the full catalog page.
3:45
So we want the category
parameter to be optional.
3:48
To specify a parameter as
optional we give it a default
3:52
when setting it up in the function.
3:55
So instead of just using category,
we use category=null.
3:57
So if we don't pass in a category,
the category variable will be set to null.
4:07
First we need to include
our connection file.
4:13
We'll start with the SQL variable for
our main SELECT.
4:26
We're going to use another SQL function,
= count.
4:32
We're going to count.
4:37
This would count all
the items in our database.
4:45
Now let's put those results
in a PDO statement object.
4:48
Next, we'll add our conditional for
the category.
5:07
So before our prepare.
5:10
We'll start our conditional.
5:12
if (!empty($category)).
5:14
We're going to duplicate
this prepare statement.
5:22
Let's break this up on a few lines.
5:34
Great that's easier to read.
5:41
Then we'll add or bind.
5:43
We'll use 1, our category and
then our filter.
5:51
We want to make sure that our
category is always lowercase.
6:05
So let's add that to
the top of our function.
6:08
Now we're ready to write the else for
the second prepare state.
6:22
We're leaving the execute outside of
the if statement because we want to run it
6:33
either way.
6:37
Finally we'll grab the count.
6:55
Since we just want to
get a single column and
6:57
assign it to a count variable,
we'll use a new fetch method.
6:59
Fetch column.
7:04
We specify zero for the first column.
7:09
And now we can return our count.
7:13
Our function is ready to use.
7:18
So let's go back to the catalog.php file.
7:20
After the current page
let's add the total items.
7:23
If the section variable is not
set it will pass null as well.
7:35
So it's safe to pass the section
variable no matter what.
7:39
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