[PHP-DB] SELECT
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 #? Ron www.TheVerseOfTheDay.info
[PHP-DB] Re: SELECT
I would do it this way: Where $sel_d = (the day # you want) $sel_m = (the month # you want) The where clause would be: Where (start_month = $sel_m and start_day = $sel_d) and (end_month = $sel_m and end_day = $sel_d) Someone else will probably have something more elegant, but I believe this will work for you. Never used the between clause before - have to remember that. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Re: SELECT
Though the operators are = and =, not = and =. Toby -Original Message- From: Jim Giner [mailto:jim.gi...@albanyhandball.com] Sent: Monday, October 17, 2011 1:58 PM To: php-db@lists.php.net Subject: [PHP-DB] Re: SELECT I would do it this way: Where $sel_d = (the day # you want) $sel_m = (the month # you want) The where clause would be: Where (start_month = $sel_m and start_day = $sel_d) and (end_month = $sel_m and end_day = $sel_d) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT
Dear Ron If only day is required u could add another day condition in the where clause e.g. month(current_date) between and day(current_date) between. i think u require something more than this. So could u pls explain your requirement in a little more detail say what would be the output of the query given by u. When would u consider the start date/month and when the end one. regds amit The difference between fiction and reality? Fiction has to make sense. On Mon, Oct 17, 2011 at 11:08 PM, Ron Piggott ron.pigg...@actsministries.org wrote: 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 #? Ron www.TheVerseOfTheDay.info
