Jump to content

Recommended Posts

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;

Link to comment
https://forums.phpfreaks.com/topic/228577-need-help-with-this-date-query/
Share on other sites

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.

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

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.

 

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

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)

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.