0

I am doing a mysql query and trying to get birthdays that match the current day and month.

Here is what I was trying but realized that it wont work properly:

$birthdays = $wpdb->get_results(" SELECT * FROM {$wpdb->usermeta} WHERE meta_key = 'birthday' AND meta_value >= UNIX_TIMESTAMP(DATE(NOW()))");

This will pull a birthday of course if the birthday is in this year (not what I need). the birthday is stored in the db as 1304035200

Any help would be greatly appreciated. I have read many of the related posts but they do not help with what I need.

Jay Schires
  • 1
  • 4

2 Answers2

0
select * from table where from_unixtime(field,'%m-%d') = right(curdate(),5)

$birthdays = $wpdb->get_results(" SELECT * FROM {$wpdb->usermeta} WHERE meta_key = 'birthday' and from_unixtime(meta_value,'%m-%d') = right(curdate(),5) ");
Nicola Cossu
  • 54,599
  • 15
  • 92
  • 98
  • not sure if this helps explain, but this pulls all birthdays $birthdays = $wpdb->get_results(" SELECT * FROM {$wpdb->usermeta} WHERE `meta_key` = 'birthday' "); – Jay Schires Apr 29 '11 at 02:37
  • Sorry. I don't know wordpress. My method is right but I don't know how table is structured. Are you sure that is there someone born today? – Nicola Cossu Apr 29 '11 at 02:49
  • Im positive. If I revert back to my orginal code and have the year the same, it pulls the birthday. The table is just meta_key = birthday and meta_value is where the birthdate is stored as 1304035200 – Jay Schires Apr 29 '11 at 02:51
  • I don't know what to say. select from_unixtime(1304035200,'%m-%d') = right(curdate(),5) returns 1. This means that if you have one record with that unix timestamp, the query must returns it. Bye. – Nicola Cossu Apr 29 '11 at 02:54
  • I have 1 and if i use this with both having the same year it does return it but not everyone is born in the same year $wpdb->get_results(" SELECT * FROM {$wpdb->usermeta} WHERE meta_key = 'birthday' AND meta_value >= UNIX_TIMESTAMP(DATE(NOW()))"); – Jay Schires Apr 29 '11 at 03:01
  • I have no idea what you're doing. select unix_timestamp('1990-04-29') -- 641340000 select from_unixtime(641340000,'%m-%d') = right(curdate(),5) As you can see this example matches and year of birth is 1990. Wait for other advices. I give up. Regards. – Nicola Cossu Apr 29 '11 at 03:10
  • Thats ok I appreciate the attempt...Im not worried about the year – Jay Schires Apr 29 '11 at 03:24
0

The easiest way might be to convert the unix timestamp to a date and compare it's month and day values with the month and day values of today. I'm on my iPhone so forgive formatting/typos but I think the following should work (waiting for the wife while she shops :))

SELECT * FROM {$wpdb->usermeta} WHERE meta_key = 'birthday' AND ((MONTH(FROM_UNIXTIME(meta_value)) = MONTH(DATE(NOW()))) AND (DAY(FROM_UNIXTIME(meta_value)) = DAY(DATE(NOW()))))

