limiting results/paging not working

[winey]

I've put together a search section for my wine web directory site, but I'm having a problem with breaking it into pages and numbering. the numbering is appearing at the bottom of the results list, but all the results are being returned on the first page, even though there should only be 15.

Site can be found at: http://www.winereader.com/search.php

here's the code I'm using:

// database connection stuff here
include ('dbconnect.inc');
mysql_select_db("winereaderdb",$dbcnx);

									$searchstring = explode(", ",$keywords); 
									sort($searchstring); 
									$l = count($searchstring); 

									$rows_per_page = 15;
									for ($i = 0; $i < $l ; $i++) { 

									$sql = "SELECT * FROM sites where description like '%$searchstring[$i]%' or name like '%$searchstring[$i]%' ORDER BY name ASC "; 

									$result = mysql_query($sql,$dbcnx) 
									or die("Couldn't execute query.");
									}
									$total_records = mysql_num_rows($result);
									$pages = ceil($total_records / $rows_per_page);
									mysql_free_result($result);


									if (!isset($screen))
									  $screen = 0;
									$start = $screen * $rows_per_page;

									for ($i = 0; $i < $l ; $i++) { 

									$sql = "SELECT * FROM sites where description like '%$searchstring[$i]%' or name like '%$searchstring[$i]%' ORDER BY name ASC "; 

									$result = mysql_query($sql,$dbcnx) 
									or die("Couldn't execute query.");


									$sql .= "LIMIT $start, $rows_per_page";
									$rows = mysql_num_rows($result);
									while ( $row = mysql_fetch_array($result) ) {
										$id = $row["id"];
										$name = $row["name"];
										$url = $row["url"];
										$description = $row["description"];
										echo ("<p><a href='$url' target='_blank'>$name</a><br>
										$description</p>");
										}
									}
									$youhere=$screen+1;
									echo "<hr>
										You are on page $youhere of $pages.<br>";
									// page numbering links now
									for ($i = 0; $i < $pages; $i++) {
									  $url = "searchresults.php?screen=" . $i;
									  $pagenumber=$i+1;
									  echo " | <a href=\"$url\">$pagenumber</a> | ";
									}

[enterume]

Hi,

Why not you use LIMIT at your sql statement? LIKE :

$sql = "SELECT * FROM sites where description like '%$searchstring[$i]%' or name like '%$searchstring[$i]%' ORDER BY name ASC LIMIT $startrow, $endrow ";

This would be better if you do it in the sql statemnt, and get faster result return, and save the MySQL resource, because you will only get the certain range of record from database, in stead you start select from row1 till the row that you want.

you can refer to here.

Thanks
Louis

[jmcneese]

here's a simple (and not very intelligent) example to get you started.

$query = mysql_query("SELECT id,name FROM table WHERE id=$id ORDER BY id DESC LIMIT $offset, $display_records");

while($index = mysql_fetch_array($query)) {
// print your record stuff here
}

$numrecords = mysql_num_rows($query);
$nextoffset = $offset+$display_records;
$prevoffset = $offset-$display_records;

if ($offset == 0) {
    $previous = "";
} else {
    $previous = "<a href=\"$PHP_SELF?offset=$prevoffset\"><< Previous 10 records</a> ";
}

if ($numrecords < 10) {
    $next = "";
} else {
    $next = "<a href=\"$PHP_SELF?offset=$nextoffset\">Next 10 records >></a>";
}