r00ttap Posted June 16, 2009 Share Posted June 16, 2009 I have two tables. One that holds these messages along with some other information and another table that holds the dates that belong to these messages. I'm trying to only pull the messages that are >= NOW() meaning anything older than today will not display. The first "version" of this app was only able to store one date, an end date. I originally pulled the messages with this syntax: SELECT * FROM messages WHERE campus = 'campus' AND CONCAT(date,' ',end_time) >= NOW() ORDER by date, start_time The table looked a little liked this (along with a date field that housed the date to displayed): +----+------------+----------+-------------------------------+-------------------------+-------------------------------------------+ | id | start_time | end_time | course_name | campus | message | +----+------------+----------+-------------------------------+-------------------------+-------------------------------------------+ | 11 | 10:00:00 | 12:00:00 | Some course name | Campus | Test message with additional information. | +----+------------+----------+-------------------------------+-------------------------+-------------------------------------------+ Well now I need MULTIPLE dates for these messages and had to create the second table that houses only the ID and the dates. +----+------------+ | id | date | +----+------------+ | 11 | 2009-06-15 | | 11 | 2009-06-16 | | 11 | 2009-06-17 | | 11 | 2009-06-18 | | 11 | 2009-06-19 | +----+------------+ I've been working with this new syntax to pull the messages that looks like this: SELECT * FROM messages, messages_dates WHERE campus = 'campus' AND messages.id = messages_dates.id AND CONCAT(date,' ',end_time) >= NOW() GROUP BY messages.id ORDER by date, start_time And it will only pull 1 record, not all of the dates listed. I know my problem is "date". What I'm asking here is how do I get the last or "MAX(date)" from the dates table so that I can only select messages that are greater than or equal today, anything older than today will not be shown. Quote Link to comment https://forums.phpfreaks.com/topic/162390-solved-how-to-get-the-highest-date-from-a-set-of-records/ Share on other sites More sharing options...
kickstart Posted June 16, 2009 Share Posted June 16, 2009 Hi Probably your group by clause that is confusing things. Initial idea, change it to GROUP BY messages_dates.id All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/162390-solved-how-to-get-the-highest-date-from-a-set-of-records/#findComment-857139 Share on other sites More sharing options...
r00ttap Posted June 16, 2009 Author Share Posted June 16, 2009 kickstart, This works, as well as my original statement but the problem is that it only pulls 1 record. I want it to pull everything that >= NOW(). So if the dates were: 2009-06-14 2009-06-15 2009-06-16 2009-06-17 2009-06-18 2009-06-19 It will show the message until the 19th, after the 19th it won't pull the messages anymore. Quote Link to comment https://forums.phpfreaks.com/topic/162390-solved-how-to-get-the-highest-date-from-a-set-of-records/#findComment-857151 Share on other sites More sharing options...
kickstart Posted June 16, 2009 Share Posted June 16, 2009 Hi More of a look, and while I think I am on the right track, my above idea is garbage. Not sure you need the GROUP BY at all. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/162390-solved-how-to-get-the-highest-date-from-a-set-of-records/#findComment-857152 Share on other sites More sharing options...
r00ttap Posted June 16, 2009 Author Share Posted June 16, 2009 Actually the GROUP BY is exactly what a wanted. I must have spelled something wrong because it's working perfectly now. Thanks for you time and help kickstart! Quote Link to comment https://forums.phpfreaks.com/topic/162390-solved-how-to-get-the-highest-date-from-a-set-of-records/#findComment-857175 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.