Reputation: 17701
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
Reputation: 1092
Here is a simple way:
SELECT * FROM MEMBER
WHERE TIMESTAMPDIFF(YEAR, members.member_dob, CURDATE()) BETWEEN 18 AND 24
Upvotes: 1
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
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
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