MYSQL Pagination Class

Coding Critique is the place to post source code for peer review by other members of DevNetwork. Any kind of code can be posted. Code posted does not have to be limited to PHP. All members are invited to contribute constructive criticism with the goal of improving the code. Posted code should include some background information about it and what areas you specifically would like help with.

Popular code excerpts may be moved to "Code Snippets" by the moderators.

Moderator: General Moderators

User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Man there was a crapload of things to be fixed, but I added support for single row viewing at a time.
Other than that basically things should work as they were supposed to originally. Everything suggested in the thread was addressed and would like to thank you guys for taking a look at this class.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Will integrate the updated script into my application tomorrow...ill let you know how it goes...thanks
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Seems to be running pretty good now.

Gonna make it place "..." at the end of the pages numbers when there are more pages so that it makes more sense
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post by n00b Saibot »

Pimptastic wrote:Gonna make it place "..." at the end of the pages numbers when there are more pages so that it makes more sense
First, Last Links will make sense even more :)

edit | oh, and with that I am a GURU, yay!
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

n00b Saibot wrote:
Pimptastic wrote:Gonna make it place "..." at the end of the pages numbers when there are more pages so that it makes more sense
First, Last Links will make sense even more :)

edit | oh, and with that I am a GURU, yay!
I think the current setup is confusing

The output looks like this

Code: Select all

Prev | Next | 1 | 2 | 3 | 4 | 5 | First | Last
Clicking last would lead me to believe i would be going to page 5 of 5, when actually there are 9 pages
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post by n00b Saibot »

I guess changing to following or something similar will make it less confusing

Code: Select all

Prev | First | 1 | 2 | 3 | 4 | 5 | ... | 9 | Next | Last
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Yeah, something like that would be better IMO.

Maybe something you can add JCart?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

In due time good fellow :) Gotto run to a lecture atm.. soontime
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

I've updated to output it as follows

Code: Select all

Prev | First | 1 | 2 | 3 | 4 | 5 | 6 | ... | Next | Last
or

Code: Select all

Prev | First | ... | 5 | 6 | 7 | 8 | 9 | 10 | 11 | ... | Next | Last
or

Code: Select all

Prev | First | ... | 7 | 8 | 9 | 10 | 11 | 12 | Next | Last
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post by n00b Saibot »

Goody goody :twisted: with that you have finished your job quite nicely :twisted:
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Excellent, will test it out later
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Okay, i have tested this out.

Seems to be a weird issue...

I have 11 pages of results

When on the first page the navigation looks like this

Code: Select all

Prev | First | 1 | 2 | 3 | 4 | 5 | 6 | ... | Next | Last
...when i got to page 2, the navigation looks like this

Code: Select all

Prev | First | 1 | 2 | 3 | 4 | 5 | ... | Next | Last
...notice the link to page 6 has gone...now i click the page 3 link

Code: Select all

Prev | First | 1 | 2 | 3 | 4 | 5 | 6 | ... | Next | Last
notice the page 6 link is now back


:? :? :? :?
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post by n00b Saibot »

Pimptastic wrote:Okay, i have tested this out.

Seems to be a weird issue...

I have 11 pages of results

When on the first page the navigation looks like this

Code: Select all

Prev | First | 1 | 2 | 3 | 4 | 5 | 6 | ... | Next | Last
...when i got to page 2, the navigation looks like this

Code: Select all

Prev | First | 1 | 2 | 3 | 4 | 5 | ... | Next | Last
...notice the link to page 6 has gone...now i click the page 3 link

Code: Select all

Prev | First | 1 | 2 | 3 | 4 | 5 | 6 | ... | Next | Last
notice the page 6 link is now back


:? :? :? :?
:lol: Elementary, my dear Watson. Here we have our new case... The Case of Missing Page Link :lol:
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

I've refactored most of the class. Would you guys mind running it through some tests?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

Check out this function for outputting page numbering like phpBB

1 2 3 ... 15 16 17 .... 55 56 57


Also read up here: http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html

