MySQL - 按日期选择行并传递给php数组

Question

I have a table with 5 columns which one is date, everytime my event happens at that date, i put a 1 on its column so my tables looks like:

Data;;;;;;;;;;; 0_6 ;;;;;;;;;; 6_12 ;;;;;;;;;; 12_18 ;;;;;;;;;;; 18_24

2013-02-01     ....1.................0...................1......................0 <br>
2013-02-01    ....0.................0...................1......................0 <br>
2013-02-01    ....0.................1...................1......................0 <br>
2013-02-02    ....0.................0...................1......................0 <br>
2013-02-04    ....1.................0...................0......................0 <br>

So what i want to do is to take the sum of all the columns in the day the event occurs and pass it to a php array. i have the start date and end date, i was trying that:
Where $diferenca = difference in days between end and start day

for($i=0; $i < $diferenca;$i++) {
$query = $con->("SELECT Data, sum(0h_6h) AS sum0_6,sum(6h_12h) AS sum6_12,sum(12h_18h) AS sum12_18,sum(18h_24h) AS sum18_24
FROM mytable
WHERE Data = 'Date_format('DATE(data)+$i','%Y-%M-%e')'
ORDER BY Data ASC
LIMIT 1");

while($row = $query->fetch(PDO::FETCH_ASSOC)....
}

but i think i'm having a problem with the mysql query, could someone help me with this select? What where clause should i be using to get my result? thanks in advance!!

Answer 1

Try :

$con->("SELECT 
            Data, 
            sum(0h_6h) AS sum0_6,
            sum(6h_12h) AS sum6_12,
            sum(12h_18h) AS sum12_18,
            sum(18h_24h) AS sum18_24
FROM 
      mytable
WHERE 
      Data BETWEEN start_day AND end_day
GROUP BY 
      Data
ORDER BY
      Data ASC");

change start_day and end_day with acual dates, provided that data is a date type column. If not then change the WHERE clause to suit your needs. What you needed, in my opinion, is the GROUP BY part