Is there a better loop I could write to reduce database queries?

Below is some code I've written that is effective, but makes too many database queries. Is there a way I could optimize and reduce the number of queries but have conditional statements still be as effective as below?

I pasted the code repeated a few times just for good measure.

echo "<h3>Pool Packages</h3>";

    echo "<ul>";

    foreach ($items as $item):  

        $this->db->where('id', $item['id']);
        $query = $this->db->get('items')->row();

            if ($item['quantity'] > 1 && $item['quantity'] == TRUE && $query->category == "Pool Packages") {
                $newprice = $item['quantity'] * $query->price;                                  
                $totals[] = $newprice;  
            }
            else { 
                $newprice = $query->price;                  
                $totals[] = $newprice; 
            }

            if ($query->category == "Pool Packages") {                          
                echo "<li>" . $query->name . " (QTY: " . $item['quantity'] . " x = " . str_ireplace(" ", "", money_format('%(#10n', $newprice)) . ")</li>";         
            }

            else { }

    endforeach;

    echo "</ul>";

    echo "<h3>Water Features</h3>"; 

    echo "<ul>";

    foreach ($items as $item):  

        $this->db->where('id', $item['id']);
        $query = $this->db->get('items')->row();

            if ($item['quantity'] > 1 && $item['quantity'] == TRUE && $query->category == "Water Features") {
                $newprice = $item['quantity'] * $query->price;                                  
                $totals[] = $newprice;  
            }
            else { 
                $newprice = $query->price;                  
                $totals[] = $newprice; 
            }

            if ($query->category == "Water Features") {         
                echo "<li>" . $query->name . " (QTY: " . $item['quantity'] . " x = " . str_ireplace(" ", "", money_format('%(#10n', $newprice)) . ")</li>";
            }
            else { }



    endforeach;

    echo "</ul>";

    echo "<h3>Waterfall Rock Work</h3>";

    echo "<ul>";

    foreach ($items as $item):  

        $this->db->where('id', $item['id']);
        $query = $this->db->get('items')->row();

            if ($item['quantity'] > 1 && $item['quantity'] == TRUE) {
                    $newprice = $item['quantity'] * $query->price;                                  
                    $totals[] = $newprice;  
            }
            else { 
                $newprice = $query->price;                  
    开发者_开发问答            $totals[] = $newprice; 
            }

            if ($query->category == "Waterfall Rock Work") {            

                echo "<li>" . $query->name . " (QTY: " . $item['quantity'] . " x = " . str_ireplace(" ", "", money_format('%(#10n', $newprice)) . ")</li>";
            }

            else { }

    endforeach;

    echo "</ul>";

    echo "<h3>Sheer Descents</h3>";

    echo "<ul>";

    foreach ($items as $item):  

        $this->db->where('id', $item['id']);
        $query = $this->db->get('items')->row();

        if ($item['quantity'] > 1 && $item['quantity'] == TRUE && $query->category == "Sheer Descents") {
                $newprice = $item['quantity'] * $query->price;                                  
                $totals[] = $newprice;  
        }
        else { 
            $newprice = $query->price;                  
            $totals[] = $newprice; 
        }

        if ($query->category == "Sheer Descents") {         

            echo "<li>" . $query->name . " (QTY: " . $item['quantity'] . " x = " . str_ireplace(" ", "", money_format('%(#10n', $newprice)) . ")</li>";
        }

        else { }

    endforeach;

    echo "</ul>";

    echo "<h3>Booster Pump</h3>";

    echo "<ul>";

    foreach ($items as $item):  

        $this->db->where('id', $item['id']);
        $query = $this->db->get('items')->row();

        if ($item['quantity'] > 1 && $item['quantity'] == TRUE && $query->category == "Booster Pump") {
                $newprice = $item['quantity'] * $query->price;                                  
                $totals[] = $newprice;  
        }
        else { 
            $newprice = $query->price;                  
            $totals[] = $newprice; 
        }       

        if ($query->category == "Booster Pump") {

            echo "<li>" . $query->name . " (QTY: " . $item['quantity'] . " x = " . str_ireplace(" ", "", money_format('%(#10n', $newprice)) . ")</li>";
        }

        else { }

    endforeach;

    echo "</ul>";

    echo "<h3>Pool Concrete Decking</h3>";

    echo "<ul>";

    foreach ($items as $item):  

        $this->db->where('id', $item['id']);
        $query = $this->db->get('items')->row();

        if ($item['quantity'] > 1 && $item['quantity'] == TRUE && $query->category == "Pool Concrete Decking") {
                $newprice = $item['quantity'] * $query->price;                                  
                $totals[] = $newprice;  
        }
        else { 
            $newprice = $query->price;                  
            $totals[] = $newprice; 
        }           

        if ($query->category == "Pool Concrete Decking") {

            echo "<li>" . $query->name . " (QTY: " . $item['quantity'] . " x = " . str_ireplace(" ", "", money_format('%(#10n', $newprice)) . ")</li>";
        }

        else { }

    endforeach;

    echo "</ul>";

    echo "<h3>Solar Heating</h3>";

    echo "<ul>";

    foreach ($items as $item):  

        $this->db->where('id', $item['id']);
        $query = $this->db->get('items')->row();

        if ($item['quantity'] > 1 && $item['quantity'] == TRUE && $query->category == "Solar Heating") {
                $newprice = $item['quantity'] * $query->price;                                  
                $totals[] = $newprice;  
        }
        else { 
            $newprice = $query->price;                  
            $totals[] = $newprice; 
        }   

        if ($query->category == "Solar Heating") {

            echo "<li>" . $query->name . " (QTY: " . $item['quantity'] . " x = " . str_ireplace(" ", "", money_format('%(#10n', $newprice)) . ")</li>";
        }

        else { }

    endforeach;

    echo "</ul>";

    echo "<h3>Raised Bond Beam</h3>";

    echo "<ul>";

    foreach ($items as $item):  

        $this->db->where('id', $item['id']);
        $query = $this->db->get('items')->row();

        if ($item['quantity'] > 1 && $item['quantity'] == TRUE && $query->category == "Raised Bond Beam") {
                $newprice = $item['quantity'] * $query->price;                                  
                $totals[] = $newprice;  
        }
        else { 
            $newprice = $query->price;                  
            $totals[] = $newprice; 
        }

        if ($query->category == "Raised Bond Beam") {
            echo "<li>" . $query->name . " (QTY: " . $item['quantity'] . " x = " . str_ireplace(" ", "", money_format('%(#10n', $newprice)) . ")</li>";
        }

        else { echo "<li>None</li>"; }

    endforeach;

    echo "</ul>";

It goes on beyond this to several more categories, but I don't know how to handle looping through this best. Thanks!

You could build the html in a variable so you only loop once. Here's a quick and dirty example just to show you what I'm talking about:

$html = '';
$oldCat = '';
foreach ($items as $item) {  

    $this->db->where('id', $item['id']);
    $query = $this->db->get('items')->row();

    if ($oldCat != $query->category) {
        $html .= "</ul>\n";
        $html .= "<h3>".$query->category."</h3>\n<ul>\n";
        $oldCat = $query->category;
    }

    if ($item['quantity'] > 0) {
        $newprice = $item['quantity'] * $query->price;                                  
        $totals[] = $newprice;  
    }

    $html .= "<li>" . $query->name . " (QTY: " . $item['quantity'] . " x = " . str_ireplace(" ", "", money_format('%(#10n', $newprice)) . ")</li>\n";         

}
// strip leading /ul, append a /ul, echo html

You could store all the rows into a separate array during the first loop and then reference the array throughout all the other loops rather than fetching the same information over and over, assuming you're select * which you probably are.

Or, if there are not many items more than the ones you're fetching, you could use a single query to fetch all of the rows at once (you're using only one query) and loop through that to store all the values in an array $array[$row['id']] = $row (or something similar) then simply reference all those rows in the array in each of your loops.

You need to start thinking in terms of sets instead of loops. Write a stored proc that takes the array either as a varchar (or in SQL Server 2008 you can use a table valued input parameter, don't know about other dbs).

Then split the string into a temp table and return all the records in one select joining to the temp table. Even if you need to return separate record sets, doing it in a stored proc will reduce the network traffic in.

you should use a join from items to category and get all the items, then you can sort them out into a multi-dimensional array and then loop through that for output.

Im not sure what youre classes db connection is doing but but lets assume we want all items with thier category:

$sql = "SELECT item.*, category.name as category from item, category WHERE item.category_id = category.item_id";

// ill use PDO for db access here...
$db = new PDO($connString, $username, $password);
$items = array(); // our array indexed by category.

foreach($db->query($sql) as $item) {
  if(!array_key_exists($items, $item['category']) {
     $items[$item['category']] = array();
  }

  $items[$item['category']][] = $item;
}

// now loop through $items using the similar stuff you did for output previously.
// note instead of doing the conditionals for pricing and stuff here you may want to
// do that in the loop above and put it in the array before hand... it will keep the
// output loop cleaner.