on FOUND_ROWS()
A SELECT statement may include a LIMIT clause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again. To obtain this row count, include a SQL_CALC_FOUND_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterward:

Code: Select all

function create_pagination($url, $page, $maxPage) {  
    // if there is no need for pagination return the stuff empty  
    if($maxPage == 1) {  
        return ' <b>1</b> ';  
    }  
    $pagination = '';  
    $current_page = $page;  
    //if there are 10 or more links create the special pagination  
    if ( $maxPage >= 10 ) {  
        //show the first 3 links  
        if($current_page <= 5) {  
            //this is to prevent that if page 1 is selected only 2 links are shown instead of 3.  
            if($current_page == 1) {  
                $maximum = $current_page + 2;  
            } else {  
                $maximum = $current_page + 1;  
            }  
            for($i = 1; $i <= $maximum; $i++) {    
            	$pagination .= ($i == $current_page) ? '<strong>'.$i.'</strong>' : '<a href="' .$url .$i. '">' .$i. '</a>';  
                if($i < $maximum) {  
                	$pagination .= ', ';  
                }  
            }  
        } else {
			//if the page is not in the first row of links show the the first 3 links.  
			$maximum = 3;  
			for($i = 1; $i <= $maximum; $i++) {    
				$pagination .= ($i == $page) ? '<strong>'.$i.'</strong>' : '<a href="' .$url .$i. '">' .$i. '</a>';  
				if($i < $maximum) {  
					$pagination .= ', ';  
				}  
			}  
		}  
		if($current_page > 5) {  
			//show the first dots  
			$pagination .= ' ... ';  
			//and show the link in front and behind $current_page  
			if($current_page <= $maxPage) {  
				 //this creates the links if page is higher then 5.  
				 if($current_page == $maxPage) {  
					  $start_num = $current_page - 2;  
				 } else {  
					  $start_num = $current_page - 1;  
				 }  
				 $max_num = 3;  
				 for($i = 1; $i <= $max_num; $i++) {  
				   $pagination .= ($start_num == $current_page) ? '<strong>'.$start_num.'</strong>' : '<a href="' .$url .$start_num. '">' .$start_num. '</a>';  
				   if($i < $maximum) {  
						$pagination .= ', ';  
				   }  
				   $start_num++;  
				}  
			}  
			//see if there have to be dots at the end.                 
		}  
		if($current_page <= ($maxPage - 5)) {  
			//its smaller so we have to show the dots.  
			$pagination .= ' ... ';  
		}  
		//see how many links we should put at the end of the links row.  
		//if the current page is is the last or the one before the last we display no links.  
		if($current_page == $maxPage) {  
			$max_num = 0;  
		 }  
		 if($current_page == ($maxPage -1)) {  
			$max_num = 0;  
		 }  
		 if($current_page == ($maxPage -2)) {  
			$max_num = 1;  
		 }  
		 if($current_page == ($maxPage -3)) {  
			$max_num = 2;  
		 }  
		 if($current_page <= ($maxPage -4)) {  
			$max_num = 3;  
		 }  
		 //little thing to check the output of the above if functions.  
		 if($max_num !== 0) {  
			$start_num = $maxPage - ($max_num - 1);  
			if($current_page >= ($maxPage - 4))  {  
				$pagination .= ', ';  
			}  
			for($i = 1; $i <= $max_num; $i++) {  
				$pagination .= '<a href="' .$url .$start_num. '">' .$start_num. '</a>';  
				if($start_num < $maxPage) {  
					$pagination .= ', ';  
				}  
				$start_num++;  
			}  
		}  
	} else {
		//if there are 9 links or less create a link string  
		$nextLink = array();    
		for($i = 1; $i <= $maxPage; $i++) {    
			$nextLink[] = ($i == $page) ? '<strong>'.$i.'</strong>' : '<a href="' .$url .$i. '">' .$i. '</a>';  
		}  
		$pagination .= implode(', ', $nextLink);    
	}  
	return $pagination;  
}
Locked