Optimizing PHP while loop

Question

What I'm doing:

Retrieving available apartment listings and grouping them by layout - Studio, 1 Bedroom, 2 Bedroom, etc. Each layout lists the lowest and highest square footage, lowest rent, and then lists out all the individual units with details on each one. I attached an image to demonstrate what it looks like. floorplans

Problem

Currently, I am using multiple queries to grab the amound of beds and running while loops for each query. I would like this to be optimized and use only 1 query but am not sure where to begin.

Code

I am only showing the loops for the Studio and 1 Bedroom here, but I am doing the same thing for 2 and 3 bedroom queries. I would like to consolidate these all to one. Any help is appreciated.

$studioQuery = $mysqli->query('SELECT * FROM properties WHERE Beds = "0" AND PropertyId = '.$propID.' AND AvailableDate <> "" ORDER BY FloorplanName');
$oneBedQuery = $mysqli->query('SELECT * FROM properties WHERE Beds = "1" AND PropertyId = '.$propID.' AND AvailableDate <> "" ORDER BY FloorplanName');
$twoBedQuery = $mysqli->query('SELECT * FROM properties WHERE Beds = "2" AND PropertyId = '.$propID.' AND AvailableDate <> "" ORDER BY FloorplanName');
$threeBedQuery = $mysqli->query('SELECT * FROM properties WHERE Beds = "3" AND PropertyId = '.$propID.' AND AvailableDate <> "" ORDER BY FloorplanName');

// Studio
if($studioQuery->num_rows > 0) {
    echo '<div class="layout">';
    $studioCount = 0;
    $highSqFt = 0;
    $markupStudio = '';
    while ($row = mysqli_fetch_assoc($studioQuery)) {

        $aptID = $row['ApartmentId'];

        // Get the lowest square footage
        if($studioCount == 0){
            $lowSqFt = $row['SQFT'];
            $lowRent = $row['MinimumRent'];
        }
        if($lowSqFt > $row['SQFT']){
            $lowSqFt = $row['SQFT'];
        }
        // Get the highest square footage
        if($highSqFt < $row['SQFT']){
            $highSqFt = $row['SQFT'];
        }
        // Get the lowest Rent
        if($lowRent > $row['MinimumRent']){
            $lowRent = $row['MinimumRent'];
        }

        $markupStudio .= '<div class="row unit">';
        $markupStudio .= '<div class="col-sm-15">'. $row['Beds'] .' Bed / '. $row['Baths'] .' Bath</div>';
        $markupStudio .= '<div class="col-sm-15">'. $row['ApartmentName'] .'</div>';
        $markupStudio .= '<div class="col-sm-15">$'. $row['MinimumRent'] .'</div>';
        $markupStudio .= '<div class="col-sm-15">'. $row['AvailableDate'] .'</div>';
        $markupStudio .= '<div class="col-sm-15 apply-col"><a href="'. $row['ApplyOnlineURL'] .'" target="_blank">Apply ></a></div>';
        $markupStudio .= '</div>';

        $studioCount++;
    }
    ?>

    <div class="row floorplan-header">
        <div class="col-sm-3"><div><h3>Studio</h3></div></div>
        <div class="col-sm-3 sq-ft-col"><div><?php echo $lowSqFt .' - '. $highSqFt .' SQ. FT.'; ?></div></div>
        <div class="col-sm-3 from-price-col"><div>FROM $<?php echo $lowRent; ?></div></div>
        <div class="col-sm-3 available-col"><div>Check Availability <span class="icon-caret-right"></span></div></div>
    </div>

    <div class="row unit-list">
        <div class="col-lg-12">
            <div class="row unit-header">
                <div class="col-sm-15">Type</div>
                <div class="col-sm-15">Unit#</div>
                <div class="col-sm-15">Rent</div>
                <div class="col-sm-15">Available</div>
            </div>
            <?php echo $markupStudio; ?>
        </div>
    </div>

    <?php echo '</div>';
}

