homepage Welcome to WebmasterWorld Guest from 54.163.72.86
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Pagination with MySQL
A little guide
eelixduppy




msg:3175728
 8:44 am on Dec 2, 2006 (gmt 0)

Well it's extremely early on Saturday morning and, well....I'm bored. :)

Anyway, there have been some questions about Pagination with MySQL, so I decided to write up something real quick. I believe the comments within the code explain it all.

Please excuse me if there are any typos...I'm tired ;)


<?php

/*
Check whether the 'page' GET variable is not defined or equals zero.
In either case, an error is sent and the script terminated.
*/

if(empty [us3.php.net]($_GET['page'])) {
echo 'Page number is not defined!';
exit [us3.php.net]();
}

/*
The following makes sure that the 'page' variable only contains numbers,
therefore limiting it to just integers(whole numbers).
*/

else if(!preg_match [us3.php.net]("/^([0-9])+$/",$_GET['page'])) {
echo 'Page is not a valid value!';
exit [us3.php.net]();
}

/*
If the 'num' GET variable isn't defined in the URI, then the var num_per_page is by default set to 10.
This utilizes the ternary operator [us3.php.net]. After the check for existence, then there is a check to make sure that
the string contains only an integer using the same method as above.
*/

$num_per_page = (empty [us3.php.net]($_GET['num']))? 10 : $_GET['num'];
if(!preg_match [us3.php.net]("/^([0-9])+$/",$num_per_page)) {
echo 'Num is not a valid value!';
exit [us3.php.net]();
}
//Get the page number from the URI; the pages start when page=1, not page=0
$page = $_GET['page'];

/*
Connect to database and then select a database.
You can also include this information if you want, but this is just an example ;)
*/

$link = mysql_connect [us3.php.net]('localhost','username','password');
mysql_select_db [us3.php.net]('database');

/*
This piece is the essence of pagination. Using LIMIT in the mysql query is what we are going
to use for this. The following line creates a new variable, qPage, which is the value of the
page number minus one times the number of rows you want to show up on each page.
For example, if page = 1 in the URI, then 1-1*10 = 0. This is the offset for the LIMIT in the query.
If page = 2then 2-1*10 = 10. Since 10 is the number per page in the example, then it only makes sense
to start the next limit at an offset of 10.
*/

$qPage = (($page-1)*$num_per_page);

/*
The following is the query of the pagination; this is where the magic takes place.
the SELECT is self-explanatory. The LIMIT is the most important. LIMIT has the syntax
LIMIT [offset],[length]. Knowing this format you should be able to follow the query now.
Now for the following you are probably wondering why I added 1 to the value for the number per page.
I did this simply to know if there is going to be another page after the current one. We will need this
information later when we are echoing out the 'Next page' link.

Note: You may want to change the query to fit your needs, however the LIMIT clause should stay the same.
Everything else can be changed :)
*/

$query = "SELECT * FROM `table` ORDER BY `col_name` ASC LIMIT ".$qPage.",".($num_per_page+1);
//You should remove the 'die(mysql_error());' part of the following after development
$result = mysql_query [us3.php.net]($query) or die [us3.php.net](mysql_error [us3.php.net]());

/*
Here 'num' is set to the number of rows that the above query returned. The condition simply
sees if the number of rows returned equals zero, because if it does than that means that there
are no more rows for the specified LIMIT.
*/

$num = mysql_num_rows [us3.php.net]($result);
if($num == 0) {
echo 'No more results!';
exit [us3.php.net]();
}

/*
Here's is another interesting part. At first thought one may want to use a while [us3.php.net] loop here.
The only problem with the while loop is that it echos that last row that we selected in the
query when we don't want it to. Instead of handling this it's easier to use a for loop
instead, using the num_per_page variable as shown. Obviously you can format the HTML and PHP
output however you'd like; I'm just outputting the array as an example.
*/

for [us3.php.net]($i = 0; $i < $num_per_page; $i++) {
$row = mysql_fetch_array [us3.php.net]($result);
echo '<pre>';
print_r [us3.php.net]($row);
echo '</pre>';
}
mysql_close [us3.php.net]($link);
//Close the link to the MySQL database.

/*
This goes back to selecting that extra row in the query. If the amount selected (num) is
greater than the number per page(num_per_page) than that means that there is at least one more
row to be returned after the the range that you want, therefore you know you can echo the 'Next Page'
link without having to worry if you are linking to a blank page or not.

The rest should be self-explanatory. If you are going onto the next page, you are going to increment the
page variable by one, and of course you still want to keep the current number per page otherwise you may
see repeats, so you just echo that value in it's corresponding place within the url query.
*/

if($num > $num_per_page) {
echo '<a href="index.php?page='.($page+1).'&num='.$num_per_page.'">Next Page</a>';
}
echo '<br/>';
//Just acts as a separator for the two links ;)

/*
The following acts the same way as above, however instead of incrementing you are subtracting one from the
page variable to 'go back' to the previous page. Obviously you do not want to 'go back' when you are on the first page
to begin with, so we first check to make sure that the page value is larger than '1' (our first page) before we start
echoing it to the browser.
*/

if($page > 1) {
echo '<a href="index.php?page='.($page-1).'&num='.$num_per_page.'">Previous Page</a>';
}

?>

There are also many other related threads on this topic. Here's a few:

If anyone can add something, please do. I know I'm missing something ;)

Hope this helps some of you out there!

[edited by: jatar_k at 4:13 pm (utc) on Dec. 2, 2006]

[edited by: eelixduppy at 12:15 am (utc) on Mar. 15, 2007]

 

Lowkei




msg:3175788
 11:56 am on Dec 2, 2006 (gmt 0)

Hi eelixduppy,

Thanks a lot for sharing! It's really useful.. I not yet try it out anyway. :p

Regards,
Lowkei

eelixduppy




msg:3176085
 6:55 pm on Dec 2, 2006 (gmt 0)

Thanks Lowkei and Jatar ;)

For those that are confused, you must specify at least the page number in the query for the script to work:

index.php?page=1&num=5

Where page is the page number, and num is the number of rows per page.

Sorry; I left that out of my first post.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved