search help

[steviez]

Hi,

 

I am writing a php script to search through my database of users, I need one of the search criteria to be age (eg: between 30 and 70) but the only problem is that their age is not stored in the database only there date of birth.

 

Without rewriting my script or adding extra functions on search (not sure if its even possible) is there any way of doing this search?

 

Thanks

[rajivgonsalves]

you can use the following to get the age from the birthdate, birthdate_field will be your birthdate field in the database

 

floor(datediff(now(), birthdate_field) / 365) 

[steviez]

you can use the following to get the age from the birthdate, birthdate_field will be your birthdate field in the database

 

floor(datediff(now(), birthdate_field) / 365) 

 

Sorry I should have said, the birthdays are stored as: 2-02-1984.. I am starting to think that it may not be do-able

[rajivgonsalves]

you can use the STR_TO_DATE function in mysql to convert them to a proper format

 

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date

 

although I would recommend storing them in YYYY-MM-DD format

[steviez]

you can use the STR_TO_DATE function in mysql to convert them to a proper format

 

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date

 

I am a complete SQL nOOob, would you be able to show me sample syntax?

[PFMaBiSmAd]

Except that there are not 356 days in every year and that formula will be off by one day for every leap year the person has been alive (matters to those who's age is being checked close to the day of their birthday.)

 

See the age calculation at this link - http://dev.mysql.com/doc/refman/5.0/en/date-calculations.html (basically take the difference in years, subtract one if the birthday has not occurred yet in the current year.)

[PFMaBiSmAd]

You need to fix your table design so that you store dates using the DATE data type. As is, you will be in a loosing battle with every query that attempts to find or sort by your dates.

[steviez]

I have no idea how to do any of that im afraid... Im a complete noob, so I think I will have to alter my script some how.

[steviez]

there should be no problem in grabbing the date of birth and working out the age with php then adding it to a row in ther users database should there? I can then do a simple search query

[PFMaBiSmAd]

then adding it to a row in the users database should there

 

That would result in duplicate/derived data. It would also create a data management problem because a person's age is not fixed (unless they are dead.) You would need to recalculate the age every time you used it in case their birthday date went past.

 

The link I posted above to the mysql manual section shows a query that calculates the age. You can then use the age value in that same query any way you want.

[steviez]

then adding it to a row in the users database should there

 

That would result in duplicate/derived data. It would also create a data management problem because a person's age is not fixed (unless they are dead.) You would need to recalculate the age every time you used it in case their birthday date went past.

 

The link I posted above to the mysql manual section shows a query that calculates the age. You can then use the age value in that same query any way you want.

 

I have done what is says on that page but it does not return there age, my query is here:

 

"SELECT mm_users.user_id, mm_users.user_username, mm_users.user_photo, mm_users.user_signupdate, mm_users.user_lastlogindate, mm_users.user_dateupdated, mm_users.user_birthday, CURDATE(), (YEAR(CURDATE())-YEAR(mm_users.user_birthday)) - (RIGHT(CURDATE(),5)<RIGHT(mm_users.user_birthday,5)) AS age FROM mm_profiles LEFT JOIN mm_users ON mm_profiles.profile_id = mm_users.user_id WHERE mm_users.user_privacy_search = '1' $bquery"

[PFMaBiSmAd]

it does not return there age

 

Yes, but what DOES it return? And as previously mentioned in the thread, what data type is mm_users.user_birthday?

[steviez]

it does not return there age

 

Yes, but what DOES it return? And as previously mentioned in the thread, what data type is mm_users.user_birthday?

 

I get no result its blank, I am not sure what you mean by data type... If you mean the database row type then its varchar(20)

[steviez]

I have just found my mistake there, it needs to be date format, why does it only allow yyyy-mm-dd though? I am UK based and wanted dd-mm-yyyy

[PFMaBiSmAd]

I am UK based

Computers don't really care about that. Formats like dd-mm-yyyy or mm/dd/yyyy are human conventions and have no significance to a computer. Computers care about things like speed and storage requirements.

 

The YYYY-MM-DD format exists for a couple of reasons -

 

1) When the fields (y, m, d) are in that specific left-to-right order, the values can be directly sorted and compared using less-than/greater-than comparisons,

2) By using a consistent, fixed format, the amount of storage has been minimized and the code has been optimized to result in the fastest queries.

 

There are also a couple dozen built in data/time functions that can be used once your dates are in the expected DATE data type.

 

To output a DATE data type in any format you want, simply use the mysql DATE_FORMAT() function in your query when you SELECT the data.

[steviez]

I am UK based

Computers don't really care about that. Formats like dd-mm-yyyy or mm/dd/yyyy are human conventions and have no significance to a computer. Computers care about things like speed and storage requirements.

 

The YYYY-MM-DD format exists for a couple of reasons -

 

1) When the fields (y, m, d) are in that specific order, the values can be directly sorted and compared using less-than/greater-than comparisons,

2) By using a consistent, fixed format, the amount of storage has been minimized and the code has been optimized to result in the fastest queries.

 

There are also a couple dozen built in data/time functions that can be used once your dates are in the expected DATE data type.

 

To output a DATE data type in any format you want, simply use the mysql DATE_FORMAT() function in your query when you SELECT the data.

 

Thank you so so much for your help

[steviez]

My query is not working for the search criteria.. after my initial query I have extra statments like: AND username = 'me'

 

The following query wont work:

 

" AND (YEAR(CURDATE())-YEAR(user_birthday)) - (RIGHT(CURDATE(),5)<RIGHT(user_birthday,5)) between '%{$_GET['age_from']}%' and '%{$_GET['age_to']}%'"

 

Any help?

[fenway]

That's not a query, that's php code.

 

And what does "not work" mean?

[steviez]

dont matter now, I got it fixed... thanks for all your help guys.