Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

Integrating PHP with Databases

Preview

Start a free Courses trial
to watch this video

Sign up for Treehouse

Setting LIMITs

4:52 with Alena Holligan

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

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 up