Generating an html report from an sql query.

Question

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.

Answer 1

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.

Answer 2

-- 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.

Answer 3

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.