Can't query for Birthday

djburner

HI there, I want to make a Birthday system but Im stuck in how to format the DATE.

$myquery = "SELECT username,bd,email FROM $tbl_users WHERE DATE_FORMAT(NOW(),'%m-%d')= DATE_FORMAT(bd),'%m-%d'";

Then with the results send emails with PHP but it doesn't work. No matches even thoug I filled some fake users with today's date.

How can I do this?

My bd table is a DATE so it is yy:mm:dd.
Hope somebody can give a hand on this one.

Greetings.

johanafm

When posting code, always wrap it in appropraite tags: php, html or code tags.

When coding, always use an editor with syntax highliting.

djburner;10990146 wrote:

WHERE DATE_FORMAT(NOW(),'%m-%d')= DATE_FORMAT(bd),'%m-%d';

Apart from not being syntactically correct (more on that below), why do you even bother formatting things when comparing them? The user won't even see it. You achieve nothing but doing needless extra work.

Well, NOW() has to be changed into something matching a date, but that is most easily accomplished by using DATE(). Modifying data from each and every row to see if it matches a non-standard format after transformation can be skipped if you stick to formatting NOW() insto a standard date format.

djburner;10990146 wrote:
$myquery = "SELECT username,bd,email FROM $tbl_users
WHERE DATE_FORMAT(NOW(),'%m-%d')= DATE_FORMAT(bd),'%m-%d'";
Then with the results send emails with PHP but it doesn't work. No matches even thoug I filled some fake users with today's date.

No matches? Are you certain there are no matches? I'm willing to bet that the reason you see nothing is that you have a SQL syntax error, which you would see if you set php to log/display errors.

How can I do this?

djburner;10990146 wrote:
My bd table is a DATE so it is yy:mm:dd.

No, it's yyyy-mm-dd.

djburner

Hi johanafm, I got a little confused so Im going to tell you what I would like to do.

I input my users Birthday using the DATE format in my mySQL. So it is 20111102 (todays). If I input a birthday like username: John mail: john@mail.com bd:20111102 with this query

$myquery = "SELECT username,email, FROM $tb_users WHERE NOW()= bd";

I got the username and the email of John and the system send the "congratulation email", but as you can see this is not supposed to work because it uses the year, this is where Im stuck, How can I compare todays date with my Date column without taking consideration of the year, so if the query looks for results it will look for mathes with just the month and the day.

Is that possible?.

Greetings.

djburner

Hi johanafm, I got a little confused so Im going to tell you what I would like to do.

I input my users Birthday using the DATE format in my mySQL. So it is 20111102 (todays). If I input a birthday like username: John mail: john@mail.com bd:20111102 with this query

$myquery = "SELECT username,email, FROM $tb_users WHERE NOW()= bd";

Is that possible?.

Greetings.

bradgrafelman

djburner;10990476 wrote:
I input my users Birthday using the DATE format in my mySQL. So it is 20111102 (todays).

Well now which is it... are you using DATE or are you using the format '20111102' (which is most definitely not 'YYYY-MM-DD').

As for your question, use MySQL's Date and Time Functions to compare the current month to the month portion of the birthdate and the current day to the day portion of the brithdate.

EDIT: I suppose you could also use something like RIGHT() to compare the right-most 5 characters ("MM-DD") of both the birthdate and the current date.

djburner

Hi bradgrafelman, I have a table in mySQL with:

username: varchar
email: varchar
Birthday: DATE

I input in the DATE 2011-11-02 for example to use succesfully the query that I posted, and it works

$myquery = "SELECT username,email, FROM $tb_users WHERE NOW()= bd";

But as you can see the NOW() take in consideration the year, and thats the problem.

I try to used DATE_FORMAT but im totally stuck and confused, I dont know how can I do using the built it function to do what I want.

I really will appreciate any advice.

Greetings.

bradgrafelman

djburner;10990480 wrote:
I try to used DATE_FORMAT but im totally stuck and confused, I dont know how can I do using the built it function to do what I want.

Well I gave you two ideas above (extracting the YEAR and MONTH from both dates, as well as doing a simple string comparison where you ignore the first 5 characters, e.g. "YYYY-MM-DD"). Using DATE_FORMAT() to format both date strings is yet another way (and there are still more possibilities than just these three).

