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)
Table structure for table `introduction_messages`
--
CREATE TABLE IF NOT EXISTS `introduction_messages` (
`reference` int(2) NOT NULL AUTO_INCREMENT,
`start_month` int(2) NOT NULL,
`start_day` int(2) NOT NULL,
`end_month` int(2) NOT NULL,
`end_day` int(2) NOT NULL,
`theme` varchar(100) NOT NULL,
`message` longtext NOT NULL,
PRIMARY KEY (`reference`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
My query so far is:
SELECT * FROM `introduction_messages` WHERE 11 BETWEEN `start_month` AND `end_month`
11 is for November. 2 rows have been selected:
Row #1:
`start_month` 9
`start_day` 16
`end_month` 11
`end_day` 15
Row #2:
`start_month` 11
`start_day` 16
`end_month` 12
`end_day` 10
How do I modify the query to incorporate the day #?