I'm目前使用此代码，用于输出结果的数组：
<?PHP
$db = JFactory::getDbo();
//Start date
$startdate = date('2010-05-21');
// End date
$end_date = date("Y-m-d");
$arraystring = '';
while (strtotime($startdate) <= strtotime($end_date)) {
$query = "SELECT * FROM table1 WHERE FROM_UNIXTIME(tm,'%Y-%m-%d') = '".$startdate."'";
$db->setQuery($query);
$replyAGS = $db->query();
$my_count = $db->getNumRows();
$arraystring .= $my_count.', ';
$startdate = date ("Y-m-d", strtotime("+1 day", strtotime($startdate)));
}
$arraystring = rtrim($arraystring, ', ');
?>
正如你所看到I'm做特定日期，直到当前日期运行循环中有一个数据库查询。 Let's说，这个循环是从一月运行。 2007年一月 2014将意味着2557总循环，这样岂不是关于性能/服务器负载严重的问题？ 有没有一种更聪明的办法做到这一点？
请这个查询尝试：
SELECT FROM_UNIXTIME(tm,'%Y-%m-%d') AS `Day`
, COUNT(*) AS Visits
FROM `table1`
GROUP BY
FROM_UNIXTIME(tm,'%Y-%m-%d')
如果是为你工作，你必须添加：
WHERE FROM_UNIXTIME(tm,'%Y-%m-%d') > DATE('2010-05-21')
我终于做到了！ 我认为这是不把太大的压力在服务器ressources一个合理的解决方案。
请看看，看看it's ok了有关业绩。 请注意，我现在用的是while循环foreach循环，但至少现在I'm在每个循环不quering MySQL的..
<?php
$query = "SELECT FROM_UNIXTIME(tm,'%Y-%m-%d') AS Day, COUNT(*) AS Visits FROM table1 GROUP BY FROM_UNIXTIME(tm,'%Y-%m-%d')";
$db->setQuery($query);
$replyAGS1 = $db->query();
$rowsarray = $db->loadObjectList();
$arrayforyearchart = '';
while (strtotime($startdateforarray) <= strtotime($end_date)) {
$counttouse = 0;
foreach ($rowsarray as $keycount) {
if ($keycount->Day == $startdateforarray) {
$counttouse = $keycount->Visits;
}
}
$arrayforyearchart .= $counttouse.', ';
$startdateforarray = date ("Y-m-d", strtotime("+1 day", strtotime($startdateforarray)));
}
$arrayforyearchart = rtrim($arrayforyearchart, ', ');
?>
编辑
这里是一个优化的代码，我做了，我只能做一个数据库查询和一个循环。 我使用智能检测内部循环来检查缺失每天结果，如果需要，我填的是阵列与零个值。 看这里：
<?php
$query = "SELECT FROM_UNIXTIME(tm,'%Y-%m-%d') AS Day, COUNT(*) AS Visits FROM #__cwtraffic GROUP BY FROM_UNIXTIME(tm,'%Y-%m-%d')";
$db->setQuery($query);
$replyAGS1 = $db->query();
$rowsarray = $db->loadObjectList();
$arrayforyearchart = '';
$loopcount = 0;
//Loop START
foreach ($rowsarray as $keycount) {
$loopcount = $loopcount + 1;
if ($loopcount == 1) {
$startdateforarray = $keycount->Day;
//Data needed later for chart START
$startdateUnix = strtotime($keycount->Day);
$startdateDay = date('j', $startdateUnix);
$startdateMonth = date('n', $startdateUnix);
$startdateYear = date('Y', $startdateUnix);
//Data needed later for chart END
}
//Fill the array with zero counts if needed START
$currentdateinloop = strtotime($keycount->Day);
$comparedate = strtotime($startdateforarray);
$datediff = $currentdateinloop - $comparedate;
$istheregap = floor($datediff/(60*60*24));
$i=1;
while ($i <= $istheregap) {
$arrayforyearchart .= '0, ';
$i++;
}
//Fill the array with zero counts if needed END
$arrayforyearchart .= $keycount->Visits.', ';
$startdateforarray = date ("Y-m-d", strtotime("+1 day", strtotime($startdateforarray)));
}
//Loop END
$arrayforyearchart = rtrim($arrayforyearchart, ', ');
?>
您可以将查询更改为以下： SELECT * FROM table1 WHERE FROM_UNIXTIME(tm) BETWEEN '" . $startdate . "' AND '" . $end_date . "' 。 这将选择是你提供的时间跨度内的所有结果。
接下来，你要通过的结果回路和计数每天结果的量，像这样：
$query = "SELECT * FROM table1 WHERE FROM_UNIXTIME(tm) BETWEEN '" . $startdate . "' AND '" . $end_date . "'";
$db->setQuery($query);
$counts = array();
while ($result = $db->loadAssoc()) {
$counts[$result['tm']] = (isset($counts[$result['tm']])) ? ($counts[$result['tm']] + 1) : 1;
}
后来终于在现有while循环，你可以做$my_count = (isset($counts[$result['tm']])) ? $counts[$result['tm']] : 0; $my_count = (isset($counts[$result['tm']])) ? $counts[$result['tm']] : 0; 访问结果的量给定的一天。
这导致：
<?PHP
$db = JFactory::getDbo();
//Start date
$startdate = '2010-05-21';
// End date
$end_date = date("Y-m-d");
$query = "SELECT * FROM table1 WHERE FROM_UNIXTIME(tm) BETWEEN '" . $startdate . "' AND '" . $end_date . "'";
$db->setQuery($query);
$counts = array();
while ($result = $db->loadAssoc()) {
$counts[$result['tm']] = (isset($counts[$result['tm']])) ? ($counts[$result['tm']] + 1) : 1;
}
$arraystring = '';
while (strtotime($startdate) <= strtotime($end_date)) {
$my_count = (isset($counts[$result['tm']])) ? $counts[$result['tm']] : 0;
$arraystring .= $my_count.', ';
$startdate = date ("Y-m-d", strtotime("+1 day", strtotime($startdate)));
}
$arraystring = rtrim($arraystring, ', ');
?>
而你现在只有1查询相同的功能。
接下来，我可能会移动计数代码到其自身的功能，就像这样：
<?php
function getDayCounts($startdate, $enddate) {
$query = "SELECT * FROM table1 WHERE FROM_UNIXTIME(tm) BETWEEN '" . $startdate . "' AND '" . $enddate . "'";
$db->setQuery($query);
$counts = array();
while ($result = $db->loadAssoc()) {
$counts[$result['tm']] = (isset($counts[$result['tm']])) ? ($counts[$result['tm']] + 1) : 1;
}
return $counts;
}
$db = JFactory::getDbo();
//Start date
$startdate = '2010-05-21';
// End date
$end_date = date("Y-m-d");
$counts = getDayCounts($startdate, $end_date);
$arraystring = '';
while (strtotime($startdate) <= strtotime($end_date)) {
$my_count = (isset($counts[$result['tm']])) ? $counts[$result['tm']] : 0;
$arraystring .= $my_count.', ';
$startdate = date ("Y-m-d", strtotime("+1 day", strtotime($startdate)));
}
$arraystring = rtrim($arraystring, ', ');
希望这可以帮助。
我猜测最终落实，但我会建议
首先让所有你想要的（你可以自定义查询，让您更小的记录）的记录。
$query = "SELECT * FROM table1";
当你循环的$结果
if(isset($count[strtotime($result['tm'])])){
$count[strtotime($result['tm'])] += 1;
}else{
$count[strtotime($result['tm'])] = 1;
}
这样，您就可以得到由时间戳分组的一个数组中的计数。
然后，你需要对它进行排序，以便它是升序
ksort($count);
然后你循环数组来获得日期
foreach($count as $date=>$val){
$_date_count[date('Y-M-d',$date)] = $val;
}
你也做
$arraystring = implode(',',$_date_count);
这是你想要的吗 ？
