AndyB Posted April 15, 2007 Share Posted April 15, 2007 I have a simple 'blog' database table containing content with the posted date entered as date (yyyy-mm-dd). Some months there will be many items, some months there may be none. Can I generate a list of months where there is at least one item, without lots of separate queries? If so, how? Quote Link to comment https://forums.phpfreaks.com/topic/47156-solved-finding-months-with-data/ Share on other sites More sharing options...
guyfromfl Posted April 15, 2007 Share Posted April 15, 2007 do you mean generate a list like this: March // where there might be 5 entries for march but it is in the list only 1 time??? May June August Mark Quote Link to comment https://forums.phpfreaks.com/topic/47156-solved-finding-months-with-data/#findComment-229971 Share on other sites More sharing options...
AndyB Posted April 15, 2007 Author Share Posted April 15, 2007 Exactly like that. Quote Link to comment https://forums.phpfreaks.com/topic/47156-solved-finding-months-with-data/#findComment-229972 Share on other sites More sharing options...
Barand Posted April 15, 2007 Share Posted April 15, 2007 SELECT YEAR(date) as yr, MONTH(date) as mth, COUNT(*) as blogs FROM blog GROUP BY yr, mth HAVING blogs > 0 Quote Link to comment https://forums.phpfreaks.com/topic/47156-solved-finding-months-with-data/#findComment-229983 Share on other sites More sharing options...
guyfromfl Posted April 15, 2007 Share Posted April 15, 2007 $sql = "SELECT DISTINCT MONTH(eventDate) FROM tbl_blog ORDER BY MONTH(eventDate)"; Quote Link to comment https://forums.phpfreaks.com/topic/47156-solved-finding-months-with-data/#findComment-229984 Share on other sites More sharing options...
guyfromfl Posted April 16, 2007 Share Posted April 16, 2007 this will give you the month name automatically $sql = "SELECT DISTINCT MONTHNAME(eventDate) FROM tbl_events ORDER BY MONTH(eventDate)"; Quote Link to comment https://forums.phpfreaks.com/topic/47156-solved-finding-months-with-data/#findComment-229991 Share on other sites More sharing options...
AndyB Posted April 17, 2007 Author Share Posted April 17, 2007 Thank you. Barand's query did the trick perfectly. Quote Link to comment https://forums.phpfreaks.com/topic/47156-solved-finding-months-with-data/#findComment-230876 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.