How can I select every date in a given month?

[DanielCoe]

Tweet

Hi,

I need to select every date in a given month for a report. How can I do this in MySQL?

Originally the report was selecting all data we have for certain days, but some days there maybe no data at all, so I want to display rows even though there is no data for that date and show it as a '0' or blank.

So I thought the best way to do it would be to select all the days in a month first then see if there is any data on that date, but I don't know how to select every day in a given month.

Any helpers?

Thanks

Daniel

[David Bowley]

What's your database look like?

[OLBG]

Are you using php?

Basically you want


SELECT field1,field2 FROM table WHERE datefield>='firstofmonth' AND datefield<='lastofmonth'

firstofmonth and lastofmonth needed to be calculated in php/asp

[andy_jacko]

appologies if this is wrong (im no expert at all)

but if the dates are stored

dd/mm/yy

cant you display results based on wildcards for the dd and yy and using the month you need.


again no expert and not sure on the exact syntax,

im sure someone else will give you the full techy version.

HTH

Regards andy

*edit* or you could do as above */edit*

[DanielCoe]

Hi,

OLBG - that would be fine, but I want to display a date even if there is no data corresponidng to that date in the DB.

The way I have gone about it is to get the number of days in the month - in PHP this is:

date("t")

Then I have looped around every day in the month and within the loop fire a select to get the data (if any) from MySQL.

If no data is returned, make the data/figure a '0', then display the date and '0' data, otherwise display the data and the database value.

It works perfectly, and fast.

But, the only downside I see is firing off 28-30 selects (1 for each day of the month) .

I'd love to know if there is an easier way but with only using 1 SQL query.

Daniel

[dmorison]

Hi Daniel,

It would make more sense to "fill in the blanks" in your application rather than make 31 separate queries. Logically, they achive the same result anyway.

Use a query to select data for the whole month - along the lines of what OLBG suggests above; and then loop through the result creating a new array that either contains the data from the query for a particular day, or zero if no data exists. Then construct your report from the new array.

If you can post the actual query that you are using at the moment we should be able to help out with some PHP code for the intermediate step...

[DanielCoe]

Hi,

Thanks for the help. Your idea is great, I could have done it that way.

The problem is now it's working, I don't want to change it, I'd rather get on with something else.

If I find the report is being used a lot and gets slow then I'll probably change it, but I wonder how much faster an array would be compared to 30 fast selects.

By the way I like the services you offer at FeedProcessor, I'd like to probably advertise or exchange links with you on a new project I am working on. So I may be in contact with you sometime.

Thanks

Daniel