我有2个搜索查询-一个将显示最近7天的内容.另一个将显示2周前的内容.
两者都很好.但是我想从第一个查询中获取结果,并从第二个查询中获取差异.然后显示带有差异的第一个查询.
$result_account = $db->query("
SELECT nid,COUNT(cat) AS qty,dte,descript,cat,name,user
FROM client_note AS cn
JOIN client_note_tag_items AS cnti
ON cnti.note_id = cn.nid
JOIN client_note_tags AS cnt
ON cnt.tag_id = cnti.tag_id
WHERE dte >= DATE_SUB(CURDATE(),INTERVAL 7 DAY)
AND name NOT LIKE 'Resolution%'
GROUP
BY cat
ORDER
BY qty DESC
LIMIT 5
");
if($count_account = $result_account->num_rows) {
while($row = $result_account->fetch_object()){
echo "<tr>";
echo "<td><h6>".$row->cat."</h6></td><td><h3 class='text-primary'>".$row->qty."</h3></td>";
echo "</tr>";
}
}
$result_prevIoUs = $db->query("SELECT nid,user FROM client_note AS cn JOIN client_note_tag_items AS cnti ON cnti.note_id = cn.nid JOIN client_note_tags AS cnt ON cnt.tag_id = cnti.tag_id WHERE (dte BETWEEN DATE_SUB(CURDATE(),INTERVAL 21 DAY) AND DATE_SUB(CURDATE(),INTERVAL 14 DAY)) AND name NOT LIKE 'Resolution%' GROUP BY cat ORDER BY qty DESC LIMIT 5");
if($count_prevIoUs = $result_prevIoUs->num_rows) {
while($row_p = $result_prevIoUs->fetch_object()){
echo "<tr>";
echo "<td><h6>".$row_p->cat."</h6></td><td><h3 class='text-primary'>".$row_p->qty."</h3></td>";
echo "</tr>";
}
}
第一个查询将导致：
@H_301_6@Category - Qty
Baseball - 45
Football - 33
Soccer - 21
Hockey - 7
Basketball - 3
第二个查询将导致：
@H_301_6@Category - Qty
Basketball - 38
Soccer - 28
Hockey - 16
Football - 12
Baseball - 12
现在我要这样显示
@H_301_6@Category - Qty Difference
Baseball - 45 +33
Football - 33 +21
Soccer - 21 -7
Hockey - 7 -9
Basketball - 3 -35
最佳答案
我要做的是创建另一个数组,该数组在键-值的基础上保存来自第一个查询的值,而在下一个查询中,仅从数组中相应的条目中减去值.
注意：我没有测试此代码.
注意2：我假设第一类运动缺失将被计为0
注意3：我编写的代码仅用于显示计算差异的方法.让我知道是否应该更新它,以提供您在那里所需的完全相同的输出.
@H_301_6@$results = array();
$result_account = $db->query("SELECT nid,user FROM client_note AS cn JOIN client_note_tag_items AS cnti ON cnti.note_id = cn.nid JOIN client_note_tags AS cnt ON cnt.tag_id = cnti.tag_id WHERE `dte` >= DATE_SUB(CURDATE(),INTERVAL 7 DAY) AND name NOT LIKE 'Resolution%' GROUP BY cat ORDER BY qty DESC LIMIT 5");
if($count_account = $result_account->num_rows) {
while($row = $result_account->fetch_object()){
//output this query results here
$results[$row->cat] = $row->qty;
}
}
$result_prevIoUs = $db->query("SELECT nid,INTERVAL 14 DAY)) AND name NOT LIKE 'Resolution%' GROUP BY cat ORDER BY qty DESC LIMIT 5");
if($count_prevIoUs = $result_prevIoUs->num_rows) {
while($row_p = $result_prevIoUs->fetch_object()){
//output this query results here
$results[$row_p->cat] = ((isset($results[$row_p->cat])) ? $results[$row_p->cat] : 0 ) - $row_p->qty;
}
}
foreach( $results as $key => $result) {
echo "<tr>";
echo "<td><h6>".$key."</h6></td><td><h3 class='text-primary'>".$result."</h3></td>";
echo "</tr>";
}
更新-显示第一周及其旁边的差异
你可以试试看我摆脱了foreach,仅使用查询循环来完成所有操作.
注意：同样,这是未经测试的代码
@H_301_6@$results = array();
$result_account = $db->query("SELECT nid,INTERVAL 7 DAY) AND name NOT LIKE 'Resolution%' GROUP BY cat ORDER BY qty DESC LIMIT 5");
$result_prevIoUs = $db->query("SELECT nid,INTERVAL 14 DAY)) AND name NOT LIKE 'Resolution%' GROUP BY cat ORDER BY qty DESC");
if($count_prevIoUs = $result_prevIoUs->num_rows) {
while($row_p = $result_prevIoUs->fetch_object()){
//output this query results here
$results[$row_p->cat] = $row_p->qty;
}
}
if($count_account = $result_account->num_rows) {
while($row = $result_account->fetch_object()){
$difference = $row->qty - ((isset($results[$row->cat])) ? $results[$row->cat] : 0 );
echo "<tr>";
echo "<td><h6>".$row->cat."</h6></td><td><h3 class='text-primary'>".$row->qty."</h3></td><td><h3 class='text-primary'>".$difference."</h3></td>";
echo "</tr>";
}
}