kevinkhan Posted February 23, 2011 Share Posted February 23, 2011 CREATE TABLE IF NOT EXISTS `friend_req` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `profilelink` varchar(1024) COLLATE utf8_unicode_ci DEFAULT NULL, `name` text CHARACTER SET latin1 NOT NULL, `email` text CHARACTER SET latin1 NOT NULL, `dateOfBirth` date NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=28135 ; The above is my database structure. I am looking for a query that will extract records from the database where peoples age is 20years and 335 days old. ie 30 days before there 21st birthday. Any ideas how i could do this so far i have SELECT id,user,profilelink,name,email,dateOfBirth FROM `friend_req` WHERE dateofBirth = Limit 5; Quote Link to comment https://forums.phpfreaks.com/topic/228577-need-help-with-this-date-query/ Share on other sites More sharing options...
Muddy_Funster Posted February 23, 2011 Share Posted February 23, 2011 something along the lines of : SELECT id,user,profilelink,name,email,dateOfBirth FROM `friend_req` WHERE (SELECT (DATEDIFF(CURDATE(), dateOfBirth) / 365.25) FROM friend_req) = ((21*365.25)-30) Limit 5; Havn't tested it, but I used something simmilar about a year ago to calculate employee ages for a pension program, so should work with a little tweeking. Quote Link to comment https://forums.phpfreaks.com/topic/228577-need-help-with-this-date-query/#findComment-1178568 Share on other sites More sharing options...
kevinkhan Posted February 23, 2011 Author Share Posted February 23, 2011 ok thanks.. and just one last question im trying to update a record in my database so that the users age will always be 30 days before his 21st birthday ie 7640 days old. how would i write this i have the following equation but dont know how to get the correct dateOfBirth out of it... DATEDIFF( DATE_FORMAT( NOW( ) , '%Y-%m-%d' ) , `dateOfBirth` ) =7650 Quote Link to comment https://forums.phpfreaks.com/topic/228577-need-help-with-this-date-query/#findComment-1178570 Share on other sites More sharing options...
Muddy_Funster Posted February 23, 2011 Share Posted February 23, 2011 ok thanks.. and just one last question im trying to update a record in my database so that the users age will always be 30 days before his 21st birthday ie 7640 days old. how would i write this i have the following equation but dont know how to get the correct dateOfBirth out of it... DATEDIFF( DATE_FORMAT( NOW( ) , '%Y-%m-%d' ) , `dateOfBirth` ) =7650 I don't understand what you are trying to do here...People get older, as the current date increses, so will there age. are you trying to force their dateOfbirth field to be constantly set to what it would be were they born 20 years and 335 days ago? I'm confused, what you are suggesting makes no logical sense without any context to go with it. Could you tell us a bit more about what you are actualy trying to achieve here? P.S. CURRDATE() is the same as DATE_FORMAT(now(), '%y-%M-%d') - just a nicer way of doing it. Quote Link to comment https://forums.phpfreaks.com/topic/228577-need-help-with-this-date-query/#findComment-1178577 Share on other sites More sharing options...
kevinkhan Posted February 23, 2011 Author Share Posted February 23, 2011 I don't understand what you are trying to do here...People get older, as the current date increses, so will there age. are you trying to force their dateOfbirth field to be constantly set to what it would be were they born 20 years and 335 days ago? I'm confused, what you are suggesting makes no logical sense without any context to go with it. Could you tell us a bit more about what you are actualy trying to achieve here? P.S. CURRDATE() is the same as DATE_FORMAT(now(), '%y-%M-%d') - just a nicer way of doing it. Its only for testing porposses . i want send a messages to people that are nearly 21 and i want to reset one link any time i run my script to test to see if a message as been sent Quote Link to comment https://forums.phpfreaks.com/topic/228577-need-help-with-this-date-query/#findComment-1178855 Share on other sites More sharing options...
Pikachu2000 Posted February 23, 2011 Share Posted February 23, 2011 This should do the trick. Just know that if you miss a day, it won't work for the records that have been missed. You'd need to set a 'flag' field to indicate whether a message has been sent, and use a >= comparison, along with checking the flag field to overcome that. SELECT . . . FROM . . . WHERE CURDATE() = DATE_SUB(`birthdate`, INTERVAL 30 DAYS) Quote Link to comment https://forums.phpfreaks.com/topic/228577-need-help-with-this-date-query/#findComment-1178867 Share on other sites More sharing options...
kevinkhan Posted February 23, 2011 Author Share Posted February 23, 2011 its actually an up date i want to do.. will this still work? Quote Link to comment https://forums.phpfreaks.com/topic/228577-need-help-with-this-date-query/#findComment-1178869 Share on other sites More sharing options...
Pikachu2000 Posted February 23, 2011 Share Posted February 23, 2011 I don't see why it wouldn't, as long as the rest of the syntax is valid. Quote Link to comment https://forums.phpfreaks.com/topic/228577-need-help-with-this-date-query/#findComment-1178881 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.