php - 我可以编写更好的循环来减少数据库查询吗？

Question

下面是我写的一些有效的代码，但是进行了太多的数据库查询。有没有一种方法可以优化和减少查询数量，但条件语句仍然像下面一样有效？

为了更好的衡量，我重复粘贴了几次代码。

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>";

除此之外还有更多类别，但我不知道如何最好地处理循环。谢谢!

Answer 1

您可以在变量中构建 html，这样您只循环一次。这是一个简单粗暴的例子，只是为了向您展示我在说什么:

$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