How to create 7 days query in MySql
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
Re: How to create 7 days query in MySql
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’)))
Re: How to create 7 days query in MySql
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'), '%')