CODEWITHSUNDEEP
phpmysql
Daniel Robinson
Daniel Robinson

Reputation: 653

struggling with getting birthdays in the next 2 weeks from MySQL Database

Found a few solutions on stack for this but people all seems to be asking it different ways none of which seems to match what i'm doing so their answers dont really help. Basically I have the below.

 $query = sprintf("SELECT * FROM user WHERE WEEK( userDOB ) < WEEK( DATE_ADD(DATE(now()), INTERVAL 2 WEEK) ) AND WEEK( userDOB ) >= NOW()");

I'm trying to find all birthdays in the database between today and 2 weeks from today. Currently I'm storing birthdays in the sql format yyy-mm-dd

I also have this version of the same query

        $query = sprintf("SELECT * FROM user WHERE userDOB  BETWEEN CURDATE()  AND  DATE_ADD(CURDATE(), INTERVAL +14 DAY)");

Which I found here... https://www.daniweb.com/web-development/php/threads/175275/birthday-reminder

Upvotes: 0

Views: 856

Answers (2)

Daniel Robinson
Daniel Robinson

Reputation: 653

OK I just solved it with the WEEK function in sql

here is the solution for anyone looking

$query = sprintf("SELECT * FROM user WHERE WEEK( userDOB )  BETWEEN WEEK( CURDATE() )  AND  WEEK( DATE_ADD(CURDATE(), INTERVAL +14 DAY) )");

Upvotes: 0

Etixpp
Etixpp

Reputation: 328

Store them as timestamps (seconds since 1970 or something) and just search for all data which is between now and current timestamp + 3600 * 24 (a day) * 14 ( a week)

http://php.net/manual/de/function.time.php

time() gives you the current timestamp.

Upvotes: 1

Related Questions