CODEWITHSUNDEEP
mysqlsql
Glory Raj
Glory Raj

Reputation: 17701

filtering members based on the age using mysql

I am trying to check for people in a certain age range. I have two ComboBoxes, minagecombobox and maxagecombobox, with values between 1 and 120.

I want to get the people whose age is between these two values (suppose for example I want to filter out the people aged between 18 and 24).

This is the Member table structure:

     member_id 
     member_firstname 
     member_dob(datatype is string) (values like 1987-09-08,1976-09-08,.....)

i am using mysql as my database.....

**Modified query** 


             SELECT members.member_Id,
                   members.member_Lastname as 'Last Name',
                   members.member_Firstname as 'First Name',
                   members.member_Postcode as 'Post Code',
                   members.member_Reference as Reference,
                   members.member_CardNum as 'Card Number',
                   members.member_IsBiometric as Biometric,
                   members.member_DOB as DoB,
                   mshiptypes.mshipType_Name as Membership,
                   mshipstatustypes.mshipStatusType_Name as Status,
                   membertomships.memberToMship_EndDate as Expiry
                     FROM members
    INNER JOIN membertomships ON membertomships.member_Id = members.member_Id
     INNER JOIN mshipoptions ON mshipoptions.mshipOption_Id = membertomships.mshipOption_Id
    INNER JOIN mshiptypes ON mshiptypes.mshipType_Id = mshipoptions.mshipType_Id
    INNER JOIN mshipstatustypes ON mshipStatusTypes.mshipStatusType_Id = membertomships.mshipStatusType_Id
    WHERE memberToMship_EndDate BETWEEN '2011-09-24' AND '2011-09-30'
    AND members.member_active LIKE 'y%'
    AND (YEAR(CURDATE(members.member_Dob))-YEAR()) - (RIGHT(CURDATE(),5)< RIGHT(member_Dob,5)) BETWEEN '21' AND '102'
    ORDER BY members.member_Lastname

got an error like this.....

     Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'member_Dob))-YEAR()) - (RIGHT(CURDATE(),5)< RIGHT(member_Dob,5)) BETWEEN '21' AN' at line 19

Upvotes: 1

Views: 2885

Answers (4)

arrmani88
arrmani88

Reputation: 1092

Here is a simple way:

SELECT * FROM MEMBER
WHERE TIMESTAMPDIFF(YEAR, members.member_dob, CURDATE()) BETWEEN 18 AND 24

Upvotes: 1

Aziz Shaikh
Aziz Shaikh

Reputation: 16544

Try this:

SELECT * FROM MEMBER
WHERE (YEAR(CURDATE())-YEAR(member_dob)) - (RIGHT(CURDATE(),5)<RIGHT(member_dob,5)) 
BETWEEN 18 AND 24

The WHERE clause has the age calculation formula. Get the number of years by subtracting the year of birth from current year and then further subtract 1 if one current day and month is less than the day and month of DOB. In case, you get a datatype mismatch error, use STR_TO_DATE(member_dob, '%Y-%M-%d') to convert the string DOB to DATETIME DOB.

Upvotes: 2

Dan
Dan

Reputation: 4663

Not exactly sure what you are trying to do. Your question doesn't make much sense since combo boxes aren't mySQL, they are whatever language you are using to interact with the database. You would be better programmatically determining the date range and passing the dynamic variables to mySQL, but here is a concept for now. Also, why is member_dob a string? Make it a datetime.... 

SELECT * FROM Member 
WHERE STR_TO_DATE(member_dob, '%Y-%M-%d') BETWEEN '1900-0916' AND CURDATE()

Upvotes: 1

Piskvor left the building
Piskvor left the building

Reputation: 92792

Refactor so that member_dob is of type DATE (or another date-related datatype), then use SELECT member_id, member_firstname, member_dob FROM Member WHERE member_dob BETWEEN '1993-09-16' AND '1987-09-16' (note that 1993==2011-18 , 1987==2011-24 , month and day are from today).

You'll need to convert the date of birth to some sort of date anyway - this approach allows you to use the database for data searching, something it's pretty good at (instead of just using it as a data store).

Upvotes: 0

Related Questions