MySQL Query: Retrieve 'birthdays' (YYYY-MM-DD) from the database, & select those that fall w/in the current week (regardless of year). / PHP

Question

Language: PHP and MySQL

What I'm trying to do:
Email users who are celebrating their birthdays within the week, a week ahead from their birthday.

Facts:

birthday dates are stored in the standard YYYY-MM-DD date format

Technically:

Retrieve 'birthday' (YYYY-MM-DD) from the database, and select users whose 'birthday' is within the current week (regardless of the year).
Output results.

What I'm doing:

Assuming that we already have a database connection...

//execute the SQL query and return records
$result = mysql_query("SELECT * FROM table_birthdays WHERE strftime('%W', column_birthday) == strftime('%W', 'now')");

// debug
var_dump($result);

//fetch the data from the database 
while ($row = mysql_fetch_array($result)) {

      // DO SOMETHING WITH RESULTS
   echo $row['column_birthday']."<br><br>" .
   "Email: " . $row['column_email'];

}
//close the connection
mysql_close($dbhandle);

Based on the above code...
I am currently getting the following results:

var_dump() returns:

bool(false)
And the while loop returns:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in...

Question:
Am I doing it right? Will the while loop not return boolean if results are found?
Or... should I be querying this differently in order to achieve my desired outcome? (quoted below)

Desired outcome:

Email users who are celebrating their birthdays within the week, a week ahead from their birthday.

`mysql_*` functions are deprecated and shouldn't be used. Use `mysqli_*` or PDO instead. — naththedeveloper, Jun 27 '13 at 08:18
possible duplicate of [mysql\_fetch\_array() expects parameter 1 to be resource, boolean given in select](http://stackoverflow.com/questions/2973202/mysql-fetch-array-expects-parameter-1-to-be-resource-boolean-given-in-select) — John Conde, Jul 02 '13 at 13:44
See [this answer](http://stackoverflow.com/a/11674313/250259) for how to troubleshoot this. — John Conde, Jul 02 '13 at 13:44

score 4 · Accepted Answer · answered Jun 27 '13 at 08:15

4

MySQL has a convenient function called WEEKOFYEAR - if you select all WHERE WEEKOFYEAR(`birthday`) = WEEKOFYEAR(NOW()) then you will get all users who have a birthday in the current week.

However, if you are looking for people whose birthday is sometime between today and 7 days from now, you could make use of the DATEDIFF function:

WHERE DATEDIFF(NOW(),`birthday`)%365 BETWEEN 0 AND 7

answered Jun 27 '13 at 08:15

Niet the Dark Absol

320,036
81
464
592

I can accept the answer as correct within the next 4 minutes, meanwhile, thank you @Kolink. This worked. – Mafia Jun 27 '13 at 08:21
Out of curiosity, which one of my two options were you looking for? – Niet the Dark Absol Jun 27 '13 at 08:22
I was looking for people whose birthday is sometime between today and 7 days from now. Basically, your 2nd option using the `DATEDIFF` function. Thanks again. (Thanks for providing 2 options to cater both scenarios) – Mafia Jun 27 '13 at 08:23
1

Gotcha :) Sometimes when it's unclear I like to give options - one of them might be it! "Week" is actually quite difficult to be clear on, so I tend to use "7 days" when I'm talking about the span of time, and "week" to mean a calendar week from Monday to Sunday. – Niet the Dark Absol Jun 27 '13 at 08:25
word of caution on using BETWEEN 0 and 7 isn't including days 0 and 7 in the range so you may not get the results you're expecting all the time – Dave Jun 27 '13 at 08:25
weekofyear won't always work, e.g. WEEKOFYEAR('2013-01-01') != WEEKOFYEAR('2012-01-01') – fthiella Jun 27 '13 at 08:26
@Dave: Yes it does. `BETWEEN` is inclusive. – Niet the Dark Absol Jun 27 '13 at 08:29
@fthiella That's a good point. I didn't think of that XD Mind you, that'd actually screw things up royally! Wow... – Niet the Dark Absol Jun 27 '13 at 08:29
@Kolink never in my experience (at least not in mysql) – Dave Jun 27 '13 at 08:30
How would that screw things up @fthiella & @Kolink? And should I be worried? :) ...Oh wait, I'm not using the `weekofyear` function, so I shouldn't be. Duh. – Mafia Jun 27 '13 at 08:32
Yeah, you're safe with the second option. The first one is bad because "week of year" is based on some calculation that is not just "number of days since the start". – Niet the Dark Absol Jun 27 '13 at 08:39
@Kolink : How would you query it if you need the birthday to be **precisely** 7 days from today, if you're still feeling helpful? Thanks so much. – Mafia Jun 28 '13 at 07:57
1

