<?php
//db connection goes here
$arr=array('12:30:00','01:30:01','02:30:01','03:30:01','04:30:01','05:30:01','06:30:01','07:30:01');
$arr1=array('01:30:00','02:30:00','03:30:00','04:30:00','05:30:00','06:30:00','07:30:00','08:30:00');
$cnt=count($arr);
for($i=0;$i<$cnt;$i++){
$sql="SELECT count(*) FROM report WHERE DATE_FORMAT(dt,'%H:%m:%i') BETWEEN $arr[$i] AND $arr1[$i]";
}
//fetching in while and echo
这是我正在尝试的脚本，它将从两个不同的表report和report1生成报告计数用户数量和应用程序数量。报告将像
Time count Logged In user Count-Apps
12:30:00-01:30:00
01:30:01-02:30:00
02:30:01 03:30:00
03:30:01-04:30:00
04:30:01-05:30:00
05:30:01-06:30:00
06:30:01-07:30:00
07:30:01-08:30:00
08:30:01-09:30:00
report用户计数表
user datetimeuser(datetime)
a 12:30:00
b 01:30:00
c 01:30:01
d 02:30:00
report1应用程序计数表
user datetimeuser(datetime)
a 12:30:00
b 01:30:00
c 01:30:01
d 02:30:00
以前我已经做了一个脚本，它做的工作，但它减慢了服务器，因为我的脚本将被放置在cron作业中，每隔1小时触发一次并获取结果
previous.php
$time_ranges = array(
array('12:30:00','01:30:00'),
array('01:30:01', '02:30:00'),
array('02:30:01', '03:30:00'),
array('03:30:01', '04:30:00'),
array('04:30:01', '05:30:00'),
array('05:30:01', '06:30:00'),
array('06:30:01', '07:30:00'),
array('07:30:01', '08:30:00'),
array('08:30:01', '09:30:00'),
);
$sql="SELECT sub0.TimeRange, sub0.number, COUNT(*) AS countapps
FROM
(
SELECT
CASE
";
foreach ($time_ranges as $r) {
$sql .= "
WHEN DATE_FORMAT(dt,'%H:%i:%s') BETWEEN '$r[0]' and '$r[1]'
THEN STR_TO_DATE(CONCAT(CURDATE(), ' ', '$r[0]'), '%Y-%m-%d %H:%i:%s') ";
}
$sql .= "
ELSE NULL
END AS StartRange,
CASE ";
foreach ($time_ranges as $r) {
$sql .= "
WHEN DATE_FORMAT(dt,'%H:%i:%s') BETWEEN '$r[0]' and '$r[1]'
THEN STR_TO_DATE(CONCAT(CURDATE(), ' ', '$r[1]'), '%Y-%m-%d %H:%i:%s') ";
}
$sql .= "
ELSE NULL
END AS EndRange,
CASE ";
foreach ($time_ranges as $r) {
$sql .= "
WHEN DATE_FORMAT(dt,'%H:%i:%s') BETWEEN '$r[0]' and '$r[1]'
THEN '$r[0]-$r[1]' ";
}
$sql .= "
ELSE NULL
END AS TimeRange,
COUNT(*) as number
FROM report
WHERE DATE_FORMAT(dt,'%Y:%m:%d')=DATE(CURDATE())
GROUP BY StartRange, EndRange, TimeRange
HAVING TimeRange IS NOT NULL
) sub0
LEFT OUTER JOIN report1
ON report1.dt BETWEEN sub0.StartRange AND sub0.EndRange
GROUP BY sub0.TimeRange, sub0.number";
$query=mysql_query($sql);
echo'<html>
<head>
<title>Count User Info TimeWise</title>
</head>
<h1>Count User</h1>
<table border="3" cellspacing="2">
<tr>
<th>range</th>
<th>count</th>
<th>Apps Count</th>';
while($row = mysql_fetch_array($query))
{
echo "<tr>";
echo "<td>" . $row['TimeRange'] . "</td>";
echo "<td>" . $row['number'] . "</td>";
echo "<td>" . $row['countapps'] . "</td>";
echo "</tr>";
}
echo "</table>";
echo "</html>";
?>
我想让mysql查询更短，更精确的只有两个数组和循环它。但我真的做不到。请帮助。我怎么能做到这一点取两个数组，然后计数(数组)和for循环它和计数语句在mysql
$arr=array('12:30:00','01:30:01','02:30:01','03:30:01','04:30:01','05:30:01','06:30:01','07:30:01');
$arr1=array('01:30:00','02:30:00','03:30:00','04:30:00','05:30:00','06:30:00','07:30:00','08:30:00');
$cnt=count($arr);
for($i=0;$i<$cnt;$i++){
$sql="SELECT count(*) AS test FROM report WHERE DATE_FORMAT(dt,'%H:%m:%i') BETWEEN $arr[$i] AND $arr1[$i]";
可能动态地建立一个select来返回时间，然后将它与报表连接起来:-
$numbers = array();
foreach($arr AS $key=>$value)
{
$numbers[] = "SELECT '".$arr[$key]."' AS StartRange, '".$arr1[$key]."' AS EndRange ";
}
$dates_select = "(".implode(" UNION ",$numbers).") sub0";
$sql="SELECT sub0.StartRange, sub0.EndRange, count(report.dt)
FROM $dates_select
LEFT OUTER JOIN report
ON DATE_FORMAT(report.dt,'%H:%m:%i') BETWEEN sub0.StartRange AND sub0.EndRange
GROUP BY sub0.StartRange, sub0.EndRange";