2

What I am trying to do here is render a report for each date in the current month from a MYSQL table.

I have a table with rows in, which a lot have the same date. As you can see heremy MYSQL table with rows with duplicate dates in

What I would like to do is for every date in the current month is see how many rows have been found in the MYSQL table for that date and then return it into an array. The final product will be a multidimensional array for every date in the current month. Something like this

array("dates"=>
array(
"1 feb"=>2, //the number of rows for that date found in the MYSQL table
"2 feb"=>8,
"3 feb"=>0
)
)

But the issue is I wouldn't have a clue where to start with the coding, like what PHP functions would I use? So I was hoping someone could push me in the right direction :)

John Conde
  • 217,595
  • 99
  • 455
  • 496
Frank
  • 1,844
  • 8
  • 29
  • 44

2 Answers2

0

Probably not working code but should point you in the right direction:

SELECT 
    SUM(money) AS sum,
    `date`
FROM 
    money_table
WHERE
    MONTH(`date`) = 2
GROUP BY 
    DAY(`date`)

Reference

John Conde
  • 217,595
  • 99
  • 455
  • 496
  • But this doesn't loop through every date in the current month – Frank Feb 09 '13 at 22:47
  • You can use a `where` clause to do that. You know how your DB is set up so it's up to you to ultimately get this query to do exactly what you want. – John Conde Feb 09 '13 at 22:48
  • It's not doing what I need it to do though, I want it to loop through every possible date in this month, if no rows are counted for that date then put a `0` – Frank Feb 09 '13 at 22:58
  • So write some code that combine the this answer with [the other answer i gave you](http://stackoverflow.com/questions/14792217/php-getting-month-dates-and-placing-into-array). You're already 99% there. I'm happy to help you but I can't do all of your work for you. – John Conde Feb 10 '13 at 03:29
-1

You said you didn't have a table with all the dates. If you did, this would be trivial. Let's say you had such a table called calendar.

select calendardate
, ifnull(sum(money), 0) sum_of_money
from calendar left join money on calendardate = money.date
where calendardate >= {d '2013-02-01'}
and calendardate < {d '2013-03-01'}
group by calendardate

Other things that could be stored in the calendar table are fiscal information and holidays. It might be worth your while to create and maintain one.

Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43