limiting mysql results and paginating

I need to limit the total results of query, and paginate them

[RobOgden]

I have the following code, which I use to limit the number of results per page and to number the pages:

$display = 10;
$searchterms = $_GET[searchterms];
$num_pages = $_GET[num_pages];
$start = $_GET[start];

if (!isset($num_pages)) {

if (isset($searchterms)) {
$query1 = "SELECT *** , MATCH(list_search_terms) AGAINST ('$searchterms') AS score FROM listings WHERE MATCH(list_search_terms) AGAINST('$searchterms') ORDER BY score DESC";
} else {
$query1 = "SELECT *** FROM listings ORDER BY list_name DESC";
}

$result1 = mysql_query ($query1);
$num = @mysql_num_rows ($result1);
if ($num > $display) {
$num_pages = ceil ($num/$display);
} elseif ($num > 0) {
$num_pages = 1;
} else {
echo '<h3>Sorry. We have no listings in this category</h3>';
include_once ('includes/footer.inc');
include_once ('includes/column_right.inc');
mysql_close();
exit();
}
$start = 0;
}

if (isset($searchterms)) {
$query = "SELECT ***, MATCH(list_search_terms) AGAINST ('$searchterms') AS score FROM listings WHERE MATCH(list_search_terms) AGAINST('$searchterms') ORDER BY score DESC LIMIT $start, $display";
} else {
$query = "SELECT *** FROM listings ORDER BY list_company DESC LIMIT $start, $display";
}

$result = @mysql_query ($query);
echo "<h3>Search results, in order of relevance</h3>
<font size=\"1\">Click on the title to view the full listing.</font>
***Here I echo the fields and layout of the search result***";
while ($row = @mysql_fetch_array ($result, MYSQL_ASSOC)) {

echo "***Here I echo the results***";
}
echo "</table><br><br>";
if ($num_pages > 1) {
echo '<hr width="50% align="left" color="#4A6887" />';

if ($start == 0) {
$current_page = 1;
} else {
$current_page = ($start/$display) + 1;
}

if ($start!= 0) {
echo '<a href="searchresults.php?start=' . ($start - $display) . '&num_pages=' . $num_pages . '&searchterms=' . $searchterms . '">Previous</a> ';
}

for ($i = 1; $i <= $num_pages; $i++) {
$next_start = $start + $display;
if ($i!= $current_page) {
echo '<a href="searchresults.php?start=' . (($display * ($i - 1))) . '&num_pages=' . $num_pages . '&searchterms=' . $searchterms . '">' . $i . '</a> ';
} else {
echo $i . ' ';
}
}
if ($current_page!= $num_pages) {
echo '<a href="searchresults.php?start=' . ($start + $display) . '&num_pages=' . $num_pages . '&searchterms=' . $searchterms . '">Next</a> ';
}
}

Sorry the quote is so long! I want to add a further stipulation to the search: that no more than 30 results are returned. (Otherwise, with such a non-hierarchical advanced search the database would shut down!). Is it possible to add this to the 'LIMIT' somehow?

I'd really appreciate any help, as we go live on Monday!

Yours,

Rob Ogden

[mincklerstraat]

I haven't looked at this in real detail, but it seems that in the second part of your code, what happens if

$_GET[num_pages]

(btw, you can clean up your code a bit by putting

'num_pages'

and the other keynames inside single quotes), you do have a LIMIT set. I'd just use this second part of your file's code, and axe the whole first part:

/* 
if (!isset($num_pages)) { 
if (isset($searchterms)) { 
$query1 = "SELECT *** , MATCH(list_search_terms) ( ... ) 
( ... ) 
mysql_close(); 
exit(); 
} 
$start = 0; 
} 
*/ 

comment that part out (elipsis meaning code coming in between)
Then in the next part, add another conditional statement about whether

$num_pages

is set - if it isn't set, set it to a value that makes sense for your script - i.e.,

/* part of your script directly after the part you commented out above */ 
if(empty($num_pages)) { // fyi empty is similar to!isset 
$num_pages = 1; // try 0 if this doesn't work at first 
$start = 0; 
} 
if (isset($searchterms)) { 
$query = "SELECT ***, MATCH(list_search_terms) AGAINST ('$searchterms') AS score FROM listings WHERE MATCH(list_search_terms) AGAINST('$searchterms') ORDER BY score DESC LIMIT $start, $display"; 
} else { 
$query = "SELECT *** FROM listings ORDER BY list_company DESC LIMIT $start, $display"; 
} 
(...) 

This might do it.

[RobOgden]

Thanks Mincklerstraat,

I did try it. Unfortunately, it stopped paginating, because all the code for numbering pages was in the first section we quoted out. However, you got me on the right track (i.e. limiting the number of pages rather than the query), and I inserted a clause limiting the variable $num_pages:

if ($num > $display) {
$num_pages = ceil ($num/$display);
if ($num_pages > 5) {
$num_pages = 5;
}
} elseif ($num > 0) {
$num_pages = 1;
} else {
echo '<h3>Sorry. We have no listings in this category</h3>
/* etc.
}

It seems to do the trick!

Thanks,

Rob