Pagination

[wendystewart80]

Hi there,
I am trying my hand a pagination.
There are 13 rows from the database which are being called. I have requested 10 rows per results page. The pagination says there are 2 pages but both pages contain all 13 rows. The limit of 10 is not working.
Any suggestions? - Here's my code.
[php]
<?php
mysql_connect ("localhost", "root", "purple1");

mysql_select_db ('good_practice');

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

$query = "SELECT * FROM project AS p, regions AS r, cats as c
WHERE p.region = r.region AND c.id = p.id
GROUP BY p.id
ORDER BY org
";

$result1 = mysql_query($query) or trigger_error('SQL', E_USER_ERROR);
$numrows = mysql_num_rows($result1);

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

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

$limit = 'LIMIT' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;

$query = $query . $limit;

$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);

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

echo " ( Page $pageno of $lastpage ) ";

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

if(mysql_num_rows($result1)>0)
{
while($r=mysql_fetch_array($result1))
{

$id=$r["id"];
$org=$r["org"];
$address1=$r["address1"];
$address2=$r["address2"];
$address3=$r["address3"];
$city=$r["city"];
$pcode=$r["pcode"];
$tel=$r["tel"];
$fax=$r["fax"];
$email=$r["email"];
$web=$r["web"];
$name=$r["name"];
$surname=$r["surname"];
$image=$r["image"];
$updated=$r["updated"];
$regdesc=$r["regdesc"];

echo "<table width=800><tr><td><hr></table><table><tr bgcolor=#ffffff><td width=300 valign=top><font face=Arial, Helvetica, sans-serif size=3><b>$org</b><font face=Arial, Helvetica, sans-serif size=2>
";
if($address1>0)print "<br>$address1";
if($address2>0)print "<br>$address2";
if($address3>0)print "<br>$address3";
echo "<br> <font face=Arial, Helvetica, sans-serif size=2>
$city<br>$pcode<br><i>Region:</i> $regdesc";

if($tel>0)print "<br><i>Tel:</i> $tel<br>";
if($fax>0)print "<i>Fax:</i> $fax<br>";
print "<i>Email: </i><a href=mailto:$email>$email</a><br>";
print "<i>Contact Name: </i>$name $surname<br>";
print "<i>Web: </i><a href=http://$web target=_blank>$web</a><br>";
if($updated>0)print "<i>Updated:</i> $updated<br>";

echo"<font face=Arial, Helvetica, sans-serif size=2><b>Categories:</b><br>";

$result2 = mysql_query("SELECT catdesc, g.catid FROM def_cats AS d, cats AS g
WHERE g.catid = d.catid AND g.id = ".$r["id"]."
ORDER BY catdesc
")
or die ("Error - ".mysql_error()."");

while($r2=mysql_fetch_array($result2))
{ $catdesc=$r2["catdesc"];
echo "<font face=Arial, Helvetica, sans-serif size=2>$catdesc <br>";

}

echo"<td valign=top align=left width=115><font face=Arial, Helvetica, sans-serif size=2><img src='$image' width='100'><br>";
echo "<a href='searchprojMI.php?id=$id'>More project info</a></td>";
echo " <td width=15><td valign=top width=375>";
$result3 = mysql_query("SELECT * FROM practice AS p, project AS t, regions AS r
WHERE p.id = t.id AND t.region = r.region AND p.id = ".$r["id"]."
ORDER BY verified
")
or die ("Error - ".mysql_error()."");

while($r3=mysql_fetch_array($result3))
{
$gpid=$r3["gpid"];
$gptext=$r3["gptext"];
$gptitle=$r3["gptitle"];
$min_age=$r3["min_age"];
$max_age=$r3["max_age"];
$verified=$r3["verified"];

echo "<font face=Arial, Helvetica, sans-serif size=2><i>Practice Example Title: </i><b>$gptitle</b><br>
<i>Age range:</i> $min_age to $max_age <br><i>Updated:</i> $verified<br>";
echo "<a href='searchpracMI.php?gpid=$gpid'>More detail</a><br><hr>";
}

echo " </table>";

}

}

else
print "<br><br>Search terms too specific - please try again.";

?>
[/php]

[jd01]

It appears you are setting the limit on $result, but you are using $result1 for the loop to display results, and there are no limits set on $result1, so it is showing all rows from the table.

Justin

[mcibor]

Probably you pass pageno wrongly. Here you will find a function that writes the html: [webmasterworld.com...]

The limit I get via:

$page = (!(int)($_GET['page']))? 1 : (int)$_GET['page'];//be sure it's numeric!
$sql = "SELECT * FROM table";//example

$perPage = 20;
$pages = 10;
$limit = (int)(($page - 1) * $perPage);

$result = mysql_query($sql);
$amount = mysql_num_rows($result);
$text_choosePage = my_pagination($page, $perPage, $pages, $amount, "index.html");

$sql_limited = $sql." LIMIT ".$limit.", ".$perPage;

That should work
Michal Cibor

PS. Listen to jd!