By "precisely", do you mean to the second? Hmm... Maybe try something like ``WHERE CONCAT(YEAR(NOW()),SUBSTRING(`birthday` FROM 5)) BETWEEN NOW() AND DATE_ADD(NOW(),INTERVAL 7 DAY)`` - can't promise it'll work, or how fast it'll be, but in theory it should do the job. – Niet the Dark Absol Jun 28 '13 at 14:51
Thank you so much @Kolink, you've been more than helpful! By "precisely" I meant 7 days from today (and not a day later), this all sounds so technical so I'm not sure if I'm even explaining it right. The e-mail is supposed to send out 7 days before a user's birthday, & I realize using `BETWEEN` would e-mail them more then once (7 times to be exact), because their birthday would be applicable the entire week, so there was definitely flaw in my logic there. What I'm really trying to do is e-mail a user once, & that is a week before his birthday. The script runs once a day on auto-pilot. Thanks! – Mafia Jul 02 '13 at 09:21
1

Why not just use `=7` then, instead of `BETWEEN 0 AND 7`? – Niet the Dark Absol Jul 02 '13 at 13:31

score 2 · Answer 2 · answered Jun 27 '13 at 09:33

This is my solution. It is a little more complicated but we also need to consider leap years.

Given the date of birth @dob, and given the current date @curdate, we can calculate the neth birthday with this:

SELECT
  @dob +
    INTERVAL
      YEAR(@curdate)-YEAR(@dob) +
      (MONTH(@dob)<MONTH(@curdate)
       OR (MONTH(@dob)=MONTH(@curdate) AND DAY(@dob)<DAY(@curdate)))
    YEAR nextbd

So if for example the current date is '2013-06-27' and the date of birth is '1980-06-28' the next birthday would be on '2013-06-28', while if the date of birth is '1980-06-26' the next birthday would be on '2014-06-26'.

If the current date is '2013-02-28' and the date of birth is '2012-02-29', the next birthday would be on '2013-02-28'.

The number of days to the next birthday can be calculated using DATEDIFF:

SELECT
  DATEDIFF(
    @dob +
      INTERVAL
        YEAR(@curdate)-YEAR(@dob) +
        (MONTH(@dob)<MONTH(@curdate)
         OR (MONTH(@dob)=MONTH(@curdate) AND DAY(@dob)<DAY(@curdate)))
      YEAR
    , @curdate) days_to_next_bd

and you can add a where condition where this number is, for example, BETWEEN 0 AND 7.

Your final query could then become:

SELECT *
FROM   table_birthdays
WHERE
  DATEDIFF(
    column_birthday +
      INTERVAL
        YEAR(CURDATE())-YEAR(column_birthday) +
        (MONTH(column_birthday)<MONTH(CURDATE())
         OR (MONTH(column_birthday)=MONTH(CURDATE())
             AND DAY(column_birthday)<DAY(CURDATE())))
      YEAR
    , CURDATE()) BETWEEN 0 AND 7

I actually ended up using this (I needed the query to only consider future dates & not include the dates 7 days prior from curdate, and this is how yours work), thank you. +1 — Mafia, Jul 08 '13 at 21:53

score 0 · Answer 3 · answered Jun 27 '13 at 08:15

You have an error in your query, there is no == operator in SQL - use = instead. Also, NOW is used differently.

SELECT * FROM table_birthdays WHERE strftime('%W', column_birthday) = strftime('%W', NOW())"

The reason you are getting false is because there was an error in processing the query. In such a situation, you can use the mysql_error() function to get a description of what went wrong with the last MySQL query you attempted.

There are also simpler ways to write this query, using DATEDIFF(), for example.

Dave · Answer 4 · 2013-06-27T08:25:01.347

0

SELECT * FROM table_birthdays WHERE DATEDIFF(NOW(),`birthday`)%365 >= 0 AND DATEDIFF(NOW(),`birthday`)%365 <= 7

Just date diff it if the date is > zero (today) and less than 7(1 week from now() ) then their birthday is within the next week so you can email them or whatever. don't forget you'll also need to set a flag to say they've been notified or whatever so that you don't do it again every day

As a note in point use >= <= rather than between as BETWEEN 0 and 7 will ignore days 0 (today) and 7 (week from now) so effectively BETWEEN 0 and 7 is the same as using >=1 and <=6

edited Jun 27 '13 at 08:25

answered Jun 27 '13 at 08:16

Dave

3,280
2
22
40

`NOW` is a function, and this only works for people who will be born up to six days in the future. – Niet the Dark Absol Jun 27 '13 at 08:16
I know now is a function doesn't always need () though and 7 days in the future is in the next week? or did weeks change from being 7 days to being 8 days ? – Dave Jun 27 '13 at 08:18
Like I said, it only works for people who haven't been born yet. You are including the year in your test. – Niet the Dark Absol Jun 27 '13 at 08:20
Ahh yeah forgot about the year count with you on that one now – Dave Jun 27 '13 at 08:22

MySQL Query: Retrieve 'birthdays' (YYYY-MM-DD) from the database, & select those that fall w/in the current week (regardless of year). / PHP

Language: PHP and MySQL

4 Answers4