pagination when coming from a Form and dropdown menus
Posted: Sun May 24, 2009 8:12 am
What I want to do is the most common existing thing ever. I need to paginate the results that come from a SELECT ..but, hold it, not so easy. If it were a SELECT that with one goes fetches them all yes, it would be easy. Here is why it is difficult:
I HAVE:
a form with two dropdown menus from which the user may multiple select. Say country and activity.
This is passed via [] to the array and fetched by the $_POST variable (and then I pass it from there to a $_SESSION variable). I have actually both the form and the processing on the same single page, and activate the processing via if ($_POST['submitted']){
I get the rows displayed alright, but if I want to paginate because I get too many rows:
I HAVE THIS PROBLEM:
when I do the sql ($q) query and try to limit the results so as
LIMIT $start, $display
that, of course, will not work because $q does not fetch all of the results at once, but one by one, because it is in a loop, so number of records per loop = 1.
$q= "SELECT id,actividad, region, provider
FROM thetable.objetonegocio, thetable.abact, thetable.abpa
WHERE abact.abreviacion = '{$_SESSION['actividad'][$i]}'//THIS COMES FROM THE FORM
AND abpa.abreviacion = '{$_SESSION['pais'][$j]}'
..
LIMIT $start, $display
as you see, per every loop, those variables fetch a different value which yields, out of the table, a different row, but it is always one by one.
Therefore, the $display never works, because the number of results per every loop, 1 as I said is never bigger than whatever $display was set to. Therefore, it displays all rows.
then it follows $r = @mysqli_query ($dbc, $q)
or die("Error: ".mysqli_error($dbc));
and then
while ($row =mysqli_fetch_array($r, MYSQL_ASSOC)){ etc
I mean, it would be easy to paginate if I could get all the results by a single SQL select query, because there it is easy to make $start and $display work, but since the sql query is embedded within a loop that fetches 1 by 1, it cant get all at once.
Anybody knows how to paginate when fetching from forms through loops?
I HAVE:
a form with two dropdown menus from which the user may multiple select. Say country and activity.
This is passed via [] to the array and fetched by the $_POST variable (and then I pass it from there to a $_SESSION variable). I have actually both the form and the processing on the same single page, and activate the processing via if ($_POST['submitted']){
I get the rows displayed alright, but if I want to paginate because I get too many rows:
I HAVE THIS PROBLEM:
when I do the sql ($q) query and try to limit the results so as
LIMIT $start, $display
that, of course, will not work because $q does not fetch all of the results at once, but one by one, because it is in a loop, so number of records per loop = 1.
$q= "SELECT id,actividad, region, provider
FROM thetable.objetonegocio, thetable.abact, thetable.abpa
WHERE abact.abreviacion = '{$_SESSION['actividad'][$i]}'//THIS COMES FROM THE FORM
AND abpa.abreviacion = '{$_SESSION['pais'][$j]}'
..
LIMIT $start, $display
as you see, per every loop, those variables fetch a different value which yields, out of the table, a different row, but it is always one by one.
Therefore, the $display never works, because the number of results per every loop, 1 as I said is never bigger than whatever $display was set to. Therefore, it displays all rows.
then it follows $r = @mysqli_query ($dbc, $q)
or die("Error: ".mysqli_error($dbc));
and then
while ($row =mysqli_fetch_array($r, MYSQL_ASSOC)){ etc
I mean, it would be easy to paginate if I could get all the results by a single SQL select query, because there it is easy to make $start and $display work, but since the sql query is embedded within a loop that fetches 1 by 1, it cant get all at once.
Anybody knows how to paginate when fetching from forms through loops?