Joomla - 循环内的 MySQL 查询 - 我想这是不好的做法

2022-01-06 00:00:00 php mysql joomla

我目前正在使用此代码输出结果数组:

I´m currently using this code for outputting an array of results:

<?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, ', ');
?>

如您所见，我正在从特定日期到当前日期运行的循环中执行数据库查询.假设这个循环是从 jan 开始运行的.2007 年至 1 月2014 年意味着总循环数为 2557，这不会是性能/服务器负载方面的严重问题吗??有没有更聪明的方法来做到这一点?

As you can see I´m doing a database query inside a loop running from a specific date up until current date. Let´s say that this loop was running from jan. 2007 to jan. 2014 that would mean a total loops of 2557, would this not be a serious problem regarding performance / server load?? Is there a smarter way to do this?

推荐答案

我终于做到了！而且我认为这是一个合理的解决方案，不会对服务器资源造成太大压力.

I finally did it! And I think this is a reasonable solution without putting too much strain on the server ressources.

请看一下性能是否正常.请注意，我在 while 循环中使用了 foreach 循环，但至少现在我没有在每个循环中查询 MySQL ..

Please have a look and see if it´s ok regarding performance. Note that I am using a foreach loop inside the while loop, but at least now I´m not quering MySQL in each loop..

<?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, ', ');
?>

编辑

这是我制作的优化代码，其中我只执行一个数据库查询和一个循环.我在循环内使用智能检测来每天检查丢失的结果，如果需要，我用零值填充数组.看这里:

Here is an optimized code i made where I only do one database query and one loop. I use a smart detection inside loop to check for missing results per day and if needed I fill the array with zero values. Look here:

    <?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, ', ');
    ?>

相关文章