>谁能帮我减少这段代码。
$dat= mysql_query
("SELECT wid,count(*) as count
FROM uptime
WHERE time_stamp>=DATE_SUB(NOW(),INTERVAL 10 MINUTE)
AND status<>200 GROUP BY wid ORDER BY time_stamp ASC"
);
while($ans= mysql_fetch_assoc($dat))
{
$count_array[]=$ans;
}
foreach ($count_array as $value)
{
if($value['count']==2)
{
$id=$value['wid'];
$add= mysql_query("SELECT email FROM website WHERE webid='".$id."'");
$result= mysql_fetch_assoc($add);
<--etc etc to send email-->
}
}
我需要减少代码请帮助我,如果两个查询可以组合而不失去意义也会对我有帮助。
这是两个表之间的简单连接:
select
upity.wid,
webit.email,
count(*) as count
from
uptime upity
join website webby
on upity.wid=webby.webid
where
upity.time_stamp>=DATE_SUB(NOW(),INTERVAL 10 MINUTE)
and upity.status<>200
group by
upity.wid,
webit.email
having count=2
我写了一个关于在SQL中连接表的非常深入的问答,它将更详细地解释这一切是如何工作的。事实上,我写它正是出于这个原因 - 能够帮助快速查询,然后能够链接到正在发生的事情的详细说明。
编辑:根据Zerkms和我之间的评论,如果您使用此代码,请查看执行时间。大型表在联接时可能会导致非常大的数据集,并且运行多个查询确实可能更有效。
$dat= mysql_query("SELECT wid,count(*) as count
FROM uptime WHERE time_stamp>=DATE_SUB(NOW(),INTERVAL 10 MINUTE) AND status<>200 GROUP BY wid ORDER BY time_stamp ASC");
while($ans= mysql_fetch_assoc($dat)){
if($ans['count']==2){
$id=$value['wid'];
$add= mysql_query("SELECT email FROM website WHERE webid='".$id."'");
$result= mysql_fetch_assoc($add);
<--etc etc to send email-->
}
}
试试这个:
$dat = mysql_query("
SELECT
ut.wid,ut.count(*) as count,ws.email
FROM
uptime as ut
INNER JOIN
website as ws
ON
ut.id=ws.webid
WHERE
ut.time_stamp>=DATE_SUB(NOW(),INTERVAL 10 MINUTE) AND ut.status<>200 AND ut.count=2
GROUP BY
ut.wid
ORDER BY
ut.time_stamp ASC");