// 1 Bedroom
if($oneBedQuery->num_rows > 0) {
    echo '<div class="layout">';
    $oneBedCount = 0;
    $highSqFt = 0;
    $markupOne = '';
    while ($row = mysqli_fetch_assoc($oneBedQuery)) {

        $aptID = $row['ApartmentId'];

        // Get the lowest square footage
        if($oneBedCount == 0){
            $lowSqFt = $row['SQFT'];
            $lowRent = $row['MinimumRent'];
        }
        if($lowSqFt > $row['SQFT']){
            $lowSqFt = $row['SQFT'];
        }
        // Get the highest square footage
        if($highSqFt < $row['SQFT']){
            $highSqFt = $row['SQFT'];
        }
        // Get the lowest Rent
        if($lowRent > $row['MinimumRent']){
            $lowRent = $row['MinimumRent'];
        }

        $markupOne .= '<div class="row unit">';
        $markupOne .= '<div class="col-sm-15">'. $row['Beds'] .' Bed / '. $row['Baths'] .' Bath</div>';
        $markupOne .= '<div class="col-sm-15">'. $row['ApartmentName'] .'</div>';
        $markupOne .= '<div class="col-sm-15">$'. $row['MinimumRent'] .'</div>';
        $markupOne .= '<div class="col-sm-15">'. $row['AvailableDate'] .'</div>';
        $markupOne .= '<div class="col-sm-15 apply-col"><a href="'. $row['ApplyOnlineURL'] .'" target="_blank">Apply ></a></div>';
        $markupOne .= '</div>';

        $oneBedCount++;
    }
    ?>

    <div class="row floorplan-header">
        <div class="col-sm-3"><div><h3>1 Bedroom</h3></div></div>
        <div class="col-sm-3 sq-ft-col"><div><?php echo $lowSqFt .' - '. $highSqFt .' SQ. FT.'; ?></div></div>
        <div class="col-sm-3 from-price-col"><div>FROM $<?php echo $lowRent; ?></div></div>
        <div class="col-sm-3 available-col"><div>Check Availability <span class="icon-caret-right"></span></div></div>
    </div>

    <div class="row unit-list">
        <div class="col-lg-12">
            <div class="row unit-header">
                <div class="col-sm-15">Type</div>
                <div class="col-sm-15">Unit#</div>
                <div class="col-sm-15">Rent</div>
                <div class="col-sm-15">Available</div>
            </div>
            <?php echo $markupOne; ?>
        </div>
    </div>

    <?php echo '</div>';
}

/* Edit */

So now I have the one query but my logic is off in the while loop. It is now printing out the correct amount of Floorplan headers, but is only showing the first available unit under 1 Bedroom and then it's listing all of the available 1 Bedroom units under the 2 Bedroom header and only the first available 2 Bedroom unit. Below is the updated code.

$bedQuery = $mysqli->query('SELECT * FROM properties WHERE PropertyId = '.$propID.' AND AvailableDate <> "" ORDER BY Beds, FloorplanName');