Can you show us what you've tried?

djburner

My first approach was:

$myquery = "SELECT username,email FROM $tbl_users
WHERE DATE_FORMAT(NOW(),'%m-%d')= DATE_FORMAT(bd),'%m-%d'";

But I got not positive results, even though that I have a user (made by myself) with todays birthday.

I used the RIGHT function:

$myquery = "SELECT username,email  FROM $tbl_users WHERE RIGHT(NOW(),5)= bd";

But it doesn't work to me. I really have none experience so it is way difficult to me to understand behind the scenes what is happening or actually if I’m writing correctly the query.

Thank for your help.
Greetings.

bradgrafelman

djburner;10990483 wrote:
DATE_FORMAT(NOW(),'%m-%d')= DATE_FORMAT(bd),'%m-%d'"; [/CODE]

Careful... look at the right-hand side of that comparison. You're calling DATE_FORMAT() with only a single parameter whereas it expects 2. In addition, you've got a comma and a string following the (erroneous) call to DATE_FORMAT().

This query won't even get executed - you should have gotten an error message from MySQL along the lines of:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'DATE_FORMAT'

(Let me rephrase... you did get such an error message; whether or not you actually checked for a failing query and/or retrieved and logged the SQL error message, well that's up to you to do.)

djburner;10990483 wrote:
RIGHT(NOW(),5)= bd";

Again, carefully read what you're comparing there. On the left-hand side, you've got part of a time (e.g. "16:59" - 16 minutes, 59 seconds), and on the right-hand side you've got an unmodified date.

Thus, you're trying to compare something like "16:59" with "2011-11-03". Notice anything wrong?

(I noticed two things; don't use NOW() if you don't want the time as well, and don't modify/truncate one date value and try to compare it to another unmodified date value.)

djburner;10990483 wrote:
it is way difficult to me to understand behind the scenes what is happening

Note you can use SELECT to see the values of expressions. For example, you can simply execute this query:

SELECT RIGHT(NOW(), 8);

in MySQL in case you weren't sure of the output.... which was something like:

mysql> SELECT RIGHT(NOW(),8);
+----------------+
| RIGHT(NOW(),8) |
+----------------+
| 00:23:37       |
+----------------+
1 row in set (0.00 sec)

djburner

Well I have really positive results so far (thank you), I used this query:

SELECT username,email, FROM $tbl_users WHERE RIGHT(CURDATE(),5);= RIGHT(bd,5)

And finally (thanks to your advices) I get 11-02 on my left hand, but Im not quite sure how to use the RIGHT function with the column bd which contents my birthdays.

How could I do to get just the 11-02 from my bd to get a math?

Thank you so much.
Greetings.

johanafm

I missed that you were checking for birthdays, which is why I initially recommended not formatting dates when comparing them.

Assuming bd is of type DATE which it should be, RIGHT(bd, 5) should work. If it's DATETIME, you'd be better off using DATE_FORMAT

DATE_FORMAT(bd, '%m-%d)

-- As brad pointed out, the issue you initially had with this was that you wrote
DATE_FORMAT(bd), '%m-%d  -- which is not correct

Also note that you can always check what something is by selecting it as if it were a field to inspect the results manually

SELECT RIGHT(bd, 5), RIGHT('2011-11-01', 5), DATE_FORMAT(bd, '%m-%d'), DATE_FORMAT('2011-11-01')
FROM tbl_users;

If your bd column is not of type date, you should change it to be so. Well, there is one other viable option, and that is storing date(time)s as unix timestamps, which require an INT UNSIGNED. However, when looking at your tables, it's easier to interpret the data when using date or datetime. You'd also have to convert between dates and unix timestamps, which is done with UNIX_TIMESTAMP(date) and FROM_UNIXTIME(timestamp)

djburner

Hi johanafm. It's DONE! thank u so much!, my system is working and sending the dedicated emails.

I really appreciate your time and knowledge and the same goes to the other users that gave me a hand.

This is the working code.

SELECT username,email FROM jos_users WHERE RIGHT(CURDATE(),5)= RIGHT(bd,5)

Greettings

NogDog

How about...

...WHERE MONTH(CURDATE()) = MONTH(bd) AND DAYOFMONTH(CURDATE()) = DAYOFMONTH(bd)

...?