pagination in php

Question

i am using this to collect comments made abt a particular user. i want to display 7 comments on each page and want to enable pagination. what would be the steps of implementation. sorry. n00b question.

$query = "SELECT msg, user_id, comment_time FROM comments WHERE aid = '$aid' ORDER BY comment_time DESC";
        $result = mysql_query($query) or die("ERROR: $query.".mysql_error());
        if (mysql_num_rows($result) > 0) {
            while($row = mysql_fetch_object($result)){
                $uidval = $row->user_id;
                if ($uidval != 0){
                $userInfo = $facebook->api_client->fql_query("SELECT name, pic_square_with_logo, profile_url FROM user WHERE uid='".$uidval."'");

            //  echo '<br>Array Info<br>';
            //  print_r($userInfo);
            //  echo '<br><br>';

                $nameuser = $userInfo[0]['name'];
                $pic = $userInfo[0]['pic_square_with_logo'];
                $profile_url = $userInfo[0]['profile_url'];

                echo '<img src="'.$pic.'" />';
                echo '<a href="'.$profile_url.'">'.$nameuser.'</a>';
                echo '<br>';
                echo $row->comment_time;
                echo '<br>';
                echo $row->msg;
                echo '<br>';
                }

            }
        }

Answer 1

Use LIMIT x, y to indicate the first and number of records to pull as a page.

Answer 2

This is the basic method how you'd implement pagination:

// Page number, >= 1, could come from $_GET for example
$page = 1;
// Amount of items to show on page
$perpage = 7;
// Calculate the index of first item to show,
// on page 1 we obviously want to start from 0, hence the - 1 part
$start = ($page - 1) * $perpage;
// Build the query using those variables
$query = "SELECT ... LIMIT $start, $perpage";

You would of course have to take the maximum and minimum page numbers into account by checking the count of items in table first and adjusting the page variable etc, but this should get you started.

Answer 3

The solution is best achieved via SQL's limit/offset clauses. For MySQL, this is achieved via appending "LIMIT [offset] [count]" to your query. PostgreSQL uses separate "select ... LIMIT [count] OFFSET [offset]" syntax.

The idea is you limit the number of results returned to the actual number you want to display. It can yield a huge performance increase if you're displaying page 1 of 200, with a hundred results per page.

Of course, you need to run a second query - a "select count(*) from ..." to determine the number of results in the result set. Divide this by the number of results per page, and you've got the number of pages.

You will likely want to pass a page parameter in the query string, something like

http://mysite.com/index.php?page=7

and then pull the data using a similar method (consider this pseudo code; I know I'm not actually querying properly)

<?php

$num_per_page = 20; // 20 results per page

$page = isset($_GET['page']) ? $_GET['page'] : 0; // start from page 0

// Build our big query minus the SELECT part here
$from_part = 'tbl_results [where ...]"'

$num_results = query("SELECT COUNT(*) FROM $from_part");

// Use ceil to round upwards
$num_pages = ceil($num_results / $num_per_page);

// Cap the page a the last page
if ($page > $num_pages)
  $page = $num_pages;

// Cap the page at the first page
if ($page <= 1)
  $page = 1;

$offset = $page * $num_per_page;

// Build the final query to select the relevant page of data
$results = query("SELECT * FROM $from_part LIMIT $offset, $num_per_page");

?>

Answer 4

While learning to do pagination is a worthy endeavor as a newbie, I highly suggest just using code that has already been written. Check out PEAR, Zend Framework, or any other collection of libraries for implementations of pagination.

The above examples give a good explanation of how pagination works but completely ignore security practices, maintainability, and other factors that go into production quality code. I'm not flaming the above commenters I'm saying that they don't have the time to write production quality code for you, hence to not use their examples (copy paste).

Security, by scrubbing what comes in through your GET parameters and a secure database abstraction layer are highly necessary here. You also have to deal with the fringe scenario of whether someone manually enters in a value that is out of range or below range.

Most of the aforementioned issues are handled by frameworks (like KohanaPHP, Zend Framework, CakePHP, &c...) these days.