• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

Display limited output from mysql query

I want to do the following:
1-The user search for certain item.
2-Server run sql search.
3-Display the output of the search by showing 25,50, or 100 output per page as per the user choice.
For number 1, and 2 I do that easily by php, but for number 3, I am not sure how to accomplish this, is server side or client to show only a selected number of output, can some guide me how to do so?
0
Ashraf-Hassan
Asked:
Ashraf-Hassan
1 Solution
 
Avinash ZalaWeb ExpertCommented:
use LIMIT into the query. Like this:

$sql= 'SELECT * FROM table LIMIT 5';

will fetch only 5 first records.

$sql= 'SELECT * FROM table LIMIT 5 10';

will fetch 10 records starting from 5.

http://dev.mysql.com/doc/refman/5.1/en/select.html
http://hackmysql.com/case3

Hope this helps.
Addy
0
 
Ashraf-HassanAuthor Commented:
The query part is not difficult, but the display part is not clear to me.
Let us say the search result return x output, and the use chose to see 25 output in one page, then depending on x will be the number of pages if x is 100 then we need 4 pages when the user press next he will to the next page, or if he press let us 3 he will jump to page 3.
Shall I make a full query, and put in an array and dsiplay it dynamically, or each time I need to make a query for each page.
Is there any example for that?
0
 
Avinash ZalaWeb ExpertCommented:
Ok, You want to use the pager.

Here is the link for that.

This will display page links based on the data.

http://pear.php.net/package/Pager/redirected

Hope this helps
Addy
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
BenMorelCommented:
Hi, let's say you the current page is $page (starting with 1), and the requested number of records is $records.
This code should suit your needs :
// these values should be taken from GET/POST
$page = 1;
$records = 10;

// compute the SQL start number
$start = ($page-1) * $records;

// fetch the records
$res = mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM table LIMIT $start, $records");

// get the total number of records in the table
list ($total) = mysql_fetch_row(mysql_unbuffered_query("SELECT FOUND_ROWS()"));

// compute the number of pages
// you will use this number to display links to other pages
$pages = (($total - 1) - (($total - 1) % $records)) / $records + 1;

// now you can fetch your records with mysql_fetch_*($res);

Open in new window

0
 
Mehul_PanchalCommented:
use LIMIT into the query. Like this:

$sql= 'SELECT * FROM table LIMIT 5';

will fetch only 5 first records.

$sql= 'SELECT * FROM table LIMIT 5 10';

will fetch 10 records starting from 5.

Thanks
Mehul Panchal
0
 
Ashraf-HassanAuthor Commented:
Thank you BenMorel, your code is very helpful
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now