ljkyser
  • 999
  • 5
  • 9
  • Thats not pulling anything using $birthdays = $wpdb->get_results(" SELECT * FROM {$wpdb->usermeta} WHERE `meta_key` = 'birthday' AND ((MONTH(FROM_UNIXTIME(meta_value)) = MONTH(DATE(NOW()))) AND (DAY(FROM_UNIXTIME(meta_value)) = DAY(DATE(NOW()))))"); – Jay Schires Apr 29 '11 at 02:27
  • My hunch is that the server time is different than the birthday. What is the value of DATE(NOW()) on your server? I am not at my computer, so I can't test this, but I think you can do the following and look at the values: SELECT DAY(DATE(NOW())), MONTH(DATE(NOW())), DAY(FROM_UNIXTIME(1304035200)), MONTH(FROM_UNIXTIME(1304035200)); – ljkyser Apr 29 '11 at 02:57
  • Did you try the SQL I just posted? What did it output? We are only comparing the month and date with the code I posted, so the year shouldn't matter. The UNIX_TIMESTAMP is just the number of seconds since 1/1/1970, so it will find all birthdays in the future using your original code. – ljkyser Apr 29 '11 at 03:03
  • I do have birthdays set on the server the days before and after the given date all having the same year...they will all pull and display using my original code. I just want todays with no regard for the year – Jay Schires Apr 29 '11 at 03:26
  • So I just ran my query against my sql server and I am getting the correct day and month (29 and 4) for DATE(NOW()) and FROM_UNIXTIME(1304035200). Are you able to execute just the SQL part of the original query on your database (without going through wpdb)? – ljkyser Apr 29 '11 at 03:46
  • Also, will you post the complete row for the birthday with the value you have been testing so we can see the other values? I am confident the date part of it is working so I am trying to identify other issues that might be happening – ljkyser Apr 29 '11 at 03:48
  • Mine is printing out as April 29, 2011 the row is birthday 1304035200 – Jay Schires Apr 29 '11 at 03:53
  • The only values are user_meta 672 user_id 1 meta_key birthday meta_value 1304035200 – Jay Schires Apr 29 '11 at 03:54
  • And what was the error you get when you run this: $wpdb->get_results("SELECT DAY(DATE(NOW())), MONTH(DATE(NOW())), DAY(FROM_UNIXTIME(1304035200)), MONTH(FROM_UNIXTIME(1304035200))");? – ljkyser Apr 29 '11 at 03:58
  • call me dumb but what I get is Array – Jay Schires Apr 29 '11 at 04:05
  • That is what I would expect as we are telling it to return 4 numbers. What does this yield? `$birthdays = $wpdb->get_results(" SELECT * FROM $wpdb->usermeta WHERE meta_key = 'birthday' AND ((MONTH(FROM_UNIXTIME(meta_value)) = MONTH(DATE(NOW()))) AND (DAY(FROM_UNIXTIME(meta_value)) = DAY(DATE(NOW()))))");` and then `var_dump($birthdays);`? – ljkyser Apr 29 '11 at 04:11
  • Ok, what does `var_dump($wpdb->get_results("SELECT DAY(DATE(NOW())), MONTH(DATE(NOW())), DAY(FROM_UNIXTIME(1304035200)), MONTH(FROM_UNIXTIME(1304035200))"));` print out? – ljkyser Apr 29 '11 at 04:22
  • array(1) { [0]=> object(stdClass)#1100 (4) { ["DAY(DATE(NOW()))"]=> string(2) "28" ["MONTH(DATE(NOW()))"]=> string(1) "4" ["DAY(FROM_UNIXTIME(1304035200))"]=> string(2) "28" ["MONTH(FROM_UNIXTIME(1304035200))"]=> string(1) "4" } } – Jay Schires Apr 29 '11 at 04:26
  • Ok, that proves that the date matching is working and that the year does not matter for the comparison. Can you remove the space just before the word SELECT and make sure the curly brackets are removed from $wpdb->usermeta and see if that helps? I'm kind of taking stabs in the dark at this point given that the date portion appears to be working... – ljkyser Apr 29 '11 at 04:35
  • can you put this before your code `show_errors(); ?>` and this after it `hide_errors(); ?>` and see if that prints out any errors that might be hidden by wpdb? – ljkyser Apr 29 '11 at 04:42
  • ok...could you post the code that's actually being run, just copy and paste it in? – ljkyser Apr 29 '11 at 04:47
  • $birthdays = $wpdb->get_results("SELECT * FROM $wpdb->usermeta WHERE meta_key = 'birthday' AND ((MONTH(FROM_UNIXTIME(meta_value)) = MONTH(DATE(NOW()))) AND (DAY(FROM_UNIXTIME(meta_value)) = DAY(DATE(NOW()))))"); – Jay Schires Apr 29 '11 at 04:49
  • What does this return `var_dump($wpdb->get_results("SELECT * FROM $wpdb->usermeta WHERE meta_key = 'birthday' AND meta_value = 1304035200"));`? – ljkyser Apr 29 '11 at 05:02
  • ok, so it can't even find a row at all with the timestamp you gave, without even doing the date conversions. Can you verify the data is in the database and that you are querying the correct database? If it is in there and the meta_key is correct, it would have found it – ljkyser Apr 29 '11 at 05:10
  • sorry was playing with the dates array(1) { [0]=> object(stdClass)#1100 (4) { ["umeta_id"]=> string(3) "353" ["user_id"]=> string(1) "1" ["meta_key"]=> string(13) "birthday" ["meta_value"]=> string(10) "1303948800" } } – Jay Schires Apr 29 '11 at 05:16
  • Can you change the date back to 1304035200 and try this query again? `var_dump($wpdb->get_results("SELECT * FROM $wpdb->usermeta WHERE meta_key = 'birthday' AND ((MONTH(FROM_UNIXTIME(meta_value)) = MONTH(DATE(NOW()))) AND (DAY(FROM_UNIXTIME(meta_value)) = DAY(DATE(NOW()))))"));` – ljkyser Apr 29 '11 at 05:20
  • array(1) { [0]=> object(stdClass)#1100 (4) { ["umeta_id"]=> string(3) "353" ["user_id"]=> string(1) "1" ["meta_key"]=> string(13) "birthday" ["meta_value"]=> string(10) "1304035200" } } Birthdays: Jay Schires April 29, 2011 – Jay Schires Apr 29 '11 at 05:25
  • It worked! Looks like it was an issue of having the right data values in place. Does that answer the question then? – ljkyser Apr 29 '11 at 05:27
  • but why didnt it work before? I had just changed the date moments before? – Jay Schires Apr 29 '11 at 05:30
  • It could be an issue of timezones and the server time. What timezone is your server in? If it is in Hawaii for example, DATE(NOW()) will return a date of 4/28 even though where I am now it is 4/29 and it would cause a match to not occur. – ljkyser Apr 29 '11 at 05:32
  • hmmm good point! Im not really sure but will have to check on that! Thank you so much for not only your amazing help, but your patients as well...very much appreciated! – Jay Schires Apr 29 '11 at 05:33
  • Have another issue with it...if a users birthday is empty im getting: date() expects parameter 2 to be long. Any idea how to eliminate this? – Jay Schires May 03 '11 at 20:16
  • Take a look at the control flow functions using MySQL: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html. You could use `SELECT CASE WHEN (meta_key = 'birthday' AND IS NOT NULL(meta_value))` and then do the logic inside of there. – ljkyser May 04 '11 at 06:40