Define the maximum result record

[Yangtze]

I would like to define the maxmum record that are shown after a search.

Say the total result has 20000 records

I want the result page show only the first 50 out of the 20000, and ignore the rest.

I heard someone say i could "Change the SQL to "SELECT TOP 50...."
I'm not sure how to do that, can anyone help?

This is the code you may need
---------
/ build SQL
$strsql = "SELECT * FROM `database`";
if ($DefaultFilter <> "") {
$whereClause .= "(" . $DefaultFilter . ") AND ";
}
if ($dbwhere <> "" ) {
$whereClause .= "(" . $dbwhere . ") AND ";
}
if (substr($whereClause, -5) == " AND ") {
$whereClause = substr($whereClause, 0, strlen($whereClause)-5);
}
if ($whereClause <> "") {
$strsql .= " WHERE " . $whereClause;
}
if ($OrderBy <> "") {
$strsql .= " ORDER BY `" . $OrderBy . "` " . @$HTTP_SESSION_VARS["database_OT"];
}

//echo $strsql; // comment out this line to view the SQL
$rs = mysql_query($strsql);
$totalRecs = intval(@mysql_num_rows($rs));

[coopster]

Have a look at the MySQL LIMIT [mysql.com] clause.

The

LIMIT

clause can be used to constrain the number of rows returned by the

SELECT

statement.

LIMIT

takes one or two numeric arguments, which must be integer constants. With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT * FROM table LIMIT 5,10; # Retrieve rows 6-15

[ergophobe]

SELECT TOP 50.

BTW, that's simply the Miscrosoft equiv of the MySQL LIMIT statement. Right idea, wrong DB server.

Tom

[digitalv]

Two ways to do this ... you can say:

SELECT TOP 50 * FROM ... (instead of SELECT * FROM)

Or you can do a SET ROWCOUNT line before your select statement. This is good for deletes since there is no "delete top", but for selects "select top" works best.