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 Link to comment https://forums.phpfreaks.com/topic/71057-solved-date-search/ 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. Link to comment https://forums.phpfreaks.com/topic/71057-solved-date-search/#findComment-357271 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? Link to comment https://forums.phpfreaks.com/topic/71057-solved-date-search/#findComment-357281 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? Link to comment https://forums.phpfreaks.com/topic/71057-solved-date-search/#findComment-357284 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. Link to comment https://forums.phpfreaks.com/topic/71057-solved-date-search/#findComment-357318 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. Link to comment https://forums.phpfreaks.com/topic/71057-solved-date-search/#findComment-357381 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... Link to comment https://forums.phpfreaks.com/topic/71057-solved-date-search/#findComment-357408 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... Link to comment https://forums.phpfreaks.com/topic/71057-solved-date-search/#findComment-357442 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. Link to comment https://forums.phpfreaks.com/topic/71057-solved-date-search/#findComment-357443 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 Link to comment https://forums.phpfreaks.com/topic/71057-solved-date-search/#findComment-357468 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. Link to comment https://forums.phpfreaks.com/topic/71057-solved-date-search/#findComment-357474 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 Link to comment https://forums.phpfreaks.com/topic/71057-solved-date-search/#findComment-357492 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. Link to comment https://forums.phpfreaks.com/topic/71057-solved-date-search/#findComment-357688 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.