Page 1 of 1

Users birthday list

Posted: Fri Sep 05, 2008 7:34 pm
by adaywalkr
Hello,

I'm implanting a "Birthday's Today" function in my script. In the user's profile users can enter their birthday date (d-m-Y). Now I need to compare the day and month with the current day and month, and not the year.

This is my query as it is now:

Code: Select all

$birthday_query = mysql_query("SELECT userid, username, birthday FROM users WHERE birthday = '". date("d-m-Y") ."' AND status = '1' ORDER BY username ASC");
This will only display the users that are born on this exact day :roll:

Not that it's a problem to just add another field for year, but as I'm learning PHP I would like to know what the smartest way would be to do this.

Thanks in advance!

Re: Users birthday list

Posted: Fri Sep 05, 2008 8:27 pm
by Cut
Use the LIKE condition:

Code: Select all

WHERE birthday LIKE '". date("d-m") ."%'
'%' is a wildcard. Remember if you use LIKE elsewhere with user input to sanitize it for _ and %: real_escape doesn't do the trick.

Re: Users birthday list

Posted: Fri Sep 05, 2008 8:40 pm
by adaywalkr
Thanks Cut, got it working now

Re: Users birthday list

Posted: Sat Sep 06, 2008 4:13 am
by onion2k
Definitely don't use LIKE. It'll be hellishly slow if you get lots of users.

MySQL has some built it date functions you could (and should) use:

Code: Select all

SELECT `users`.* FROM `users` WHERE MONTH(`birthday`) = MONTH(CURDATE()) AND DAY(`birthday`) = DAY(CURDATE())
This is assuming that the birthday column is a date field (date, datetime, timestamp, etc).

Re: Users birthday list

Posted: Sat Sep 06, 2008 9:15 am
by adaywalkr
Thanks mate

Where is the .* after users for?
And let's say i was in the same situation but not using date/time? Am I forced to use LIKE then?

Re: Users birthday list

Posted: Sat Sep 06, 2008 9:40 am
by onion2k
* is just a wildcard for 'all the columns in the table', so `users`.* just gets everything from the users table. Generally it's better to specify what columns you want but I don't know what your schema is.

As a rule only use LIKE if you absolutely have to, eg in a partial text search. It's very slow compared to all the normal operations like =.

Re: Users birthday list

Posted: Sat Sep 06, 2008 10:16 am
by adaywalkr
I see, I'm used to using * instead of `table`.*

I save the birthday data like they insert it, not the timestamp (eg. 18-01-1977)
The method you described should work if I save the date as Unix Timestamp

Edit: Timestamp counts from 1970, so I can't even use it for birth dates?

Re: Users birthday list

Posted: Sat Sep 06, 2008 11:15 am
by onion2k
No, don't use timestamps to store dates. Use a date type.

Re: Users birthday list

Posted: Sat Sep 06, 2008 11:24 am
by adaywalkr
How stupid of me, ofcourse you don't save date with timestamp

Thanks for everything