On Tue, Oct 18, 2011 at 5:36 AM, Ford, Mike <M.Ford@xxxxxxxxxxxxxx> wrote: >> -----Original Message----- >> From: Ron Piggott [mailto:ron.piggott@xxxxxxxxxxxxxxxxxx] >> 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. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
