ainoy31 Posted September 28, 2007 Share Posted September 28, 2007 Hello- I am designing a mailing list and trying to do a manual search for birthdays and anniversaries. I am needing to search for any birthdays within 2 weeks from the current time. Here is my query: $sql = "SELECT * FROM ml_users WHERE user_bday BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 14 DAY)"; Any suggestion is helpful. ty Quote Link to comment Share on other sites More sharing options...
fenway Posted September 28, 2007 Share Posted September 28, 2007 Birthdays are funny things... the mysql refman page on date/time functions actually has a rather lengthy discussion on this. Ultimately, you simply need to "swap out" the year with the current year. Quote Link to comment Share on other sites More sharing options...
ainoy31 Posted September 28, 2007 Author Share Posted September 28, 2007 How does one go about swapping a year with the current one? Quote Link to comment Share on other sites More sharing options...
ainoy31 Posted September 28, 2007 Author Share Posted September 28, 2007 Can we just only compare the month and day? Quote Link to comment Share on other sites More sharing options...
fenway Posted September 28, 2007 Share Posted September 28, 2007 Did you read that discussion? It's rather in-depth, and has many solution. Quote Link to comment Share on other sites More sharing options...
ainoy31 Posted September 28, 2007 Author Share Posted September 28, 2007 OK. I found a work around the year. I changed my birthday and anniversart data type to varchar with the input format of "mmdd". I am now able to query successfully. $sql = "SELECT * FROM ml_users WHERE user_bday = date_format(now(),'%m%d')"; The only problem now is that this searches the current day but what if I want to search up to 2 weeks from current date. Any suggestion is appreciated. Quote Link to comment Share on other sites More sharing options...
fenway Posted September 28, 2007 Share Posted September 28, 2007 OK. I found a work around the year. I changed my birthday and anniversart data type to varchar with the input format of "mmdd". I am now able to query successfully. $sql = "SELECT * FROM ml_users WHERE user_bday = date_format(now(),'%m%d')"; The only problem now is that this searches the current day but what if I want to search up to 2 weeks from current date. Any suggestion is appreciated. Wow, that's a terrible solution. Leave it as a day, convert it the current year, and then use between... Quote Link to comment Share on other sites More sharing options...
ainoy31 Posted September 28, 2007 Author Share Posted September 28, 2007 i guess everyone is subjected to his or her opinion... Quote Link to comment Share on other sites More sharing options...
fenway Posted September 28, 2007 Share Posted September 28, 2007 i guess everyone is subjected to his or her opinion... Storing dates as anything but dates -- that's not my opinion, that's plain data corruption. Since obviously you don't feel like doing any research of your own, try this refman page. Quote Link to comment Share on other sites More sharing options...
ainoy31 Posted September 28, 2007 Author Share Posted September 28, 2007 maybe the way you called my idea TERRIBLE. i do appreciate help and tips from this forum but one should be more careful on their wordings... TY Quote Link to comment Share on other sites More sharing options...
fenway Posted September 28, 2007 Share Posted September 28, 2007 maybe the way you called my idea TERRIBLE. i do appreciate help and tips from this forum but one should be more careful on their wordings... thank you There are plenty of terrible ideas on this forum, and I'll continue to point them out... like storing "lists" in the single field, trying to change the auto_increment value, etc. Besides, terrible doesn't always mean bad per se... just extremely distressing. And more than that, I haven't seen any attempt to implement the plethora of solutions provided in the link I gave you. Quote Link to comment Share on other sites More sharing options...
ainoy31 Posted September 28, 2007 Author Share Posted September 28, 2007 Cool. The issue is solved. Thanks man. I was checking out different things. Followed your suggestion and it is working. Here is my solution: $sql = "SELECT * FROM ml_users WHERE (DAYOFYEAR(curdate()) <= dayofyear(user_bday) AND DAYOFYEAR(curdate()) + 30 >= dayofyear(user_bday) OR DAYOFYEAR(curdate()) <= dayofyear(user_bday) + 365 AND DAYOFYEAR(curdate()) + 30 >= dayofyear(user_bday) + 365)"; TY. AM Quote Link to comment Share on other sites More sharing options...
fenway Posted September 29, 2007 Share Posted September 29, 2007 Glad you got it working... after all, that's why we're all here. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.