We help IT Professionals succeed at work.

Adding pagination to search results?

m0tSiE
m0tSiE asked
on
Hi,

Could someone show me how to add pagination to my search results?

I've currently got the search results to show entries with the same name in an expandable div which is fine no matter how many there is, however I am looking to have the initial search results (not including the ones in the expandable div) to be limited to 50.

Could someone show me how to add the pagination to the search results?

Many thanks,
Paul.
<table border="0" cellspacing="0" cellpadding="0" class="test">
	<tr>
		<td class="results-header-left" width="20">&nbsp;</td>
		<td class="results-header" width="180">Name</td>
		<td class="results-header" width="130">Attire</td>
		<td class="results-header" width="130">Creator</td>
		<td class="results-header" width="40">Rating</td>
		<td class="results-header" width="50">Preview</td>
		<td class="results-header" width="50">Attributes</td>
		<td class="results-header" width="50">Entrance</td>
		<td class="results-header" width="50">Movesets</td>
	</tr>
 
                
 
<?php
//connect to mysql
 
mysql_connect("localhost","",""); 
mysql_select_db(""); 
 
$search=mysql_real_escape_string($_POST["search"]);
 
$result = mysql_query("SELECT * FROM caws WHERE name LIKE '%$search%' ORDER BY name");
  
$arr_data = array();
 
echo mysql_error(); 
 
while ($row = mysql_fetch_assoc($result))
{
	if (!isset($arr_data[$row["name"]]))
  {
    $arr_data[$row["name"]] = array();
  }   
  $arr_data[$row["name"]][] = $row;
}
 
foreach ( $arr_data as $name => $rows )
{    
     if (sizeof($rows) == 1)
     { // only 1 details row, print all on 1 row
        $row = $rows[0];
        $detail_row = 1;
		$total_value=$row['total_value'];
  $total_votes=$row['total_votes'];
  $rating = ($total_votes>0)?round($total_value / $total_votes,$decimals):0;
        print "
        <tr>
		<td class=results-show-left><img src='http://cawsearch.com/images/icons/{$row["console"]}.png'></td>
		<td class=results-show><a href='http://cawsearch.com/svr2008/view_caw.php?idx={$row["id"]}'>{$row["name"]}</a></td>
		<td class=results-show>{$row["attire"]}</td>
		<td class=results-show><a href='http://cawsearch.com/svr2008/creator.php?c={$row["creator"]}'>{$row["creator"]}</a></td>
		<td class=results-show>$rating/10</td>
		<td class=hoverbox><li><a href=#>Roll Over<img src=http://cawsearch.com{$row["image"]} class=preview /></a></li></td>
		<td class=results-show><a href='http://cawsearch.com/svr2008/attributes.php?letter={$row["name"]}'>Click Here</a></td>
		<td class=results-show><a href='http://cawsearch.com/svr2008/entrances.php?letter={$row["name"]}'>Click Here</a></td>
		<td class=results-show><a href='http://cawsearch.com/svr2008/movesets.php?letter={$row["name"]}'>Click Here</a></td>
	</tr>";
     }
     else // if (sizeof($rows) == 1)
     {
       // print the name's row
       print "
         <tr>
				<td class=results-show-left>&nbsp;</td>
                 <td colspan=9 class=results-show-multi>
				   <a href='#' onclick=\"document.getElementById('row{$row["id"]}').style.display=='none' ? document.getElementById('row{$row["id"]}').style.display='' : document.getElementById('row{$row["id"]}').style.display = 'none';return false\">
				     <b>{$name}</b> <i>- (Multiple Results)</i>
				   </a>
				 </td>
         </tr>
         ";
       print "
	   <tr><td colspan=9><div id='row{$row["id"]}' style='display:none;'><table border='0' cellspacing='0' cellpadding='0' class='test'>
		<tr>
		<td class='results-header-left' width='20'>&nbsp;</td>
		<td class='results-header' width='180'>Name</td>
		<td class='results-header' width='130'>Attire</td>
		<td class='results-header' width='130'>Creator</td>
		<td class='results-header' width='40'>Rating</td>
		<td class='results-header' width='50'>Preview</td>
		<td class='results-header' width='50'>Attributes</td>
		<td class='results-header' width='50'>Entrance</td>
		<td class='results-header' width='50'>Movesets</td>
		</tr>";
       $detail_row = 0;
		foreach ( $rows as $row )
       {
		$detail_row++;
		$total_value2=$row['total_value'];
		  $total_votes2=$row['total_votes'];
		  $rating2 = ($total_votes2>0)?round($total_value2 / $total_votes2,$decimals):0;
        print "
        <tr>
		<td class=results-show-left><img src='http://cawsearch.com/images/icons/{$row["console"]}.png'></td>
		<td class=results-show><a href='http://cawsearch.com/svr2008/view_caw.php?idx={$row["id"]}'>{$row["name"]}</a> ($detail_row)</td>
		<td class=results-show>{$row["attire"]}</td>
		<td class=results-show><a href='http://http://cawsearch.com/svr2008/creator.php?c={$row["creator"]}'>{$row["creator"]}</a></td>
		<td class=results-show>$rating2/10</td>
		<td class=hoverbox><li><a href=#>Roll Over<img src=http://cawsearch.com{$row["image"]} class=preview /></a></li></td>
		<td class=results-show><a href='http://cawsearch.com/svr2008/attributes.php?letter={$row["name"]}'>Click Here</a></td>
		<td class=results-show><a href='http://cawsearch.com/svr2008/entrances.php?letter={$row["name"]}'>Click Here</a></td>
		<td class=results-show><a href='http://cawsearch.com/svr2008/movesets.php?letter={$row["name"]}'>Click Here</a></td>
	</tr>";
 
       } // foreach ( $rows as $row )
      print "
	  </table></div></td></tr>
	  ";
    } // else if (sizeof($rows) == 1)
} //  foreach ( $arr_data as $name => $rows )
 
