我有一个mysql查询,它选择数据并使用
COUNT(CASE WHEN)
作用我现在想完成的是获得所有这些字段中的一些字段,看看总数是否为
> 0
或
= 0
。我只是不确定我是否可以在同一个MySQL查询中做到这一点,或者在循环返回数据时是否应该通过PHP获得总和。
以下是我的问题,在此之后,我认为这些想法可能会奏效,但如果有人能向我展示一种更好/更高效/更清洁的方法来获得同样的结果,我将不胜感激。
foreach ($iconArray as $icon) {
$query = "
SELECT teacher_type, link_url, link_title,
COUNT(CASE WHEN icon_type = '{$icon}' AND row_value = 1 AND link_column = 1 THEN 1 END) AS location1,
COUNT(CASE WHEN icon_type = '{$icon}' AND row_value = 1 AND link_column = 2 THEN 1 END) AS location2,
COUNT(CASE WHEN icon_type = '{$icon}' AND row_value = 1 AND link_column = 3 THEN 1 END) AS location3,
COUNT(CASE WHEN icon_type = '{$icon}' AND row_value = 1 AND link_column = 4 THEN 1 END) AS location4,
COUNT(CASE WHEN icon_type = '{$icon}' AND row_value = 2 AND link_column = 1 THEN 1 END) AS location5,
COUNT(CASE WHEN icon_type = '{$icon}' AND row_value = 2 AND link_column = 2 THEN 1 END) AS location6,
COUNT(CASE WHEN icon_type = '{$icon}' AND row_value = 2 AND link_column = 3 THEN 1 END) AS location7
FROM ww_click_tracking
WHERE click_date BETWEEN {$date_clause}
AND teacher_type = {$value}";
$result = mysqli_query($link, $query);
//HERE IS WHERE I WANT TO CHECK IF ALL OF THE LOCATIONS LOCATION 1-7 ADDED TOGETHER ARE GREATER THAN 0. IF NOT THERE IS NO REASON TO CONTINUE ON.
for ($i=1,$i <=7, $i++) {
$locationTotal += $result['location'. $i];
}
if ($locationTotal > 0){
//SOMETHING TO ACTUALLY REPORT SO CONTINUE DOING YOUR THANG!
while($row = mysqli_fetch_array($result)){
}
//ELSE 0 THEN CURRENT ICON HAD NO CLICKS TO REPORT.
}
} //END OF FOREACH ICON LOOP
