Mysql/PHP pagination problem

blitzer05

Hi,

I would like to output the rows from my MYSQL query into manageable pages, eg 30 items per page.

The query is driven by a form containing numerous text boxes and selection lists.

My script accesses the form variables using
$var1 = $POST['var1'];
$var2 = $POST['var2']; etc,
which then are incorporated into the query which uses LIMIT to specify desired ouptut.

The script works fine when the page first loads, but when I click 'next' or any other 'page link' to see more results, I get no output. When the script reloads, $var1 and $var2$ now have no data which in turn changes the overall query.

I'm not sure how to preserve the form data when the script reloads itself to display remaining output.

Any suggestions, greatly appreciated.

SQL_Maestro

for example:

tableview.php

<html>
<body>
<?
$page_len=30;
$page_cur=intval((isset($GET['page']) && !empty($GET['page']))?$_GET['page']:0);
$db=mysql_connect('server','root','');
mysql_select_db('test', &$db);
$query='SELECT COUNT() FROM time';
$r=mysql_query(&$query);
list($total)=mysql_fetch_array(&$r, MYSQL_NUM);
$page=0;
$page_count=ceil($total/$page_len);
$page_id=0;
$limit=10;
while($page<$page_count && $limit--){
$page_vis=$page_id$page_len+1;
$page_vis.='-'.($page_vis+$page_len-1);
echo ($page_id!==$page_cur?"[<a href='?page=$page_id'>$page_vis</a>] ":"[$page_vis] ");
$page_id++;
}
$page_cur=$page_len;
echo '<table border=1>';
$query="SELECT FROM time LIMIT $page_cur,$page_len";
$r=mysql_query(&$query);
while($row=mysql_fetch_array(&$r,MYSQL_NUM))
echo '<tr><td>'.implode("</td>\n<td>",&$row)."</td></tr>\n";
?>
</table>
</body>

</html>

Roger_Ramjet

Simplest way is to save the original form data in session vars so that it is available from page to page.

$_SESSION['var1'] = $_POST['var1'];
$_SESSION['var2'] = $_POST['var2'];

Alternatively, you could save the original query in a session var and just substitute the LIMIT clause for each page.

blitzer05

The Form is processed by script.php shown below

Session Variables would work if set somewhere between the form and script.
However ,the script stores the form value's and when another page is selected thru the paging system, the script reloads itself and $area = "" causing the problem.

If the script could retain the orginal form values, problem solved. The only line causing a probelm is "$area = $_POST['area'];" at the top of the script

<?php
// from form
$area = $_POST['area'];

function getPagerData($numHits, $limit, $page)
{
$numHits = (int) $numHits;
$limit = max((int) $limit, 1);
$page = (int) $page;
$numPages = ceil($numHits / $limit);

       $page = max($page, 1); 
       $page = min($page, $numPages); 

       $offset = ($page - 1) * $limit; 

       $ret = new stdClass; 

       $ret->offset   = $offset; 
       $ret->limit    = $limit; 
       $ret->numPages = $numPages; 
       $ret->page     = $page; 

       return $ret; 
   }  	  
// get the pager input values 
$page = $_GET['page']; 
$limit = 2; 
include("dbconnection.inc");  
$query = "SELECT * FROM location"; 

if ($area != "all") {
    $query .= " where area='$area'";
   }

$query .= " ORDER BY area";
$result = mysql_query($query)
or die ("Couldn't execute query.");

$numHits = mysql_num_rows($result);

// work out the pager values
$pager = getPagerData($numHits, $limit, $page);
$offset = $pager->offset;
$limit = $pager->limit;
$page = $pager->page;

$query = "SELECT * FROM location";

if ($area != "all") {
$query .= " where area='$area'";
}

$query .= " ORDER BY area limit $offset, $limit";
$result2 = mysql_query($query)
or die ("Couldn't execute query.");

// database output
while ($row = mysql_fetch_array($result2)) #41
{
extract($row);
}

// output paging system
if ($page == 1) // this is the first page - there is no previous page
echo "Prev";
else // not the first page, link to the previous page
echo "<a href=\"script.php?page=" . ($page - 1) . "\">Prev</a>";

for ($i = 1; $i <= $pager->numPages; $i++) { 
    echo " | "; 
    if ($i == $pager->page) 
        echo "Pg $i"; 
    else 
        echo "<a href=\"script.php?page=$i\">Pg $i</a>"; 
} 

if ($page == $pager->numPages) // this is the last page - there is no next page 
    echo " | Next"; 
else            // not the last page, link to the next page 
    echo " | <a href=\"script.php?page=" . ($page + 1) . "\">Next</a>";

Roger_Ramjet

That is what the superglobal $_SESSION is for so use it.