How to create 7 days query in MySql

**Patrickboy** · 18-05-2009

Hi,
I had a big of list of my customers in MySQL Database. I want to create a query which can find out the coming birthdays of members within a week. The table of member includes complete information about them. Help to build up a query which can give the information of birthdays within a week. As i will just need to pass query and i can get information about the coming birthdays in a week

**chroma** · 18-05-2009

I am giving you an example of the query which you want to pass. The below information will help you to build your query.
First Create Table.

Code:

CREATE TABLE `birthdays` (
`name` varchar(200) NOT NULL,
`birthday` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Table Data Example

Code:

INSERT INTO `birthdays` VALUES (’BDAY 01′,’2008-12-01′),(’BDAY 02′,’2008-12-02′),
(’BDAY 03′,’2008-12-03′),(’BDAY 04′,’2008-12-04′),(’BDAY 05′,’2008-12-05′),(’BDAY 06′,’2008-12-06′),
(’BDAY 07′,’2008-12-07′),(’BDAY 08′,’2008-12-08′),(’BDAY 09′,’2008-12-09′),(’BDAY 10′,’2008-12-10′),
(’BDAY 11′,’2008-12-11′),(’BDAY 12′,’2008-12-12′),(’BDAY 13′,’2008-12-13′),(’BDAY 14′,’2008-12-14′),
(’BDAY 15′,’2008-12-15′),(’BDAY 16′,’2008-12-16′),(’BDAY 17′,’2008-12-17′),(’BDAY 18′,’2008-12-18′),
(’BDAY 19′,’2008-12-19′),(’BDAY 20′,’2008-12-20′),(’BDAY 21′,’2008-12-21′),(’BDAY 22′,’2008-12-22′),
(’BDAY 23′,’2008-12-23′),(’BDAY 24′,’2008-12-24′),(’BDAY 25′,’2008-12-25′),(’BDAY 26′,’2008-12-26′),
(’BDAY 27′,’2008-12-27′),(’BDAY 28′,’2008-12-28′),(’BDAY 29′,’2008-12-29′),(’BDAY 30′,’2008-12-30′),

The Query

Code:

select name, concat(date_format(birthday, ‘%m’),’-', date_format(birthday, ‘%d’)) 
from birthdays where ((date_format(birthday, ‘%m%d’) between date_format(curdate(), ‘%m%d’) 
and if((month(curdate()) = 12 and day(curdate()) > 24), date_format(str_to_date(’12312008′, ‘%m%d%y’),
‘%m%d’), date_format(adddate(curdate(), interval 7 day), ‘%m%d’)))

**Sam.D** · 18-05-2009

Below is an example of table. You will need to create the table in the below type and pass the query on the base of it.
Select from your table
WHERE

Code:

         birth_date LIKE CONCAT( CURRENT_DATE(), DATE_FORMAT('%d/%m'), '%')
         OR birth_date LIKE CONCAT( DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY), DATE_FORMAT('%d/%m'), '%')
         OR birth_date LIKE CONCAT( DATE_ADD(CURRENT_DATE(), INTERVAL 2 DAY), DATE_FORMAT('%d/%m'), '%')
         OR birth_date LIKE CONCAT( DATE_ADD(CURRENT_DATE(), INTERVAL 3 DAY), DATE_FORMAT('%d/%m'), '%')
         OR birth_date LIKE CONCAT( DATE_ADD(CURRENT_DATE(), INTERVAL 4 DAY), DATE_FORMAT('%d/%m'), '%')
         OR birth_date LIKE CONCAT( DATE_ADD(CURRENT_DATE(), INTERVAL 5 DAY), DATE_FORMAT('%d/%m'), '%')
         OR birth_date LIKE CONCAT( DATE_ADD(CURRENT_DATE(), INTERVAL 6 DAY), DATE_FORMAT('%d/%m'), '%')
         OR birth_date LIKE CONCAT( DATE_ADD(CURRENT_DATE(), INTERVAL 7 DAY), DATE_FORMAT('%d/%m'), '%')