?>
</table>

Open in new window

Comment
Watch Question

Commented:
there are many ways to paging here, just store search condition in some where. e.g session, html form...
try use my code store in session
<table border="0" cellspacing="0" cellpadding="0" class="test">
        <tr>
                <td class="results-header-left" width="20">&nbsp;</td>
                <td class="results-header" width="180">Name</td>
                <td class="results-header" width="130">Attire</td>
                <td class="results-header" width="130">Creator</td>
                <td class="results-header" width="40">Rating</td>
                <td class="results-header" width="50">Preview</td>
                <td class="results-header" width="50">Attributes</td>
                <td class="results-header" width="50">Entrance</td>
                <td class="results-header" width="50">Movesets</td>
        </tr>
 
                
 
<?php
//connect to mysql
$pagecount=20;
if(isset($_GET("pageno")))
     $pageno=$_GET("pageno"); 
else
     $pageno=1;
mysql_connect("localhost","",""); 
mysql_select_db(""); 
 
if(isset($_POST["search"])){
   $search=mysql_real_escape_string($_POST["search"]);
   $_SESSION("search")=$search;
}
else
   $search=mysql_real_escape_string($_SESSION["search"]);
 
$result = mysql_query(sprintf('SELECT * FROM caws WHERE name LIKE \'%%1$s%\' ORDER BY name LIMIT %2$d,%3$d',$search,(($pageno-1)*$pagecount),$pagecount);
  
$arr_data = array();
 
echo mysql_error(); 
 
while ($row = mysql_fetch_assoc($result))
{
        if (!isset($arr_data[$row["name"]]))
  {
    $arr_data[$row["name"]] = array();
  }   
  $arr_data[$row["name"]][] = $row;
}
 
foreach ( $arr_data as $name => $rows )
{    
     if (sizeof($rows) == 1)
     { // only 1 details row, print all on 1 row
        $row = $rows[0];
        $detail_row = 1;
                $total_value=$row['total_value'];
  $total_votes=$row['total_votes'];
  $rating = ($total_votes>0)?round($total_value / $total_votes,$decimals):0;
        print "
        <tr>
                <td class=results-show-left><img src='http://cawsearch.com/images/icons/{$row["console"]}.png'></td>
                <td class=results-show><a href='http://cawsearch.com/svr2008/view_caw.php?idx={$row["id"]}'>{$row["name"]}</a></td>
                <td class=results-show>{$row["attire"]}</td>
                <td class=results-show><a href='http://cawsearch.com/svr2008/creator.php?c={$row["creator"]}'>{$row["creator"]}</a></td>
                <td class=results-show>$rating/10</td>
                <td class=hoverbox><li><a href=#>Roll Over<img src=http://cawsearch.com{$row["image"]} class=preview /></a></li></td>
                <td class=results-show><a href='http://cawsearch.com/svr2008/attributes.php?letter={$row["name"]}'>Click Here</a></td>
                <td class=results-show><a href='http://cawsearch.com/svr2008/entrances.php?letter={$row["name"]}'>Click Here</a></td>
                <td class=results-show><a href='http://cawsearch.com/svr2008/movesets.php?letter={$row["name"]}'>Click Here</a></td>
        </tr>";
     }
     else // if (sizeof($rows) == 1)
     {
       // print the name's row
       print "
         <tr>
                                <td class=results-show-left>&nbsp;</td>
                 <td colspan=9 class=results-show-multi>
                                   <a href='#' onclick=\"document.getElementById('row{$row["id"]}').style.display=='none' ? document.getElementById('row{$row["id"]}').style.display='' : document.getElementById('row{$row["id"]}').style.display = 'none';return false\">
                                     <b>{$name}</b> <i>- (Multiple Results)</i>
                                   </a>
                                 </td>
         </tr>
         ";
       print "
           <tr><td colspan=9><div id='row{$row["id"]}' style='display:none;'><table border='0' cellspacing='0' cellpadding='0' class='test'>
                <tr>
                <td class='results-header-left' width='20'>&nbsp;</td>
                <td class='results-header' width='180'>Name</td>
                <td class='results-header' width='130'>Attire</td>
                <td class='results-header' width='130'>Creator</td>
                <td class='results-header' width='40'>Rating</td>
                <td class='results-header' width='50'>Preview</td>
                <td class='results-header' width='50'>Attributes</td>
                <td class='results-header' width='50'>Entrance</td>
                <td class='results-header' width='50'>Movesets</td>
                </tr>";
       $detail_row = 0;
                foreach ( $rows as $row )
       {
                $detail_row++;
                $total_value2=$row['total_value'];
                  $total_votes2=$row['total_votes'];
                  $rating2 = ($total_votes2>0)?round($total_value2 / $total_votes2,$decimals):0;
        print "
        <tr>
                <td class=results-show-left><img src='http://cawsearch.com/images/icons/{$row["console"]}.png'></td>
                <td class=results-show><a href='http://cawsearch.com/svr2008/view_caw.php?idx={$row["id"]}'>{$row["name"]}</a> ($detail_row)</td>
                <td class=results-show>{$row["attire"]}</td>
                <td class=results-show><a href='http://http://cawsearch.com/svr2008/creator.php?c={$row["creator"]}'>{$row["creator"]}</a></td>
                <td class=results-show>$rating2/10</td>
                <td class=hoverbox><li><a href=#>Roll Over<img src=http://cawsearch.com{$row["image"]} class=preview /></a></li></td>
                <td class=results-show><a href='http://cawsearch.com/svr2008/attributes.php?letter={$row["name"]}'>Click Here</a></td>
                <td class=results-show><a href='http://cawsearch.com/svr2008/entrances.php?letter={$row["name"]}'>Click Here</a></td>
                <td class=results-show><a href='http://cawsearch.com/svr2008/movesets.php?letter={$row["name"]}'>Click Here</a></td>
        </tr>";
 
       } // foreach ( $rows as $row )
      print "
          </table></div></td></tr>
          ";
    } // else if (sizeof($rows) == 1)
} //  foreach ( $arr_data as $name => $rows )
 
?>
</table>

Open in new window

Commented:
also, add the page nav in your page

<a href="searchresult.php?pageno=<?=($pageno+1)?>">Next</a>

Author

Commented:
Thanks HuyBD,

I've tested the code but it gives me this error:

Fatal error: Can't use function return value in write context in /home2/cawsearc/public_html/svr2008/caws.php on line 143

Is it due to the connection details being under the page count code?

Commented:
yes, I forget add more %, please change line 33
$result = mysql_query(sprintf('SELECT * FROM caws WHERE name LIKE \'%%%1$s%%\' ORDER BY name LIMIT %2$d,%3$d',$search,(($pageno-1)*$pagecount),$pagecount);

Open in new window

Author

Commented:
Fatal error: Can't use function return value in write context in /home2/cawsearc/public_html/svr2008/caws.php on line 143

Line 143 is:

if(isset($_GET("pageno")))

Commented:
Ahh, yes, I have a missing, change to if(isset($_GET["pageno"]))
<table border="0" cellspacing="0" cellpadding="0" class="test">
        <tr>
                <td class="results-header-left" width="20">&nbsp;</td>
                <td class="results-header" width="180">Name</td>
                <td class="results-header" width="130">Attire</td>
                <td class="results-header" width="130">Creator</td>
                <td class="results-header" width="40">Rating</td>
                <td class="results-header" width="50">Preview</td>
                <td class="results-header" width="50">Attributes</td>
                <td class="results-header" width="50">Entrance</td>
                <td class="results-header" width="50">Movesets</td>
        </tr>
 
                
 
<?php
//connect to mysql
$pagecount=20;
if(isset($_GET["pageno"]))
     $pageno=$_GET["pageno"]; 
else
     $pageno=1;
mysql_connect("localhost","",""); 
mysql_select_db(""); 
 
if(isset($_POST["search"])){
   $search=mysql_real_escape_string($_POST["search"]);
   $_SESSION["search"]=$search;
}
else
   $search=mysql_real_escape_string($_SESSION["search"]);
 
$result = mysql_query(sprintf('SELECT * FROM caws WHERE name LIKE \'%%1$s%\' ORDER BY name LIMIT %2$d,%3$d',$search,(($pageno-1)*$pagecount),$pagecount);
  
$arr_data = array();
 
echo mysql_error(); 
 
while ($row = mysql_fetch_assoc($result))
{
        if (!isset($arr_data[$row["name"]]))
  {
    $arr_data[$row["name"]] = array();
  }   
  $arr_data[$row["name"]][] = $row;
}
 
foreach ( $arr_data as $name => $rows )
{    
     if (sizeof($rows) == 1)
     { // only 1 details row, print all on 1 row
        $row = $rows[0];
        $detail_row = 1;
                $total_value=$row['total_value'];
  $total_votes=$row['total_votes'];
  $rating = ($total_votes>0)?round($total_value / $total_votes,$decimals):0;
        print "
        <tr>
                <td class=results-show-left><img src='http://cawsearch.com/images/icons/{$row["console"]}.png'></td>
                <td class=results-show><a href='http://cawsearch.com/svr2008/view_caw.php?idx={$row["id"]}'>{$row["name"]}</a></td>
                <td class=results-show>{$row["attire"]}</td>
                <td class=results-show><a href='http://cawsearch.com/svr2008/creator.php?c={$row["creator"]}'>{$row["creator"]}</a></td>
                <td class=results-show>$rating/10</td>
                <td class=hoverbox><li><a href=#>Roll Over<img src=http://cawsearch.com{$row["image"]} class=preview /></a></li></td>
                <td class=results-show><a href='http://cawsearch.com/svr2008/attributes.php?letter={$row["name"]}'>Click Here</a></td>
                <td class=results-show><a href='http://cawsearch.com/svr2008/entrances.php?letter={$row["name"]}'>Click Here</a></td>
                <td class=results-show><a href='http://cawsearch.com/svr2008/movesets.php?letter={$row["name"]}'>Click Here</a></td>
        </tr>";
     }
     else // if (sizeof($rows) == 1)
     {
       // print the name's row
       print "
         <tr>
                                <td class=results-show-left>&nbsp;</td>
                 <td colspan=9 class=results-show-multi>
                                   <a href='#' onclick=\"document.getElementById('row{$row["id"]}').style.display=='none' ? document.getElementById('row{$row["id"]}').style.display='' : document.getElementById('row{$row["id"]}').style.display = 'none';return false\">
                                     <b>{$name}</b> <i>- (Multiple Results)</i>
                                   </a>
                                 </td>
         </tr>
         ";
       print "
           <tr><td colspan=9><div id='row{$row["id"]}' style='display:none;'><table border='0' cellspacing='0' cellpadding='0' class='test'>
                <tr>
                <td class='results-header-left' width='20'>&nbsp;</td>
                <td class='results-header' width='180'>Name</td>
                <td class='results-header' width='130'>Attire</td>
                <td class='results-header' width='130'>Creator</td>
                <td class='results-header' width='40'>Rating</td>
                <td class='results-header' width='50'>Preview</td>
                <td class='results-header' width='50'>Attributes</td>
                <td class='results-header' width='50'>Entrance</td>
                <td class='results-header' width='50'>Movesets</td>
                </tr>";
       $detail_row = 0;
                foreach ( $rows as $row )
       {
                $detail_row++;
                $total_value2=$row['total_value'];
                  $total_votes2=$row['total_votes'];
                  $rating2 = ($total_votes2>0)?round($total_value2 / $total_votes2,$decimals):0;
        print "
        <tr>
                <td class=results-show-left><img src='http://cawsearch.com/images/icons/{$row["console"]}.png'></td>
                <td class=results-show><a href='http://cawsearch.com/svr2008/view_caw.php?idx={$row["id"]}'>{$row["name"]}</a> ($detail_row)</td>
                <td class=results-show>{$row["attire"]}</td>
                <td class=results-show><a href='http://http://cawsearch.com/svr2008/creator.php?c={$row["creator"]}'>{$row["creator"]}</a></td>
                <td class=results-show>$rating2/10</td>
                <td class=hoverbox><li><a href=#>Roll Over<img src=http://cawsearch.com{$row["image"]} class=preview /></a></li></td>
                <td class=results-show><a href='http://cawsearch.com/svr2008/attributes.php?letter={$row["name"]}'>Click Here</a></td>
                <td class=results-show><a href='http://cawsearch.com/svr2008/entrances.php?letter={$row["name"]}'>Click Here</a></td>
                <td class=results-show><a href='http://cawsearch.com/svr2008/movesets.php?letter={$row["name"]}'>Click Here</a></td>
        </tr>";
 
       } // foreach ( $rows as $row )
      print "
          </table></div></td></tr>
          ";
    } // else if (sizeof($rows) == 1)
} //  foreach ( $arr_data as $name => $rows )
 
?>
</table>

Open in new window

Author

Commented:
Thanks,

I've updated it and it now gives this error.

Parse error: syntax error, unexpected ';' in /home2/cawsearc/public_html/svr2008/caws.php on line 157

Line 157:
$result = mysql_query(sprintf('SELECT * FROM caws WHERE name LIKE \'%%1$s%\' ORDER BY name LIMIT %2$d,%3$d',$search,(($pageno-1)*$pagecount),$pagecount);
Commented:
yes, just add more close brackets

$result = mysql_query(sprintf('SELECT * FROM caws WHERE name LIKE \'%%%1$s%%\' ORDER BY name LIMIT %2$d,%3$d',$search,(($pageno-1)*$pagecount),$pagecount));

Author

Commented:
Thanks, worked fine :D

Explore More ContentExplore courses, solutions, and other research materials related to this topic.