Skip to content
April 3, 2008 / ranacse05

Pagination By php & mysql


Just yesterday i was finding a solution to view data. Actually its called “Pagination” .I have so many row/entity in a table and i want to show 10/5 entity per page then what i have to do ? I just design a algo for this using php and mysql but its very easy and a little bit long so i made a post on phpxperts and Aman Bhai ans me about it.Here it is..

1. Obtain the required page number

This code will obtain the required page number from the $_GET array. Note that if it is not present it will default to 1.

if (isset($_GET[‘pageno’])) {
$pageno = $_GET[‘pageno’];
} else {
$pageno = 1;
} // if

2. Identify how many database rows are available

This code will count how many rows will satisfy the current query.

$query = “SELECT count(*) FROM table WHERE …”;
$result = mysql_query($query, $db) or trigger_error(“SQL”, E_USER_ERROR);
$query_data = mysql_fetch_row($result);
$numrows = $query_data[0];

3. Calculate number of $lastpage

This code uses the values in $rows_per_page and $numrows in order to identify the number of the last page.

$rows_per_page = 15;
$lastpage = ceil($numrows/$rows_per_page);

4. Ensure that $pageno is within range

This code checks that the value of $pageno is an integer between 1 and $lastpage.

$pageno = (int)$pageno;
if ($pageno > $lastpage) {
$pageno = $lastpage;
} // if
if ($pageno < 1) {
$pageno = 1;
} // if

5. Construct LIMIT clause

This code will construct the LIMIT clause for the sql SELECT statement.

$limit = ‘LIMIT ‘ .($pageno1) * $rows_per_page .‘,’ .$rows_per_page;

6. Issue the database query

Now we can issue the database qery and process the result.

$query = “SELECT * FROM table $limit;
$result = mysql_query($query, $db) or trigger_error(“SQL”, E_USER_ERROR);
… process contents of $result …

7. Construct pagination hyperlinks

Finally we must construct the hyperlinks which will allow the user to select other pages. We will start with the links for any previous pages.

if ($pageno == 1) {
echo ” FIRST PREV “;
} else {
echo ” <a href='{$_SERVER[‘PHP_SELF’]}?pageno=1′>FIRST</a> “;
$prevpage = $pageno-1;
echo ” <a href='{$_SERVER[‘PHP_SELF’]}?pageno=$prevpage‘>PREV</a> “;
} // if

Next we inform the user of his current position in the sequence of available pages.

echo ” ( Page $pageno of $lastpage ) “;

This code will provide the links for any following pages.

if ($pageno == $lastpage) {
echo ” NEXT LAST “;
} else {
$nextpage = $pageno+1;
echo ” <a href='{$_SERVER[‘PHP_SELF’]}?pageno=$nextpage‘>NEXT</a> “;
echo ” <a href='{$_SERVER[‘PHP_SELF’]}?pageno=$lastpage‘>LAST</a> “;
} // if

But the main thing is its same algo which one i designed. 🙂

Note : Above tutorial is collected from this link .

Advertisements

4 Comments

Leave a Comment
  1. fumiNET / Apr 4 2008 6:49 pm

    Don’t forget to protect against any potential injection, and to sanitize your input.

  2. Shaikh Sonny Aman / Apr 16 2008 8:27 pm

    Nice post… Very structurally organized. Booked marked it for my reference 🙂

  3. ranacse05 / Apr 16 2008 8:50 pm

    @Aman
    Thanks for ur such a nice comment.Very glad to see ur comment on my blog:) i’m a big fan of urs.I heard about u from H2.

  4. oce / Sep 14 2008 3:04 am

    Great post.
    Was wondering how I would be able to actually display the different page numbers —
    thanks again!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: