Paginating Results

[UVL]

Ok, I got a question regarding paginating a search query from a MySQL Database. Basically, what I'm trying to achieve is letting the end user enter a "keyword" into the search field and return the results of the search. I only want 25 results per page to be shown, but I only want the "Next" link to appear only when there are more result pages to be viewed on their specific search. Here's the code I've got so far, and it works in every aspect except that it shows the "Next" link persistently, even when the search failed and no results are found. I've replaced "sensitive information" with ***** characters, but everything works nicely except the link to the next page of results. If anyone knows a method to remedy this, I'd be very greatful.

Here's my code (I included the HTML just in case I need to place the "Next" link script somewhere specific):

  

<? $hostname = "localhost"; // The DB server. 
$username = "************"; // The username you created for this database. 
$password = "*********"; // The password you created for the username. 
$usertable = "**********"; // The name of the table you made. 
$dbName = "*********"; // This is the name of the database you made. 
MYSQL_CONNECT($hostname, $username, $password) OR DIE("Database Connection Unavailable");
@mysql_select_db( "$dbName") or die( "Unable to select database"); 

//error message (not found message)begins 
$XX = No results found, please try your search again. Try limiting your search to specific keywords.; 

//check if this is the first page 
if (!isset($_GET['startrow']) or !is_numeric($_GET['startrow'])) { 
//we give the value of the first row to 0 
$startrow = 0; 
//otherwise we take the value from the URL 
} else { 
$startrow = (int)$_GET['startrow']; 
} 

//query details table begins
if (isset($_GET['search'])) {

 $search = $_GET['search'];

} else {

 $search = $_POST['search'];

}
$query = mysql_query("SELECT * FROM ******** WHERE keywords LIKE '%$search%' LIMIT $startrow, 25");
while ($row = @mysql_fetch_array($query)) 
{ 
$variable1=$row["********"];
$variable2=$row["**********"]; 
$variable3=$row["************"]; 
$variable4=$row["**********"];
$variable5=$row["*********"];
$variable6=$row["******"];
$variable7=$row["***********"];
$variable8=$row["***********"];
$variable9=$row["*********"];
$variable10=$row["***********"];
$variable11=$row["*******"];

//below is the table layout for results 
?>

<div id="Layer1" style="position:absolute; width:200px; height:8px; z-index:16; left: 2px; top: 63px;">
  <table width="740" border="0" cellspacing="0" cellpadding="0">
    <tr align="center" valign="middle">
      <td width="100"><div align="center"><u>************</u></div></td>
      <td width="70"><div align="center"><u>************</u></div></td>
      <td width="150"><div align="center"><u>***********</u></div></td>
      <td width="65"><div align="center"><u>************</u></div></td>
      <td width="100"><div align="center"><u>***********</u></div></td>
      <td width="255"><div align="center"><u>***********</u></div></td>
    </tr>
  </table>
</div>
<table width="740" border="0" cellpadding="0" cellspacing="0" bordercolor="#FFFFFF">
  <tr>
    <td width="100" valign="top"><div align="center"><span class="style7"><strong><? print "$variable1" ?></strong></span></div></td>
    <td width="70" align="left" valign="middle"><div align="left"></div>
        <div align="center"><? print "$variable5" ?></div></td>
    <td width="150"><div align="center"><? print ("$variable4"); ?></div></td>
    <td width="65" align="right" valign="middle"><div align="left"></div>
        <div align="center"></div>
        <div align="center"><? print "$variable8" ?></div></td>
    <td width="100"><div align="center"><? print "$variable7" ?></div></td>
    <td width="255" height="18" align="left" valign="middle"><div align="center"><strong><? print "$variable3" ?></strong></div></td>
 </tr>
</table>

<?
print ("<tr>");
print ("</tr>"); 
}
//below this is the function for no record!!
if (!$variable1)
{ 
print ("$XX");
} 
//end 
?>

<?
//now this is the link..
echo '<a href="*****.php?startrow='.($startrow+25).'&search='.$search.'">Next</a>'; 
?>

[bastien]

trick here is to add a separate query to count the total number of possible rows in the resultset meeting your where condition without the LIMIT clause...

two benefits

1. provides a total for the user...they can see how may potential results there are

2. provides data for a check to see if the next is needed

assume this query is run and stored in a variable called $total_results

<?
//now this is the link..

if ($total_results>25){
echo '<a href="*****.php?startrow='.($startrow+25).'&search='.$search.'">Next</a>'; 
}
?>

Note: you also need a Back link when the user moves off the first page

[UVL]

would this be the proper way to write a link going back?

<? 
//now this is the backward link.. 

if ($total_results>25){ 
echo '<a href="testie.php?startrow='.($startrow-25).'&search='.$search.'">Back</a>';  

} 
?>

[bastien]

yes

[mhalloran]

Here's a paging script of mine that you can look over if you want. It creates the page display as well as the limit for the SQL statement.

/*======================================*/
// Creating the Page display
/*======================================*/
// getting the total rows
$query = mysql_query($main_sql, $con);
$total_rows =(mysql_num_rows($query));

// setting the display variables
$rows_per_page	= 50;// this value can be changed
$total_pages	= ((ceil(($total_rows/$rows_per_page)+1))-1);

// setting page to 1 if not set
if (!$page) $page	=1;

// making the beginning
$page_disp = "<span class=\"main\">";
if ($page!=1) {
	$page_disp 	.= "<a class=\"main\" href=\"".$PHP_SELF."?page=".($page-1)."\">";
	$page_disp 	.="&lt;&lt;prev&nbsp;&nbsp;</a>";
}

// making the middle
if ($total_pages>1)  {
	for ($i=1;$i<($total_pages+1); $i++)  {
		if ($i==$page) {
			$page_disp .= "<font color=\"yellow\">[".$i."]</span>";
		} else {
			$page_disp .= "<a class=\"main\" href=\"$PHP_SELF?page=$i\">&nbsp;$i&nbsp;</a>";
		}
	}
}

// making the end
if ($page!=$total_pages) {
	$page_disp 	.= "<a class =\"main\" href='".$PHP_SELF."?page=".($page+1)."'>";
	$page_disp 	.="&nbsp;&nbsp;next>>&nbsp;</a>";
} else {
	$page_disp 	.="&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
}
$page_disp .="</span>";

/*======================================*/
// Setting the SQL limits
/*======================================*/
$start_limit 	= (($page*$rows_per_page)-$rows_per_page);
$limit		= $rows_per_page;
$main_sql 	.= " LIMIT $start_limit, $limit";

[Nimmie]

Hi

I am trying to use your paging code with a multi-column results table...
only one problem, my query result isn't limited 🙁.

example: http://dev.all-fantasy-reviews.com

echo $page_disp;
echo "<TABLE BORDER=\"0\" width=\"100%\">\n";

//changed this to a for loop so we can use the number of rows
for($j = 0; $j < $total_rows; $j++) {
    $row = mysql_fetch_array($query);
    if($j % $columns == 0) {
        echo "<TR>\n";
echo
 // all my nice book display

 if(($j % $columns) == ($columns - 1) || ($j + 1) == $total_rows) {
        //if there is a remainder of 1, end the row
        //or if there is nothing left in our result set, end the row
        echo "</TR>\n";
    }
}
echo "</TABLE>\n";