On Thu, Oct 20, 2011 at 3:36 PM, tamouse mailing lists <tamouse.li...@gmail.com> wrote: > On Tue, Oct 18, 2011 at 5:36 AM, Ford, Mike <m.f...@leedsmet.ac.uk> wrote: >>> -----Original Message----- >>> From: Ron Piggott [mailto:ron.pigg...@actsministries.org] >>> Sent: 17 October 2011 18:38 >>> >>> I need help creating a mySQL query that will select the correct >>> introduction message for a website I am making. The way I have >>> designed the table I can’t wrap my mind around the SELECT query that >>> will deal with the day # of the month. >>> >>> The part of the SELECT syntax I am struggling with is when the >>> introduction message is to change mid month. The reason I am >>> struggling with this is because I haven’t used ‘DATE’ for the column >>> type. The reason I didn’t use ‘DATE’ is because the same message >>> will be displayed year after year, depending on the date range. >>> >>> What I am storing in the table is the start month # (1 to 12) and >>> day # (1 to 31) and then the finishing month # (1 to 12) and the >>> finishing day # (1 to 31) >>> >> >> This is a little bit of a tricky one, as you have to consider both >> start_month and end_month as special cases - so you need a three-part >> conditional, for the start month, the end month, and the months in >> between. Something like this: >> >> SELECT * FROM `introduction_messages` >> WHERE (month>`start_month` AND month<`end_month`) >> OR (month=`start_month AND day>=`start_day`) >> OR (month=`end_month` AND day<=`end_day`); > > This still suffers from the problem in Jim's offer -- wrap of year and > wrap of month > > This might be best handled in a stored procedure, converting the > values stored in the table to dates to do the comparison with in the > where clause. >
In thinking further on this, the OP might consider this problem as well -- it is going to be difficult to determine the correct response if all that is stored is the start and ending month and day of month in the case where the desired time stretch wraps over to the new year. When your start month is 12 and your end month is 1, what do you expect to happen? It can't generally be solved by using current year for the start and current year + 1 for the end. For example, you may want to have something start at current year, month=6 and end as next year, month=8, so simply checking if end month < start month won't give you the ability to discern if you've wrapped the year. (I realize this may not be the OP's case, but it is still an issue if seeking a general solution.) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
