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.

PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to