// Floorplans
if($bedQuery->num_rows > 0) {

    $bedCount = 0;
    $highSqFt = 0;
    $markup = '';
    $previous = 0;

    while ($row = mysqli_fetch_assoc($bedQuery)) {

        $bedCount = $row['Beds'];
        $aptID = $row['ApartmentId'];

        // Get the lowest square footage
        if($previous == 0){
            $lowSqFt = $row['SQFT'];
            $lowRent = $row['MinimumRent'];
        }
        if($lowSqFt > $row['SQFT']){
            $lowSqFt = $row['SQFT'];
        }
        // Get the highest square footage
        if($highSqFt < $row['SQFT']){
            $highSqFt = $row['SQFT'];
        }
        // Get the lowest Rent
        if($lowRent > $row['MinimumRent']){
            $lowRent = $row['MinimumRent'];
        }

        $markup .= '<div class="row unit">';
        $markup .= '<div class="col-sm-15">'. $row['Beds'] .' Bed / '. $row['Baths'] .' Bath</div>';
        $markup .= '<div class="col-sm-15">'. $row['ApartmentName'] .'</div>';
        $markup .= '<div class="col-sm-15">$'. $row['MinimumRent'] .'</div>';
        $markup .= '<div class="col-sm-15">'. $row['AvailableDate'] .'</div>';
        $markup .= '<div class="col-sm-15 apply-col"><a href="'. $row['ApplyOnlineURL'] .'" target="_blank">Apply ></a></div>';
        $markup .= '</div>';

        if($bedCount != $previous) {
            //echo 'Beds: '.$bedCount.'</br>';
            //echo 'Previous: '.$previous;
            echo '<div class="layout">';
        ?>

        <div class="row floorplan-header">
            <div class="col-sm-3"><div><h3><?php echo $row['Beds']; ?> Bedroom</h3></div></div>
            <div class="col-sm-3 sq-ft-col"><div><?php echo $lowSqFt .' - '. $highSqFt .' SQ. FT.'; ?></div></div>
            <div class="col-sm-3 from-price-col"><div>FROM $<?php echo $lowRent; ?></div></div>
            <div class="col-sm-3 available-col"><div>Check Availability <span class="icon-caret-right"></span></div></div>
        </div>

        <div class="row unit-list">
            <div class="col-lg-12">
                <div class="row unit-header">
                    <div class="col-sm-15">Type</div>
                    <div class="col-sm-15">Unit#</div>
                    <div class="col-sm-15">Rent</div>
                    <div class="col-sm-15">Available</div>
                </div>
                <?php echo $markup; ?>
            </div>
        </div>

        <?php echo '</div>';
        }

        $previous = $bedCount;

    }

}

@developerwjk I dont think GROUP BY is of much use here because he wants all the individual entries he just wants them with a single query. — prodigitalson
– prodigitalson, Commented Feb 24, 2015 at 23:38

developerwjk · Accepted Answer · 2015-02-24 23:44:03Z

3

Your one query would result from using in() and adding Beds to the order by:

$studioQuery = $mysqli->query('SELECT * FROM properties WHERE Beds IN('0','1','2','3') AND PropertyId = '.$propID.' AND AvailableDate <> "" ORDER BY Beds, FloorplanName');

Now in the same query you will get all the rows you want, with each Bed number coming before the next, and in each Bed amount you will have it ordered by FloorplanName.

Now, simply as you loop through them, keep track of last Bed value and compare to current bed value. When they are no longer equal, print your new header for that section.

answered Feb 24, 2015 at 23:44

developerwjk

8,6342 gold badges20 silver badges33 bronze badges

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

5 Comments

prodigitalson Over a year ago

The IN isn't neccessary here... I think he only had the WHERE clause in order to divide them up into sections, now that he gets all the data at once it becomes unneeded. its the ORDER BY that makes the difference.

downtomike Over a year ago

Thanks for the reponses. I am now using a single query but my logic is off. I made an edit to the originial post to show the updated code.

developerwjk Over a year ago

@downtomike Before if($bedCount != $previous) { you forgot to set $bedCount = $rows['Bed'];

developerwjk Over a year ago

@downtomike And take out $bedCount++; That var should be set by $bedCount = $rows['Bed']; at the very top of the loop.

downtomike Over a year ago

Okay I am getting closer to finishing this. I updated my code example and explained what is happening now, but basically it is printing out the correct amount of headers but the listings under each aren't correct.

Steve · Accepted Answer · 2015-02-25 00:11:31Z

1

I would suggest making one query to select all the data, then save it into an array indexed on number of beds.

Then iterate the individual top level elements in place of your current while loops

$query = $mysqli->query('SELECT * FROM properties WHERE Beds < 4 AND PropertyId = '.$propID.' AND AvailableDate <> "" ORDER BY FloorplanName');

$data=[];

while ($row = mysqli_fetch_assoc($query))
    $data[$row['beds']][]=$row;

foreach($data[0] as $studioRow)
    //studio code


foreach($data[1] as $oneBedRow)
    //onebed code

This minimizes your refactoring and helps separate your presentation from your logic a little bit

answered Feb 25, 2015 at 0:11

Steve

20.5k5 gold badges47 silver badges71 bronze badges

Collectives™ on Stack Overflow

Optimizing PHP while loop

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related