I have an sql query that returns a lot of results for which I want to generate an html table to display them. Problem is I don't want to display them all on one page, I want to grab 10 at a time and flip through pages of results.
I want to return 100 results for each query but I can't figure out how to get THE NEXT 100 on the next query.
-- return the first 100 rows
SELECT * FROM table LIMIT 0, 100
-- return the next 100 rows
SELECT * FROM table LIMIT 100, 100
-- and the next
SELECT * FROM table LIMIT 200, 100
What you need to do is pass a variable to your script that will create the proper SQL LIMIT statement for each page.
You would define at the bottom a Limit. For the first page:
LIMIT 0,100
Second Page
LIMIT 100,100
and so on.
When you go to put the 'Next' link on the page, make a $_GET parameter that says start=100. Then, use that start parameter as the first value in limit, and add 100 to it to get the second value.
So, in the end it would look like:
if(empty($_GET['start']))
{
$start = 0;
}
else
{
$start = $_GET['start'];
}
$SQL = "SELECT * FROM TABLE WHERE 1=1 LIMIT ".$start.",100;";
query($sql);
$link = "<a href=\"?start=".$start+100."\">Next</a>";
If you wanted to expand upon that further, you could add a num parameter. That would allow for users to control how many records they see. So:
if(empty($_GET['start']))
{
$start = 0;
}
else
{
$start = $_GET['start'];
}
if(empty($_GET['num']))
{
$start = 100;
}
else
{
$start = $_GET['num'];
}
$SQL = "SELECT * FROM TABLE WHERE 1=1 LIMIT ".$start.",".$num.";";
query($sql);
$link = "<a href=\"?start=".$start+100."&num=".$num."\">Next</a>";
Of course, you would want to sanatize/validate all those numbers.
You will need to use the LIMIT sql statement. Using a query like SELECT * FROM table LIMIT $var,100 then have the url as /page.ext?page=2. use get (i.e, $_GET['page'] in php), and multiply that number by 100, to get the $var value. Obviously, you would need to make this secure.
