Limiting MySQL Column Search Results

Search page displays the whole search recordset

[Brownie]

I have a page that searches various mysql columns and displays the results on the same page. I have limited the display recordset to 10 records per page, however the page always displays ALL the search results. I am obviously very stubborn and have been pulling my hair out over this for days - any help may save me from premature baldness!

$maxRows_BulletinsRecordset = 10;
$pageNum_BulletinsRecordset = 0;
if (isset($HTTP_GET_VARS['pageNum_BulletinsRecordset'])) {
$pageNum_BulletinsRecordset = $HTTP_GET_VARS['pageNum_BulletinsRecordset'];
}
$startRow_BulletinsRecordset = $pageNum_BulletinsRecordset * $maxRows_BulletinsRecordset;

$query_BulletinsRecordset = "SELECT * FROM bulletins ORDER BY BulletinDate DESC";

$query_limit_BulletinsRecordset = sprintf("%s LIMIT %d, %d", $query_BulletinsRecordset, $startRow_BulletinsRecordset, $maxRows_BulletinsRecordset);

$BulletinsRecordset = mysql_query($query_limit_BulletinsRecordset, $metrologyhub) or die(mysql_error());

$row_BulletinsRecordset = mysql_fetch_assoc($BulletinsRecordset);

Any pointers welcomed!

[edited by: jatar_k at 2:40 pm (utc) on Oct. 21, 2003]
[edit reason] replaced url with code example [/edit]

[Nick_W]

Hi,

You need to use the LiMIT [mysql.com] keyword in your SELECT statement.

Nick

[jatar_k]

first I would change that sprintf lina and just build the query like so

$query_BulletinsRecordset = "SELECT * FROM bulletins ORDER BY BulletinDate DESC LIMIT $startRow_BulletinsRecordset,$maxRows_BulletinsRecordset";

echo $query_BulletinsRecordset;

then when you echo it you can see if it is correct. I assume you have a php version < 4.1.0 since you are using HTTP_GET_VARS

[Brownie]

Running PHP 4.1.2

[jatar_k]

You should maybe change those too $_GET instead.

HTTP GET variables: $_GET [ca.php.net]

did you try restructuring how you build your query and echoing the content? You could post what the actual query looks like when you echo it.

[Brownie]

Thanks for the pointers - very much a newbie, using DW MX to get me started, then moving on from there. Have never been able to get LIMIT to work for me, but have persevered and now have a successful solution.

$maxRows_Search = 10;
$pageNum_Search = 0;
if (isset($HTTP_GET_VARS['pageNum_Search'])) {
$pageNum_Search = $HTTP_GET_VARS['pageNum_Search'];
}
$startRow_Search = $pageNum_Search * $maxRows_Search;

mysql_select_db($database_metrologyhub, $metrologyhub);

$colname_Search = "1";
if (isset($HTTP_GET_VARS['searchbulletins'])) {
$colname_Search = (get_magic_quotes_gpc())? $HTTP_GET_VARS['searchbulletins']
: addslashes($HTTP_GET_VARS['searchbulletins']);
}

$query_Search = "SELECT * FROM bulletins WHERE (bulletins.BulletinTopic LIKE '%$colname_Search%' OR bulletins.BulletinNumber LIKE '%$colname_Search%')";
$query_limit_Search = sprintf("%s LIMIT %d, %d", $query_Search, $startRow_Search, $maxRows_Search);
$Search = mysql_query($query_limit_Search, $metrologyhub) or die(mysql_error());
$row_Search = mysql_fetch_assoc($Search);

if (isset($HTTP_GET_VARS['totalRows_Search'])) {
$totalRows_Search = $HTTP_GET_VARS['totalRows_Search'];
} else {
$all_Search = mysql_query($query_Search);
$totalRows_Search = mysql_num_rows($all_Search);
}
$totalPages_Search = ceil($totalRows_Search/$maxRows_Search)-1;

$queryString_Search = "";
if (!empty($HTTP_SERVER_VARS['QUERY_STRING'])) {
$params = explode("&", $HTTP_SERVER_VARS['QUERY_STRING']);
$newParams = array();
foreach ($params as $param) {
if (stristr($param, "pageNum_Search") == false &&
stristr($param, "totalRows_Search") == false) {
array_push($newParams, $param);
}
}
if (count($newParams)!= 0) {
$queryString_Search = "&" . implode("&", $newParams);
}
}
$queryString_Search = sprintf("&totalRows_Search=%d%s", $totalRows_Search, $queryString_Search);

The Search recordset now displays 10 records per page.

Thanks!