How to transform a PHP loop to a MySQL query?

Question

I am trying to make a PHP loop work for me in MySQL. Currently all visits to a website via a specific URL parameterare logged into a table along with the date and time of the visit. I am rebuilding the logging procedure to only count the visits via one specific parameter on one day, but I'll have to convert the old data first.

So here's what I'm trying to do: The MySQL table (let's call it my_visits) has 3 columns: parameter, visit_id and time. In my PHP code, I've created the following loop to gather the data I need (all visits made via one paramter on one day, for all parameters):

foreach (range(2008, 2014) as $year) {

    $visit_data = array();
    $date_ts    = strtotime($year . '-01-01');

    while ($date_ts <= strtotime($year . '-12-31')) {

        $date     = date('Y-m-d', $date_ts);
        $date_ts += 86400;

        // count visit data
        $sql  = 'SELECT parameter, COUNT(parameter) AS total ' .
                'FROM my_visits ' .
                'WHERE time BETWEEN \''.$date.' 00:00\' AND \''.$date.' 23:59\' '.                  
                'GROUP BY parameter ORDER BY total DESC';

        $stmt = $db->prepare($sql);

        $stmt->execute(array($date));

        while ($row = $stmt->fetch()) {

             $visit_data[] = array(
                'param'       => $row['parameter'],
                'visit_count' => $row['total'],
                'date'        => $date);
        }

        $stmt->closeCursor();
    }
}

Later on, the gathered data is inserted into a new table (basically eliminating visit_id) using a multiple INSERT (thanks to SO! :)).

The above code works, but due to the size of the table (roughly 3.4 million rows) it is very slow. Using 7 * 365 SQL queries just to gather the data seems just wrong to me and I fear the impact of just running the script will slow everything down substantially.

Is there a way to make this loop work in MySQL, like an equivalent query or something (on a yearly basis perhaps)? I've already tried a solution using GROUP BY, but since this eliminates either the specific dates or the parameters, I can't get it to work.

What is $datum? You don't have a placeholder in the query for it. And why aren't you using placeholders for $date? — Barmar
– Barmar, Commented Jun 5, 2014 at 8:26
@Barmar I'm so sorry, this happens to me all the time when I try to clean up my code for SO. I'm constantly overlooking 1 or 2 occurrences. What exactly do you mean by placeholders? — samejeanson
– samejeanson, Commented Jun 5, 2014 at 8:30
Placeholders are the ? or :name things you put in PDO queries, that get replaced by the parameters in the execute call. — Barmar
– Barmar, Commented Jun 5, 2014 at 8:32
I.e. it should be WHERE time BETWEEN CONCAT(?, " 00:00") AND CONCAT(?, " 23:59") — Barmar
– Barmar, Commented Jun 5, 2014 at 8:33
just for my understanding of the context (I think Niet already gave the best concrete answer): isn't it enough to run this script once, insert the result in a new, much smaller table with date and paramater as primary key (or indexed columns), then use the condensed table in concrete use cases? that way you would only need to update the condensed table once daily with the newest data — cypherabe
– cypherabe, Commented Jun 5, 2014 at 8:34

Niet the Dark Absol · Accepted Answer · 2014-06-05 08:25:16Z

3

You can GROUP further.

SELECT `parameter`, COUNT(`parameter`) AS `total`, DATE(`time`) AS `date`
FROM `my_visits`
GROUP BY `parameter`, DATE(`time`)

You can then execute it once (instead of in a loop) and use $row['date'] instead of $date.

This also means you don't have to update your code when we reach 2015 ;)

answered Jun 5, 2014 at 8:25

Niet the Dark Absol

326k86 gold badges480 silver badges604 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to transform a PHP loop to a MySQL query?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related