Is there a better loop I could write to reduce database queries?
- by dmanexe
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!
