How to group time intervals in php/mysql and get statistics based on those time groups?

Question

I am new to php and I have a problem with time/date manipulation.

I need to make statistics about daily/monthly/yearly visits in some store. There is a mysql database with table "statistics" and fields: "statistic_id" ( integer, primary key ) , "visitors" ( integer ), and "dateAndTime" ( timestamp ). I have a form where user enter number of visitors that came to the store, and that number is inserted in database along with the time and date using now() function.

So basically my database looks like :

statistic_id , visitors , timeAndDate <br />
1............, 3........., 2012-09-29 14:45:02 <br />
2............, 5........., 2012-09-29 14:46:31 <br />
3............, 2........., 2012-09-29 18:48:11 ...etc.

What I need to do is sum and display all visitors that came in specific time interval. 09h-12h ; 12h-15h ; 15h-18h ; 18h-21h . So I need to display in table all these intervals and number of visitors for each of them. The problem is I don't know how to extract these intervals and then get sum of visitors for them. I was trying everything I know and I could find, but without success. Also I will have to get monthly and yearly statistics, so how can I get all 12 months from this timeAndDate column and then sum all visitors for each month ?

Do anyone have an idea how to do this, and is willing to explain me in details please ? Thanks

lix · Accepted Answer · 2012-10-02T15:02:13.070

To get visitors between 09:00 and 12:00

SELECT
    SUM(`visitors`)

FROM
    `my_table`

WHERE
    HOUR(`timeAndDate`) BETWEEN 9 AND 12

To get visitors by month

SELECT
    MONTH(`timeAndDate`),
    SUM(`visitors`)

FROM
    `my_table`

GROUP BY
    MONTH(`timeAndDate`)

To get visitors by year

SELECT
    YEAR(`timeAndDate`),
    SUM(`visitors`)

FROM
    `my_table`

GROUP BY
    YEAR(`timeAndDate`)

Simple PHP example to output the months in a table

<?php

    // Connect to database
    $db = mysqli_connect('localhost', 'root', 'root', 'test') or die('Could not connect to database');

    // Prepare sql question
    $sql = "SELECT
                MONTHNAME(`timeAndDate`) AS `month`,
                SUM(`visitors`) AS `visitors`

            FROM
                `test`

            GROUP BY
                MONTH(`timeAndDate`)";

    // Query the database
    $result = mysqli_query($db, $sql);

    // Begin table
    print '<table><thead><tr><th>Month</th><th>Visitors</th></tr></thead><tbody>';

    // Loop result
    while($row = mysqli_fetch_assoc($result)) {
        print "<tr><td>{$row['month']}</td><td>{$row['visitors']}</td></tr>";
    }

    // End table
    print '</tbody></table>';

?>

Thanks for reply, but how can I display this ? How can I display in table sum of visitors for each time interval ? Thx — offline, Oct 02 '12 at 14:38
Thank you so much it is working. Just one more question, I am still unsure, how can I display all 4 time intervals of the day and their visitors at the same time ? So it should be 09h-12h : num. of visitors ; 12h-15h : num. of visitors...all result in one table — offline, Oct 02 '12 at 15:14

score 0 · Answer 2 · answered Oct 02 '12 at 14:04

0

WHERE DATE_FORMAT(timeAndDate,'%k') BETWEEN 9 AND 12

DATE_FORMAT : http://www.w3schools.com/sql/func_date_format.asp

answered Oct 02 '12 at 14:04

poudigne

1,694
3
17
40

score 0 · Answer 3 · edited Oct 02 '12 at 14:54

0

SELECT DATE(timeAndDate), SUM(visitors) FROM Table
WHERE 
DATE_FORMAT(timeAndDate,'%k') BETWEEN 9 AND 12
AND YEAR(timeAndDate) = 2012
GROUP BY MONTH(timeAndDate)

edited Oct 02 '12 at 14:54

Nikola K.

7,093
13
31
39

answered Oct 02 '12 at 14:08

Martin

1,193
3
12
24

How can I fetch the results ? I'm trying with : while ($row = $result->fetch_array(MYSQLI_ASSOC)) { ... But I get error msg : Call to a member function fetch_array() on a non-object – offline Oct 02 '12 at 14:46
No errors, but there is no result either. I'm trying to fetch data like this : while ($row = $result->fetch_array(MYSQLI_ASSOC)) { $visitors = $row['SUM(visitors)']; $dateAndTime = $row['dateAndTime']; $timestamp = strtotime($dateAndTime); echo " ".htmlentities($visitors, ENT_QUOTES, 'UTF-8')." ".date('H:i, d-m-Y', $timestamp)." "; } What is wrong here ? – offline Oct 02 '12 at 15:02
1.try var_dump($row) after u give it values. 2.maybe the query isn't returning anything (try it in phpmyadmin if u have such or simillar) – Martin Oct 02 '12 at 15:18

How to group time intervals in php/mysql and get statistics based on those time groups?

3 Answers3