Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP PHP & Databases with PDO PDO Database Security Cleanup & Final Steps

Sam Donald
Sam Donald
36,305 Points
Posted by Sam Donald
Sam Donald
36,305 Points

Did anyone get the pagination going for this project?

I finished the project and have improved some areas like the response if the wrong id is input, and added more information. (i.e. release_date, rating etc...) However I can't get pagination to work. I've looked at a couple of answers on StackOverflow but can't get them to work here. I know we can limit the result with LIMIT(10) in the sqlite query, but if you hard code it in like that you can't paginate.

Has anyone got it to work??

Shon Levi
Shon Levi
6,036 Points
Shon Levi
Shon Levi
6,036 Points

Hey you Sam, I took the challenge of the pagination and think I made it :) Basically I make is using the _GET method with 'page' var, then valid that what the user enter is number, then decrease 1 and multiply it in 10 (for OFFSET 10)

Here is my code for index.php, Hope you understand, ask if need some explanation:

edit: Can't seeing it here - just upload it to CodePen, Take the HTML from here and paste it in the workspace

https://codepen.io/shonlevi/pen/RaVrQG

Sam Donald
Sam Donald
36,305 Points
Sam Donald
Sam Donald
36,305 Points

Hay Shon Levi that's good but there's too much hard coding going on. For now we know there are 1000 films in the database. But if this changes then your code will break and require you or another dev to go in and manually update every-time there's a change.

For example what if there where 1,023 films? With the returned results split into 10 per page this would see more than 100 pages. but your code would stop users from reaching these extra pages.

Also what about giving the user more informed feedback. Right now you tell them which of these 100 pages they're on, but they don't know how many pages there are. Simply going count($films) will only give you 10, as thats how many are returned each time. You do want to limit the number of returns so it all works fast and efficient, especially if you where working with a much bigger database of say 1mill. But we still want to keep the user optimally informed.

2 Answers

Shon Levi
Shon Levi
6,036 Points
Shon Levi
Shon Levi
6,036 Points

Sure there are more changes and checking to make, but this is the basic of pagination...

Niki Molnar
Niki Molnar
25,698 Points
Niki Molnar
Niki Molnar
25,698 Points

This is how I did the pagination. I created a function to write the pagination to the page, so I could add the pagination to both the top and bottom of the page. The only thing I have to figure out now is how to only show 7, 9 or 11 pages in total and not all of them. For example, if you're on page 8 out of 40, it would show:

<< < 5 6 7 [8] 9 10 11 > >>

or, even better

<< < 1 ... 5 6 7 [8] 9 10 11 ... 40 > >>

It would make it much neater.

<?php
// Return pagination HTML Function
function writePagination() {
    global $this_page, $total_pages; 
    $paginationText = "";
    $paginationText .= "<a href=\"index.php?page=1\">&lt;&lt;</a>&nbsp;"; // Goto 1st page  

    for ($i=1; $i<=$total_pages; $i++) { 
        if($i==$this_page) {
            $paginationText .= "<span>".$i."</span> ";  // Highlights the page user is on with CSS
        } else {
            $paginationText .= "<a href=\"index.php?page=".$i."\">".$i."</a> "; 
        }
    }; 
    $paginationText .= "<a href=\"index.php?page=".$total_pages."\">&gt;&gt;</a>"; // Goto last page
    return $paginationText;
}

// Pagination
$num_rec_per_page=25;
if (isset($_GET["page"])) { 
    $this_page = intval($_GET["page"]); 
} else { 
    $this_page = 1; 
}; 
$result_total = $db->prepare("SELECT count(*) FROM film");
$result_total->execute(); 
$total_records = $result_total->fetchColumn(); 
$total_pages = ceil($total_records / $num_rec_per_page); 
?>

HTML - Placed where pagination should be shown

<div id="pagination">
    <?php echo writePagination(); ?>
</div>

CSS

#pagination {
    padding-bottom:25px;
}
#pagination span {
     background-color:#8d9aa5;
     color:#fff;
     font-weight:bold;
     padding:3px;
}