Hello everyone. I have never really ever had a handle on nested loops and would like to optimize this piece of code. What I have works but I am not happy with it. The first query sets up the customer_id and the location_id for the rest of the loops. (Not a good way to handle this)
Here is my code:
<?php
require_once("db.php");
$db = new Database();
$rpthdr = $db->Query("SELECT t1.customer_id, t1.location_id, t1.location_name, t2.name FROM location AS t1 Inner Join email AS t2 ON t1.customer_id = t2.customer_id AND t1.location_id = t2.location_id Inner Join report AS t3 ON t1.customer_id = t3.customer_id AND t1.location_id = t3.location_id WHERE t3.stamp >= date_sub(now(), interval 1 day) GROUP BY t2.customer_id, t2.location_id");
foreach($rpthdr as $hdr)
{
$customer_id = $hdr['customer_id'];
$location_id = $hdr['location_id'];
echo $hdr['location_name']."<br>";
echo $hdr['name']."<br>";
$report1 = $db->Query("SELECT t3.report FROM location AS t1 Inner Join email AS t2 ON t1.customer_id = t2.customer_id AND t1.location_id = t2.location_id Inner Join report AS t3 ON t1.customer_id = t3.customer_id AND t1.location_id = t3.location_id WHERE t3.stamp >= date_sub(now(), interval 1 day) AND t1.customer_id = '$customer_id' AND t1.location_id = '$location_id' AND t3.report_type = 'report1'");
foreach($report1 as $rpt1)
{
echo "$rpt1[report]"."<br>";
}
$report2 = $db->Query("SELECT t3.report FROM location AS t1 Inner Join email AS t2 ON t1.customer_id = t2.customer_id AND t1.location_id = t2.location_id Inner Join report AS t3 ON t1.customer_id = t3.customer_id AND t1.location_id = t3.location_id WHERE t3.stamp >= date_sub(now(), interval 1 day) AND t1.customer_id = '$customer_id' AND t1.location_id = '$location_id' AND t3.report_type = 'report2'");
foreach($report2 as $rpt2)
{
echo "$rpt2[report]"."<br>";
}
$report3 = $db->Query("SELECT t3.report FROM location AS t1 Inner Join email AS t2 ON t1.customer_id = t2.customer_id AND t1.location_id = t2.location_id Inner Join report AS t3 ON t1.customer_id = t3.customer_id AND t1.location_id = t3.location_id WHERE t3.stamp >= date_sub(now(), interval 1 day) AND t1.customer_id = '$customer_id' AND t1.location_id = '$location_id' AND t3.report_type = 'report3'");
foreach($report3 as $rpt3)
{
echo "$rpt3[report]"."<br>";
}
}
?>I have a plain old html page that I wanted to use str_replace on to populate these database fields. I couldn't seem to get that working though. I then decided to try and procedurally write code that would at least give me my data in the format I expect. That is the code above.
Ideally, I would like to have a small foreach and or while loop to handle this and then use str_replace. Failing that, I will have to incorporate the html into this code.
Also, please notice that the sql statements for report1, 2 and 3 are identical with exception of the "report_type in the WHERE clause". It seems to me that there has to be a much better way to do this.
Can someone please help???
Thanks. Tom
