Help with pagination of sql results!

I am very new to server scripting, helping one of the projects for disaster management. I am trying to create a data retrieval by several drop down lists. With the help of this forum and other web resources I was able to produce the following script. However, I have problems with pagination. I am getting the first 10 results, but not moving to second page. Please help me to correct or to redo the following coding.Your help is much appreciated.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<body>

<?php
$disaster = $_POST["disaster"];
$phase = $_POST["phase"];
$country = $_POST["country"];

error_reporting(E_ALL);
if (!isset($_POST['submit'])) {
// form not submitted
?>

<form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post">

<table width="100%" bordercolor="#FFFFFF" bgcolor="#FFFFFF">
<tr>
<td width="11%"><div align="left">Disaster :</div></td>
<td width="89%">
<Select NAME="disaster">
<Option VALUE="">All</option>
<Option VALUE="Tsunami">Tsunami</option>
</Select>
</td>
</tr>

<tr>
<td width="11%"><div align="left">Phase :</div></td>
<td width="89%"><Select NAME="phase">
<Option VALUE="">All</option>
<Option VALUE="Pre-disaster">Pre-disaster</option>
<Option VALUE="Rehabilitation">Rehabilitation</option>
<Option VALUE="Reconstruction">Reconstruction</option>
</Select></td>
</tr>

<tr>
<td width="11%"><div align="left">Country :</div></td>
<td width="89%"><Select NAME="country">
<Option VALUE="">All</option>
<Option VALUE="sri lanka">Sri Lanka</option>
<Option VALUE="indonasia">Indonasia</option>
<Option VALUE="philippines">Philippines</option>
<Option VALUE="australia">Australia</option>
<Option VALUE="india">India</option>
<Option VALUE="thailand">Thailand</option>
<Option VALUE="maldives">Maldives</option>
</Select></td>
</tr>
</table>

<?php
}

else {

// rows to return
$limit=10;

//connect to your database
mysql_connect("***","***","***"); // provide host, username & password

//specify database
mysql_select_db("island") or die("Unable to select database"); //provide name of the database

// Build SQL Query
$query = "select * from publications where disaster LIKE'%$disaster%' AND phase LIKE '%$phase%' AND country LIKE'%$country%'";
// specify your table and field names for the SQL query

$result=mysql_query($query) or die (mysql_error());

$numrows=mysql_num_rows($result);

if ($numrows == 0)
{
echo "<p>Sorry, your search returned with no results.</p>";
}

// next determine if s has been passed to script, if not use 0
if (empty($s)) {
$s=0;
}

// get results
$query .= " limit $s,$limit";
$result = mysql_query($query) or die("Couldn't execute query");
$numresults = mysql_num_rows($result);

if ($numresults > $s)
{

$count = 1 + $s ;

// now you can display the results returned
while ($row= mysql_fetch_array($result)) {
$title = $row["title"];
$author = $row["author"];
$URL = $row["URL"];

echo
"<table>
<tr>
<td><strong>$count.</strong></td>
<td><strong>Title:</strong></td>
<td><strong>$title</strong></td>
</tr>
<tr>
<td></td>
<td>Authors:</td>
<td>$author</td>
</tr>
<tr>
<td></td>
<td>Description:</td>
<td>$URL</td>
</tr>
</table>
<br></br>" ;

$count++ ;
}
//it is fine up to here

$self = $_SERVER['PHP_SELF'];
$currPage = (($s/$limit) + 1);

//break before paging
echo "<br />";

// next we need to do the links to other results
if ($s>=1) { // bypass PREV link if s is 0
$prevs=($s-$limit);
print " <a href=\"$self?s=$prevs\"><<
Prev 10</a>&nbsp ";
}

// calculate number of pages needing links
$pages=intval($numrows/$limit);

// $pages now contains int of pages needed unless there is a remainder from division

if ($numrows%$limit) {
// has remainder so add one page
$pages++;
}

// check to see if last page
if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

// not last page so give NEXT link
$news=$s+$limit;

echo " <a href=\"$self?s=$news\">Next 10 >></a>";
}

$a = $s + ($limit) ;
if ($a > $numrows) { $a = $numrows ; }
$b = $s + 1 ;
echo "<p>Showing results $b to $a of $numrows</p>";
}
}
?>
</body>
</html>

Help with pagination of sql results!

Mutty

eelixduppy

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week