Page 1 of 1

HELP! AGE SEARCH - PHP / MYSQL DATABASE

Posted: Thu Jan 17, 2008 12:04 am
by derniercri
Help please!

I have a script and need to include an age search option.


How can I do this?

I want the form to include the following (sample) age search options:

<select name='AgeMin'>
<option value></option>
<option value>21</option>
<option value>22</option>
<option value>23</option>
<option value>24</option>
<option value>25</option>
<option value>26</option>
<option value>27</option>
<option value>28</option>
<option value>29</option>
<option value>30</option>
</select>

<select name='AgeMax'>
<option value></option>
<option value>21</option>
<option value>22</option>
<option value>23</option>
<option value>24</option>
<option value>25</option>
<option value>26</option>
<option value>27</option>
<option value>28</option>
<option value>29</option>
<option value>30</option>
</select>


Currently the age is calculated for site users from the input of birth_date, birth_month and birth_month in the database.


How do I complete a search and display these users for users in an age range?

Example .. Search Age: Between (AgeMin) and (AgeMax)



Thank you greatly!

Re: HELP! AGE SEARCH - PHP / MYSQL DATABASE

Posted: Thu Jan 17, 2008 12:13 am
by SidewinderX
When the forum is submitted [and after you have validated your input], use the WHERE clause in your SQL query to match your specific criteria. Once you have the query, loop through the data and print it out.

Re: HELP! AGE SEARCH - PHP / MYSQL DATABASE

Posted: Thu Jan 17, 2008 12:17 am
by jimthunderbird
maybe something like

Code: Select all

 
 select [your user data fields] from [your table] where concat(birth_year,"-",birth_month,"-",birth_day) between "1900-01-01" and "2199-01-01"
 
Hope this gives you some hints.

Re: HELP! AGE SEARCH - PHP / MYSQL DATABASE

Posted: Thu Jan 17, 2008 7:03 am
by derniercri
I am a total n00b.. can someone outline the proper structure please!

Re: HELP! AGE SEARCH - PHP / MYSQL DATABASE

Posted: Thu Jan 17, 2008 9:38 pm
by califdon
I would calculate the age of each person, as of the date of the run, in your SQL query, then use the WHERE clause of the SQL query to select the records that are between the selected AgeMin and AgeMax values. Calculating the age will be a little difficult with the way your birth dates are stored, as 3 separate fields for month, day and year. I won't even attempt to go into the details here. Basically, you have to combine those 3 fields to generate a real date in standard format, then subtract the birthdate from the current date, to get the age.

Re: HELP! AGE SEARCH - PHP / MYSQL DATABASE

Posted: Tue Jan 22, 2008 1:30 pm
by derniercri
ok.. so my script calculates age by the following:

Code: Select all

    function age($day,$month,$year) {
     
    $d_stamp = date("d.m.Y",mktime());
     
    list($cur_day,$cur_month,$cur_year) = explode(".",$d_stamp);
     
    $year_diff = $cur_year-$year;
     
    if(($month > $cur_month) || ($month == $cur_month && $cur_day < $day)) {
          
    $age = $year_diff-1; } else { $age = $year_diff; }
    
    return $age;
    
    }

I now need to do a search for age ranges of users:

<18

18-25

25 - 35

35 - 45

45 - 55

55>

can someone assist me with the code for this? much appreciated!

Re: HELP! AGE SEARCH - PHP / MYSQL DATABASE

Posted: Tue Jan 22, 2008 1:53 pm
by Jonah Bron
If you need to know how to use MySQL in PHP, checkout About.com and w3Schools.com
Some greate tutorials there.

Re: HELP! AGE SEARCH - PHP / MYSQL DATABASE

Posted: Tue Jan 22, 2008 1:58 pm
by derniercri
thank you for that - but also defeats the purpose of requesting for assistance in these wonderful forums here at this site.

Re: HELP! AGE SEARCH - PHP / MYSQL DATABASE

Posted: Tue Jan 22, 2008 7:18 pm
by califdon
I can only tell you that I would not try to do it with the table structure you currently have. It can certainly be done, but I wouldn't waste my time with it. I would add a field for the correct date format of the birthdate and run an Update that populates the table with the calculated dates. That would make it a trivial SQL statement to retrieve the data that you want, as well as making the table make sense. I'm sorry to be so blunt, but I just don't have the patience to try working out some elaborate way of handling data that is stored incorrectly to begin with. It's a frequent frustration for me in these forums.

Re: HELP! AGE SEARCH - PHP / MYSQL DATABASE

Posted: Tue Jan 22, 2008 8:22 pm
by Christopher
derniercri wrote:thank you for that - but also defeats the purpose of requesting for assistance in these wonderful forums here at this site.
It actually enhances your experience to seek out multiple sources of information. The wonderful volunteers here are glad to offer assistance, but will not do your work for you.

In you first post you request "an age search option" for you site. I am assuming from the form fields you show, and that you mention MySQL in the title, that you would like to return all the database records for people with ages between the values selected.

You don't however say whether the age or birth date are stored in the data table. Storing the birth date would obviously make the most sense so you don't need to continuously update ages. Given my understanding of time, to find people in a age range you would look for records with birth dates between two dates in the past. Those dates would be calculated by subtracting the min and max ages from today's date.

Give a try at completing the HTML form to submit the two values, and creating the PHP script that will receive the two values and do the query. Post them and people can provide you with feedback.