pagination when coming from a Form and dropdown menus

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
straightman
Forum Commoner
Posts: 48
Joined: Sun Apr 19, 2009 5:20 am

pagination when coming from a Form and dropdown menus

Post by straightman »

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?
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: pagination when coming from a Form and dropdown menus

Post by allspiritseve »

Sorry, why are you making queries inside of a loop? I'm not understanding that bit.
User avatar
mikemike
Forum Contributor
Posts: 355
Joined: Sun May 24, 2009 5:37 pm
Location: Chester, UK

Re: pagination when coming from a Form and dropdown menus

Post by mikemike »

You're having all this trouble because you haven't done your SQL correctly. There are very very few cases where you'd need to query inside a loop like that, and I don't think yours is one of them.

If you show us all your queries we might be able to help you condense them into one.
alvaroe
Forum Newbie
Posts: 1
Joined: Tue Jul 29, 2008 2:22 pm

Re: pagination when coming from a Form and dropdown menus

Post by alvaroe »

alright guys, thank you. I have not thought of that. I believe you have smartly hit the key of the problem. That is actually what I was doing, a query inside a loop, that is, everytime the loop run, it would to a query. That means I have to rethink my whole design. here is anyway what I had. You would not need to go through the whole of the design because you rightly intuited that there was something "weird" with the design. In fact, because of that design, i was having to do workarounds when it came to knowing the total number of records because i would need that in order to tell how many pages depending on the $display per page variable. So I sort of put a counter that would add up all of the loops because the num_rows would again always give one if the query was within the loop.

In any case, I would still have to think how to do what you proposed. That is, the query out of the loop...

this is here is the last items from one of the dropdown menus...

....
<option value="lu">Rumania</option>
<option value="se">Suecia</option>
<option value="ch">Suiza</option>
</select></td>
</tr>

</table>

</td>

<tr>
<td valign="bottom">
<input name="Enviar" type="submit">
<input type="hidden" name="sid" value="<?=session_id()?>">
<input type="hidden" name="submitted" value="TRUE">


</tr>

<tr>
<td>
<?php
if ($_POST['submitted']){
$_SESSION['actividad'] = $_POST['actividad'];
$_SESSION['pais'] = $_POST['pais'];

echo '<table border="1" cellspacing="3" cellpadding="3" align= "left">
<th align="left">Link</th><th align="left">Actividad</th><th align="left">Region</th><th align="left">Provider</th>';

}?>

</td>
</tr>
</form>
</div>

<?php

//CONNECT TO THE DATABASE ---------------------------------------

require_once ('../mysqli_connect_europe.php');

$display = 2;
$start = 0;
for($i=0;$i<count($_SESSION['actividad']);$i++){
$_SESSION['actividad'][$i];
for($j=0;$j<count($_SESSION['pais']);$j++){
$_SESSION['pais'][$j];



// ---------------------------------------------------------------
// WRITE THE QUERY ONLY

$q= "SELECT ida,actividad, region, provider
FROM funeurope.objetonegocio, funeurope.abact, funeurope.abpa
WHERE abact.abreviacion = '{$_SESSION['actividad'][$i]}'
AND abpa.abreviacion = '{$_SESSION['pais'][$j]}'
AND objetonegocio.abreviacionactividad = abact.abreviacion
AND abpa.abreviacion = objetonegocio.abreviacionpais
LIMIT $start, $display
";
// ---------------------------------------------------------------

// ACTUALLY RUN THE QUERY ----------------------------------------

$r = @mysqli_query ($dbc, $q)
or die("Error: ".mysqli_error($dbc));





// --------------------------------------------------------------


$num = mysqli_num_rows($r); //this was a workaround i had to do in order to get the actual number of records in time
$suma += $num;
echo '<p>';
echo '</p>';

while ($row =mysqli_fetch_array($r, MYSQL_ASSOC)){

// STARTING A LOOP THAT CREATES ROWS AND CELLS AND PUTS IN THE RESULTS OF THE DB QUERY


echo '<tr align="left">';
echo '<td align="left">' . "<a href='". "details.php?ida=" . urlencode($row['ida']). "&sid= " . session_id() . "'>linktext</a>". "</td>";
echo '<td align="left">' .htmlspecialchars($row['actividad']). "</td>";
echo '<td align="left">' .htmlspecialchars($row['region']). "</td>";
echo "<td>" .htmlspecialchars($row['provider']). "</a>" . "</td>";

} // CLOSING THIS LOOP THAT CREATES ROWS AND CELLS.

echo "</tr>";

} // CLOSING THE INNER LOOP, THE ONE FOR PAIS


}



//CLOSING THE OUTER LOOP, THE ONE FOR ACTIVIDAD

