Basic Pagination

wendystewart80

I am trying to achieve php/mysql pagination of search results. I have selected to display 10 rows per page.
This should return 2 pages as there are 12 rows.
The results say there are 3 pages, and all rows from the query are displayed on each page except the first row which is missing from each page.
Any ideas why?

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);
$query_data = mysql_fetch_row($result1);
$numrows = $query_data[0];

$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>";

}

}

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

Weedpacket

You're using it up in these lines

$query_data = mysql_fetch_row($result1);
$numrows = $query_data[0];

If you want the number of rows returned in the result, use

$numrows=mysql_num_rows($result1);

wendystewart80

I use $query_data in the line below the one you mentioned.

Roger_Ramjet

Now I may be missing something, but this does not make any sense to me

$result1 = mysql_query($query) or trigger_error('SQL', E_USER_ERROR);
$query_data = mysql_fetch_row($result1);
$numrows = $query_data[0];

How is that going to give you the number of rows? $query_data contains the first row from your results and $query_data[0] is just the contents of the first column in that row.

For the count of the results you do

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

Get that right and the rest should be easy.

Drakla

As an aside, you're running the query twice, first without a limit, then secondly with one - that's a waste, look up SQL_CALC_FOUND_ROWS as it does the work for you. This is from my own paginator class to give you the idea

		// PARTIAL QUERY or full query supplied

function execute()
{		// create the full query if a fragment has been given
	if ($this->baseQuery)
	{
		$this->query = 'SELECT SQL_CALC_FOUND_ROWS  '. $this->baseQuery. ' LIMIT '. $this->offsetNow. ', '. $this->perPage;
	}

	$this->db->safe_query($this->query);

	$db2 = $this->db;
	$db2->safe_query('SELECT FOUND_ROWS()');
	$r = $db2->fetch_row();

	$this->totalRows = $r[0];

	$this->calc_offsets();
}

wendystewart80

Hi Roger Ramjet,
I exchanged the 2 lines of code you gave me for the 3 I had. This has helped as I now return all the results - the first row is no longer missing.
I still have the problem that all 14 rows are displayed on every page, even though I specified 10 results per page.
Any ideas?
Ta

wendystewart80

I realised I didn't post the bit of code that displays my search results.
Any ideas why all results are displayed on every page?

<?php
mysql_connect ("localhost", "root", "******"); 

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.";

?>

Roger_Ramjet

Thats because you did not concatenate the limit clause to the base query correctly

$query = "$query $limit"; is wrong

$query .= $limit;

$query = $query . $limit;

wendystewart80

Changed this but still all 13 results are displayed on each page.
Any further suggestions?

Roger_Ramjet

echo the query string - see what you are sending, must be some silly error

wendystewart80

Hi there,
I echoed the query and the following was displayed:
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 LIMIT 0,10

This should be right - the limit is set as 10 - but still all results are displayed.

I don't get it!!!!!!!!!!!!!!!!

Roger_Ramjet

Ahah! Did not notice before, you cannot ORDER BY a column not specified in the GROUP BY, would have expected an error but maybe mysql just ignores the rest of the query instead.