0

i am making a query to get birthdays for next 10 days . i was able to make a query for today but even after referring some post was not able to make it for next 10 days. Here is the query i made to get birthdays for today 

$count=mysql_query("SELECT u.fname, u.lname, u.profile_pic, u.uid, u.bday, f.uid, 
f.friend_id, f.status FROM friend_list f, users_profile u 
WHERE f.uid = '$id' AND f.status = '1' 
AND u.uid = f.friend_id 
AND DAY(STR_TO_DATE(u.bday, '%m-%d-%y')) = DAY(CURDATE()) 
AND MONTH(STR_TO_DATE(u.bday, '%m-%d-%y')) = MONTH(CURDATE())");

can anyone help me out with a query for 10 days

Himanshu
  • 31,810
  • 31
  • 111
  • 133
Param Veer
  • 776
  • 4
  • 13
  • 27
  • 3
    please don't use `mysql_*` functions, it's deprecated (see [*red box*](http://php.net/manual/en/function.mysql-query.php)) and vulnerable to sql-injection. Use [*PDO*](http://php.net/manual/en/book.pdo.php) or [*MySQLi*](http://php.net/manual/en/book.mysqli.php). – Nir Alfasi Sep 01 '12 at 08:11
  • ohh, didnt knew that then what i have to write it like ? it wont work on server? – Param Veer Sep 01 '12 at 08:15

5 Answers5

2

If u.bday is a date, you can do something like this:

WHERE u.bday < NOW() + INTERVAL 10 DAY AND u.bday >= NOW()

MySQL has good date comparison and calculation functions. Refer to the manual for more details: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

If u.bday is a varchar formatted mm-dd-yyyy, use this:

WHERE STR_TO_DATE(u.bday, '%m-%d-%Y') < NOW() + INTERVAL 10 DAY 
                              AND STR_TO_DATE(u.bday, '%m-%d-%Y') >= NOW()

(If u.bday is not a date, make it one. Using the right data type for your columns makes your life a lot easier.)

Bart Friederichs
  • 33,050
  • 15
  • 95
  • 195
  • yes my bday is in varchar but is ny way out to get for next 10days as i managed to get it for today but breaking it and comparing the day and month – Param Veer Sep 01 '12 at 08:21
  • Then my solution still works if you use str_to_date() and then compare it to NOW() + INTERVAL 10 DAY. Trick is to make it a date and then use date comparison functions available. Don't break it down and do date-arithmetic yourself. – Bart Friederichs Sep 01 '12 at 08:25
  • kk i tried it `SELECT u.fname, u.lname, u.profile_pic, u.uid, u.bday, f.uid, f.friend_id, f.status FROM friend_list f, users_profile u WHERE f.uid = '3' AND f.status = '1' AND u.uid = f.friend_id AND STR_TO_DATE(u.bday, '%m-%d-%y') < NOW() + INTERVAL 10 DAY` but it didnt worked – Param Veer Sep 01 '12 at 08:32
  • no records fetched but there are number of records that exist – Param Veer Sep 01 '12 at 08:35
  • How does your bdate string look like? – Bart Friederichs Sep 01 '12 at 08:35
  • my bday string looks like `09-21-1990` `m-d-y` plus its in varchar – Param Veer Sep 01 '12 at 08:41
  • In that case, use a capital Y instead of lower case y in your STR_TO_DATE call: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format – Bart Friederichs Sep 01 '12 at 08:42
  • after using capital Y its giving results of birthdays even after 10 days – Param Veer Sep 01 '12 at 08:50
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/16125/discussion-between-bart-friederichs-and-param-veer) – Bart Friederichs Sep 01 '12 at 08:51
  • could you give some of your data? – Bart Friederichs Sep 01 '12 at 09:27
0

You will need a calendar table.

See this recent post;

Get report from a the result using MYSQL

or the original post I refer to;

Mysql: Select all data between two dates

Community
  • 1
  • 1
Matt Tew
  • 1,581
  • 1
  • 9
  • 15
0

well trying for so long i found the solution for this , sharing it with everyone so that it could help someone .

"SELECT u.fname, u.lname, u.profile_pic, u.uid, u.bday, f.uid, f.friend_id, f.status FROM friend_list f, users_profile u WHERE f.uid = '$id' AND f.status = '1' AND u.uid = f.friend_id and DAY(STR_TO_DATE(u.bday, '%m-%d-%y')) < DAY(CURDATE())+7 and DAY(STR_TO_DATE(u.bday, '%m-%d-%y')) != DAY(CURDATE()) AND MONTH(STR_TO_DATE(u.bday, '%m-%d-%y')) = MONTH(CURDATE())"

Param Veer
  • 776
  • 4
  • 13
  • 27
0

Good practice: Always use the correct field type that matches the data and avoid unix_timestamp for dates unless you have good reason for it. Also specify your joins.

If your bday is not a timestamp or date field (should have used date type over timestamp bdays dont change) then write it as:

SELECT u.fname, u.lname, u.profile_pic, u.uid, u.bday, f.uid, f.friend_id, f.status<br> 
FROM friend_list f <br>
    join users_profile u on(<br>
    u.uid = f.friend_id  AND f.status = '1' AND STR_TO_DATE(u.bday, '%Y-%m-%d') between date(now()) AND date(DATE_ADD(NOW(), INTERVAL 10 DAY))<br>
    )<br>
where f.uid = '$id'

And if you did use a date type for the field you can change <br>
STR_TO_DATE(u.bday, '%Y-%m-%d')<br>
to<br>
date(u.bday)
Giacomo1968
  • 25,759
  • 11
  • 71
  • 103
Aaron
  • 167
  • 1
  • 1
  • 10
0
SELECT * FROM tablename WHERE DAYOFYEAR(curdate()) <= DAYOFYEAR(fieldname) AND DAYOFYEAR(curdate()) +10 >= DAYOFYEAR(fieldname) LIMIT 30;
ToddJCrane
  • 1,587
  • 1
  • 14
  • 22
Eko Budiharto
  • 1