MySQL - Selecting rows by date and passing to php arrays

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!!

You where clause should contain the value u want to filter on. so WHERE data = '21-10-2013' But to be honest I think ur database tabel is not good so I would fix that first. What is the purpose of this table? — Reshad
– Reshad, Commented Oct 21, 2013 at 19:03
I need this table to count how many events occur on that day, on that interval of the day, so i thought in putting a 1 on the corresponding column all the times the event happens and then summing the columns '1's for that day to get the total of events on that day, on the corresponding interrval. — GustavoxD
– GustavoxD, Commented Oct 21, 2013 at 19:07

Udan · Accepted Answer · 2013-10-21 19:10:36Z

2

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

answered Oct 21, 2013 at 19:10

Udan

5,6192 gold badges31 silver badges34 bronze badges

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

3 Comments

GustavoxD Over a year ago

I think my question wasn't clear, sorry. I'm looking for the sum of the events of one day, for all the days on my BETWEEN start_day AND end_day, not the sum for the interval of days.

Udan Over a year ago

group by does that... it makes each line have a unique day and the sums are for only ther records grouped under that day

GustavoxD Over a year ago

hmm, sorry, my bad again. Gonna try :)

Parag · Accepted Answer · 2013-10-21 19:57:04Z

1

And this is not working?

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 '2013-02-01' and '2013-02-04' 
/* Date_format('DATE(data)+$i','%Y-%M-%e') */
GROUP BY data
ORDER BY Data ASC
/*LIMIT 1 */

Notice, I have removed LIMIT 1 clause, added two dates in WHERE clause which you can replace with your "start" date and "end date". Added GROUP BY.

If you have 10 days of start date and stop date then your PHP loop will run 10 times which is not a good way to get data out of database. Rather, get all data from server in one shot and then process on client side.

Hope this works for you.

answered Oct 21, 2013 at 19:57

Parag

511 bronze badge

1 Comment

Parag Over a year ago

hmm, by the time I composed this message and replied after getting sucked into some other work, @Udan already answered same thing so you can ignore my answer.

Collectives™ on Stack Overflow

MySQL - Selecting rows by date and passing to php arrays

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related