if ($_POST['submitted']){
if ($suma == 0) {
echo '<p class="error"> There are no records for this query
You may want to try another combination.</p>';
exit();
}


echo 'a total of ' . $suma . ' records have been found';
$display = 2;
}



// ONCE WE KNOW HOW MANY RECORDS WE HAVE, WE ARE GOING TO TRY TO LIMIT THE DISPLAY PER PAGE


// DETERMINE WHERE IN THE DB TO START RETURNING RESULTS

if(isset($_GET['s']) && is_numeric($_GET['s'])){

$start = $_GET['s'];
}else {

$start = 0;
}


// DETERMINE THE NUMBER OF PAGES. THIS IS SENT BY THE URL OF THE DISPLAY OF PAGES. NOT BY ANY SUBMIT BUTTON

if(isset($_GET['p']) && is_numeric($_GET['p'])){
$pages = $_GET['p'];
}

else {
if ($suma > $display){
$pages = ceil ($suma/$display);

}
else{
$pages = 1;
}

}


// ---------------------------------------------------------------






echo '<p>';
echo '</p>';
echo '</table>';

// CLOSING DB CONNECTION

mysqli_free_result($r);
mysqli_close($dbc);


if ($pages > 1){
echo '<br>';
echo '</br>';
echo '<p>';

$current_page = ($start/$display) + 1;

//IF IT IS NOT THE FIRST PAGE. HERE IS THE URL THAT SENDS THE VALUES OF S AND P FOR RECALCULATION

if($current_page != 1){
echo '<a href="formandprocessor4.php?s=' . ($start - $display) . '&p=' . $pages . '">Previous</a>';
}

// MAKE ALL THE NUMBERED PAGES

for($i = 1; $i <= $pages; $i ++){

if ($i != $current_page) {
echo '<a href ="formandprocessor4.php?s=' . (($display * ($i - 1))) . '&p=' . $pages . '">' . $i . '</a>';

} else {

echo $i . '';
}


} // END OF FOR LOOP

// IF IT IS NOT THE LAST PAGE, MAKE A NEXT BUTTON

if ($current_page != $pages){
echo '<a href="formandprocessor4.php?s=' . ($start + $display) . '&p=' . $pages . '">Next</a>';

}



}
?>





mikemike wrote:You're having all this trouble because you haven't done your SQL correctly. There are very very few cases where you'd need to query inside a loop like that, and I don't think yours is one of them.

If you show us all your queries we might be able to help you condense them into one.
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: pagination when coming from a Form and dropdown menus

Post by allspiritseve »

First, a couple of suggestions: Your code would be a lot cleaner if you separated most of your PHP code into a php block at the beginning of the page, and had the bare minimum amount of php needed to display embedded in HTML. Also, the loop that wraps your query is a for loop, and it looks like you're jumping through hoops to get that to work. What you want is a foreach loop.

Now, I'm just taking a guess here, but are you using multiple selects, so the user can select multiple activities and countries at once? And then you're querying for all objetonegocios (business objects?) from the database that meet any combination of those activities and countries? If so, you may try using IN() for your WHERE clause and combining your activities and countries with a comma. For example:

Code: Select all

SELECT * FROM table WHERE abreviacion IN ('actividad1', 'actividad2', 'actividad3') AND abreviacion IN ('pais1', 'pais2', 'pais3')
straightman
Forum Commoner
Posts: 48
Joined: Sun Apr 19, 2009 5:20 am

Re: pagination when coming from a Form and dropdown menus

Post by straightman »

Yes, allspiritseve, your intuition is right. I am using multiple select so that the user can select at once say, snowboarding and and hiking in USA and Canada or any combination of countries. And yes, correct again, I am querying the database for those that meet that combination of activities and countries. I will gather all your well thought suggestions people and try to re-work out my code again. I am going to have to refreshen my sql knowledge as I had almost forgotten about the IN feature.

thank you all very much

Alvaro

=======================================================================================================


allspiritseve wrote:First, a couple of suggestions: Your code would be a lot cleaner if you separated most of your PHP code into a php block at the beginning of the page, and had the bare minimum amount of php needed to display embedded in HTML. Also, the loop that wraps your query is a for loop, and it looks like you're jumping through hoops to get that to work. What you want is a foreach loop.

Now, I'm just taking a guess here, but are you using multiple selects, so the user can select multiple activities and countries at once? And then you're querying for all objetonegocios (business objects?) from the database that meet any combination of those activities and countries? If so, you may try using IN() for your WHERE clause and combining your activities and countries with a comma. For example:

Code: Select all

SELECT * FROM table WHERE abreviacion IN ('actividad1', 'actividad2', 'actividad3') AND abreviacion IN ('pais1', 'pais2', 'pais3')
Post Reply