how to do 2 php while loops with 1 mysql query?

Question

is it possible to run only one query using php & mysql and then do two while loops with different results. i

//this is the query
$qry_offers = mysql_query("SELECT * FROM offers ORDER BY offer_end ASC");

in the first loop, i want to show any result which has an "end_date" less or equal than today

<div id="current">
<?
while($row_offers = mysql_fetch_assoc($qry_offers)) {
    if ( (date("Y-m-d H:i:s")) <= ($row_offers['offer_end']) ) {
        echo '<li><a href="#">'.$row_offers['offer_name'].'</a></li>';
    }
}
?>
</div><!-- END OF CURRENT -->

in the second loop, i want to show any result which has an "end_date" greater than today

//this is where i would have the 2n while loop
<div id="inactive">
<?
   while($row_offers = mysql_fetch_assoc($qry_offers)) {
    if ( (date("Y-m-d H:i:s")) > ($row_offers['offer_end']) ) {
        echo '<li><a href="#">'.$row_offers['offer_name'].'</a></li>';
    }
}
?>
</div><!-- END OF INACTIVE -->

@bretterer: yes, i tried it and i only get results for the 1st loop. the second is blank — Azukah
– Azukah, Commented Sep 5, 2012 at 2:04
use mysql_field_seek($qry_offers,0) before second loop but I prefer Ansrew's answer! — Hassan Khodadadeh
– Hassan Khodadadeh, Commented Sep 5, 2012 at 2:16

staticsan · Accepted Answer · 2012-09-05 02:13:58Z

5

The solution is to save the results from each loop and then put them together at the end.

$offers_current = array();
$offers_inactive = array();

while($row_offers = mysql_fetch_assoc($qry_offers)) {
    if ( (date("Y-m-d H:i:s")) <= ($row_offers['offer_end']) )
        $offers_current[] = '<li><a href="#">'.$row_offers['offer_name'].'</a></li>';
    if ( (date("Y-m-d H:i:s")) > ($row_offers['offer_end']) )
        $offers_inactive[] = '<li><a href="#">'.$row_offers['offer_name'].'</a></li>';
}

?>
<div id="current">
    <ul>
    <?php echo implode("\n", $offers_current) ?>
    </ul>
</div>
<div id="inactive">
    <ul>
    <?php echo implode("\n", $offers_inactive) ?>
    </ul>
</div>

answered Sep 5, 2012 at 2:13

staticsan

30.6k5 gold badges64 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tony Over a year ago

+1 for a single pass through the dataset. Could also replace the second if with an else now that they are right next to each other.

staticsan Over a year ago

@Tony Not using else provides a slightly more generic solution, though the truth is I didn't see it. One of the most common mistakes I see in PHP is passing around huge data structures willy-nilly, like a resultset. It is just so much more scalable to process a resultset as you read it and I want to encourage that.

Andrew Jackman · Accepted Answer · 2012-09-05 02:06:47Z

4

You could do this:

$itemsArray = mysql_fetch_assoc($qry_offers);
foreach ( $itemsArray as $row_offers ) {
    // ... Code in here for less than 'offer_end'
}
foreach ( $itemsArray as $row_offers ) {
    // ... Code in here for greater than 'offer_end'
}

answered Sep 5, 2012 at 2:06

Andrew Jackman

14k8 gold badges38 silver badges44 bronze badges

Collectives™ on Stack Overflow

how to do 2 php while loops with 1 mysql query?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related