mysql query to display each day between start_date and end_date fields?
I have a database with the following fields: start_date, end_date, channel, position, advertiser, user.
I want it to display like in the image (http://business-review-webinars.com/table.jpg). divided by the channel (software, ent app, open source...) and by the date (today, tomorrow, the day after, ...)
I know i can get results for each day with this:
SELECT * FROM table WHERE '2011-06-13' BETWEEN start_date AND end_date
and that would display everything that's going on today. but I'm having trouble trying to figure out how to make it display neatly for each individual channel.
I guess i could do 1 query for each channel but i have around 50 channels, and if im gonna do 1 query for each day I would have hundreds o开发者_如何学JAVAf queries on the page.. not good i guess..
any ideas?
I think you essentially are looking for a "crosstab" view of your data.
http://dev.mysql.com/tech-resources/articles/wizard/print_version.html
and
http://rpbouman.blogspot.com/2005/10/creating-crosstabs-in-mysql.html
are some links I have in my bookmarks which may help you out.
精彩评论