Pagination while using a GET variable

Question

Hello,

I have had a difficult time paginating the code below. I think it has to do with passing the GET variable $find on to the next page.

Anyway, how would I paginate the code below, so that the table below shows only 100 rows per page?

Thanks in advance,

John

<?php
ob_start();
session_start();
$find = strip_tags($_GET['find']);
$illegal = array("'", ".", "/", "\"", ";", "{", "}", "[", "]", "\\", "''", "'''", "''''", "'''''", "\\\\", "\\\\\\", "\\\\\\\\");
$find = str_replace($illegal, '', $find);
$find = trim ($find);
$find = strtolower($find);
$find = stripslashes($find);
$_SESSION['find'] = $find;
?>

<?
if ($searching =="yes")
{

if ($find == "")
{
session_write_close();
header("Location:http://www.site.com/index.php");
exit;
unset($_SESSION['find']);   

}

mysql_connect("mysqlv10", "username", "password") or die(mysql_error());
mysql_select_db("database") or die(mysql_error());
$find = mysql_real_escape_string($find);

$result=mysql_query("SHOW TABLES FROM database LIKE '$find'")
or die(mysql_error());

if(mysql_num_rows($result)>0){
while($table=mysql_fetch_row($result)){
print "<p class=\"topic\">$table[0]</p>\n";
$r=mysql_query("SELECT * , votes_up - votes_down AS effective_vote FROM `$table[0]` WHERE site != '' ORDER BY effective_vote DESC");

print "<table class=\"navbar\">\n";
while($row=mysql_fetch_array($r)){

$effective_vote = $row['votes_up'] - $row['votes_down']; 

print "<tr>";

print "<td class='sitename'>".'<a type="amzn" category="products" class="links2">'.$row['site'].'</a>'."</td>";

print "<td class='votes'>".'<span class="votes_count" id="votes_count'.$row['id'].'">'.number_format($effective_vote).'</span>'."</td>";
print "<td class='ballot'>".'<span class="button" id="button'.$row['id'].'">'.'<a href="javascript:;" class="cell1" id="'.$row['id'].'">'.vote.'</a>'.'</span>'."</td>";
}
print "</tr>\n";
}
print "</table>\n";

}
?>

Answer 1

you need to use

LIMIT (<pagenumber*amount of records you want to display>,< amount of records you want to display >)

in your SQL statement

Answer 2

You have to use LIMIT in your query to tell the database how many rows you want and where to start from.

Pass along a parameter that tells the script that you want another chunk of the results and not just the first batch.

So for the link you can pass along the page parameter:

example.com/results.php?page=2

Where page= will tell the script what page you want to return.

Then you'll want to LIMIT the number of rows returned each time so that you have consistent paging.

$results_cnt = 100; //--rows you want per page of results

Now in your script you'll check to see if the page variable has been set. If not, default the start row to return from the first. But as you want to return different pages/sets of results, a little math is needed in order to start at the proper row.

if(isset($_GET["page"]) //--see if the variable is even there
{
    $page_num = (int)$_GET["page"]; //--forcing it to always be an integer

    $start_row = $results_cnt * ($page_num - 1);

    /* --
     what happens:
       ($results_cnt currently at 100)

     on page one (page=1), start at row 0
      math: 100 * (1 - 1) = 0

     on page two (page=2), start at row 100
      math: 100 * (2 - 1) = 100

     on page three (page=3), start at row 200
      math: 100 * (3 - 1) = 200

     etc.
    */
}
else
    $start_row = 0;

Now, having set the correct starting row, adjust the SQL query to use the variables like so:

$r = mysql_query("SELECT *, votes_up - votes_down AS effective_vote 
                   FROM `$table[0]`
                   WHERE site != '' 
                   ORDER BY effective_vote DESC
                   LIMIT $start_row, $results_cnt");

Every time you hit the page it will be checking to see if $_GET["page"] is there. If not, then display from the first row. If it is, do the maths and work out how many rows to pass over and show the next page of.