Adding pagination to search results?

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

m0tSiEAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HuyBDCommented:
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

0
HuyBDCommented:
also, add the page nav in your page

<a href="searchresult.php?pageno=<?=($pageno+1)?>">Next</a>
0
m0tSiEAuthor 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?
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

HuyBDCommented:
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

0
m0tSiEAuthor 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")))
0
HuyBDCommented:
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

0
m0tSiEAuthor 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);
0
HuyBDCommented:
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));
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
m0tSiEAuthor Commented:
Thanks, worked fine :D
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.