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

Answer 1

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>';

?>