I build an array and then foreach array item I perform a large query. I need to write this into 1 query but don't know how. The problem is this page is taking a long time to load because it is performing 1 query, then its performing another query for each result of the first query via a foreach loop.
Heres the code:
$arr_websites = array();
$sql = mysql_query("select `website` from `leads` group by `website` asc");
while ($row = mysql_fetch_assoc($sql))
{
if (!empty($row['website']))
$arr_websites[] = $row['website'];
}
foreach ($arr_websites as $key => $ws)
{
$sql = mysql_query("select
l.website,
sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now(), '%Y-%m-%d') then 1 else 0 end) AS c_day,
sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now() - interval 1 day, '%Y-%m-%d') then 1 else 0 end) AS c_yesterday,
sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now() - interval 2 day, '%Y-%m-%d') then 1 else 0 end) AS c_2_days,
sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now() - interval 3 day, '%Y-%m-%d') then 1 else 0 end) AS c_3_days,
sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now() - interval 4 day, '%Y-%m-%d') then 1 else 0 end) AS c_4_days,
sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now() - interval 5 day, '%Y-%m-%d') then 1 else 0 end) AS c_5_days,
sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now() - interval 6 day, '%Y-%m-%d') then 1 else 0 end) AS c_6_days,
sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now() - interval 7 day, '%Y-%m-%d') then 1 else 0 end) AS c_7_days,
sum(case when YEARWEEK(FROM_UNIXTIME(COALESCE(a.date_assigned, l.date_added))) = YEARWEEK(CURDATE()) then 1 else 0 end) AS c_week,
sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m')= date_format(now(), '%Y-%m') then 1 else 0 end) AS c_month,
sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m')= date_format(now() - interval 1 month, '%Y-%m') then 1 else 0 end) AS c_last_month,
sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m')= date_format(now() - interval 2 month, '%Y-%m') then 1 else 0 end) AS c_2_months,
sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m')= date_format(now() - interval 3 month, '%Y-%m') then 1 else 0 end) AS c_3_months,
sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y')= date_format(now(), '%Y') then 1 else 0 end) AS c_year
from `leads` as l
left join `assignments` as a on (a.id_lead = l.id)
left join `dealerships` as d on (d.id = a.id_dealership)
where a.is_reassign='no' and a.id_dealership!='65' and a.id_dealership!='77' and a.id_dealership!='89' and a.id_dealership!='138' and (a.website='$ws' or l.website='$ws')
") or die(mysql_error());
}
I don't know how to get this to have the correct sums. The problem is that the "leads" table and then "assignments" table both have a "website" field, sometimes the website field may be empty in one or the other which is why you see the code and (a.website='$ws' or l.website='$ws'), where $ws is the result of the first query that is being looped in the foreach for the second query. The assignments have an id_lead field which links the assignments to a lead.
Heres some things I've tried which makes the page faster, but does not produce the correct sum's:
- i've tried grouping by l.website
- i've tried a left join with the code left join assignments as a on (a.id_lead = l.id AND a.website=l.website)
I hope this makes sense, any help would be greatly appreciated.
