Joomla - MySQL query within a loop - I guess this

Question

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

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?

Answer 1

I am guessing the final implementation but I would suggest

First getting all the records that you want (You can customize the query to give you lesser records).

$query = "SELECT * FROM table1";

When you loop the $result

if(isset($count[strtotime($result['tm'])])){
    $count[strtotime($result['tm'])] += 1;
}else{
    $count[strtotime($result['tm'])] = 1;
}

This way you can get the count in a array grouped by the timestamp.

Then you need to sort it so that it is ascending order

ksort($count);

Then you loop the array to get the date

foreach($count as $date=>$val){
    $_date_count[date('Y-M-d',$date)] = $val;
}

You also do

$arraystring = implode(',',$_date_count);

Is this what you want ?

Answer 2

Please try with this query:

SELECT  FROM_UNIXTIME(tm,'%Y-%m-%d') AS `Day`
        , COUNT(*) AS Visits 
FROM    `table1`
GROUP BY 
        FROM_UNIXTIME(tm,'%Y-%m-%d')

If it work for you you have to add:

WHERE FROM_UNIXTIME(tm,'%Y-%m-%d') > DATE('2010-05-21')

Answer 3

You can change your query to the following: SELECT * FROM table1 WHERE FROM_UNIXTIME(tm) BETWEEN '" . $startdate . "' AND '" . $end_date . "'. This will select all results that are within your provided timespan.

Next, you'll want to loop through the results and count the amount of results per day, like so:

$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;
}

Then finally in your existing while-loop you can do $my_count = (isset($counts[$result['tm']])) ? $counts[$result['tm']] : 0; to access the amount of results for the given day.

This results in:

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

And you now have the same functionality with only 1 query.

Next, I'd probably move the counting code into its own function, like so:

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

Hope this helps.

Answer 4

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

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

EDIT

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