How to use only one query to receive data every day for one year?

Question

I want to receive data every day for one year ago, but I have to use 365 requests for every day, for example:

for ($i = 0; $i<365; $i++){
    $end_day = ...; // the end time of each day
    $start_day = ...; // the start time of each day
    $query = select count(*)....where created < $end_day AND created > $start_day
}

I think my current solution makes the system very slow. Is there a way to use only one query?

Answer 1

Assuming your created column is DATETIME or TIMESTAMP, and the data is stored in more detail than the day:

SELECT COUNT (*) ... GROUP BY YEAR (created), MONTH (created), DAY (created)

COUNT , .. , . , , .

(), DAY , , , .

, WHERE > $start AND created < $finish, ( () == 2010).

:

MySQL Query GROUP BY //

http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html

Answer 2

group by. http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html

, , (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format)

select count(*), created from my_table group by created

Answer 3

datetime :

SELECT COUNT(*),DATE(created) as d ..... GROUP BY d;

unix:

SELECT COUNT(*),DATE(FROM_UNIXTIME(created)) as d ..... GROUP BY d;

, Mysql " " , , , , .