Page 1 of 1

search limit

Posted: Tue Dec 08, 2009 9:22 am
by chris_s_22
Can anyone show me or point me in direction of a tutorial that limits search results
1) i want to limit the number of rows that will show up in total eg 500 / 998 so only the 500 can be viewed.
2) i also want to display 10 results per page and have link to next 10 and previous 10 as well as option to jump straight to page ?

Code: Select all

 
$result = mysql_query ("SELECT * FROM table_name WHERE column_name LIKE '%$search_entered%' "); 
$row=mysql_num_rows($result);
 
echo "<table>";
while($row=mysql_fetch_array($result)) 
{
$column1 = $row["column1 "];
$column2 = $row["column2 "];
$column3 = $row["column3 "];
echo "<td>$column1 <td>";
echo "<td>$column2 <td>";
echo "<td>$column3 <td>";
}
echo "</table>";
 
 

Re: search limit

Posted: Tue Dec 08, 2009 12:37 pm
by AbraCadaver
LIMIT

Re: search limit

Posted: Tue Dec 08, 2009 12:43 pm
by BlaineSch
Yea it's pretty simple. You might do one query to get the total count:

Code: Select all

$result = mysql_query ("SELECT count(*) as cnt, '1' as ib FROM table_name WHERE column_name LIKE '%$search_entered%' group by 'ib'");
That way you know how many pages you want... just get the number divide by 10 and round up

Code: Select all

$result = mysql_query ("SELECT count(*) as cnt, '1' as ib FROM table_name WHERE column_name LIKE '%$search_entered%' group by 'ib'");
$row = mysql_fetch_assoc($result);
echo "Pages: ".ceil($row['cnt']/10);
Now just throw it through a loop to get the pages, and even the page your on.

Then in the query do a "limit" which accepts 2 parameters, one being where to start and one being how many you want to display so "Limit 0,30" would be the first page "Limit 30,30" would be the second.

Code: Select all

$page = preg_replace('/[^0-9]/', '', $_GET['page']);
$start = ($page-1)*10;
$query = mysql_query("SELECT * FROM table_name WHERE column_name LIKE '%$search_entered%' LIMIT $start, 10");
Do note that none of this is tested.