All geek questions in one place

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

Below is the code I wrote that is efficient but makes too many database queries. Is there a way that I could optimize and reduce the number of queries, but conditional statements are still effective, as shown below?

I pasted the code repeated several 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>";

This is beyond the scope of this few more categories, but I don’t know how to deal with this path. Thank!

+3
source share
4 answers

You can build html in a variable so that you only loop once. Here's a quick and dirty example 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
+1
source

, , , , *, , , .

, , , , ( ) , $array[$row['id']] = $row ( - ), .

0

, .

, db-, , , :

$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.
0

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

Then split the row into a temporary table and return all the records to one element that joins the temp table. Even if you need to return separate recordsets, doing this in a stored procedure will reduce network traffic.

0
source

Source: https://habr.com/ru/post/1739350/

More articles:


All Articles