age/year dropdown

[adamlacombe]

Okay I am trying to list out years in a dropdown box. It should show say how many years old. I want to start from 16 though and stop at 100. So the year has to be 1995 for 16 but then I would have to change the code every year. so I was wondering how do I modify this code to do what I explained?

<?php
$start_year = ($start_year) ? $start_year - 1 : date('Y') - 100;
    $end_year = ($end_year) ? $end_year : date('Y');
for ($i = $end_year; $i > $start_year; $i -= 1) {

$date=date(Y);
$age = $i - $date;

        echo '<option value="'.$i.'">'.$age.'</option>';
    }
?>

 

Thanks in advanced

[Pikachu2000]

Not everyone born in 1995 is 16 years old yet, so using the year as the value in the <option> tags isn't going to be accurate. Either use the year for both the value and the display, or use the age for both. This would list the years:

$start = date('Y') - 16;
$end = date('Y') - 100;
$years = range( $start, $end );

echo '<select name="year">';
foreach( $years as $v ) {
     echo "<option value=\"$v\">$v</option>\n";
}
echo '</select>';

[micah1701]

.

[adamlacombe]

Thanks for the quick reply guys.

I am going to try to use that code but here is my whole plan and issues im having.

 

Users table has month, day, and year. I want to make a search that will search AGE to AGE. Is it possible to go about doing that with how I have the table set up?

[Pikachu2000]

Are they stored in an appropriate field, such as DATE data type, in YYYY-MM-DD format?

[adamlacombe]

no... I have three fields month,day, and the year all varchar(255)

[Pikachu2000]

And I suppose it's too late now to add a DATE field and UPDATE the table to populate that field using the data in the other 3, right?

[adamlacombe]

No actually its not too late, I only have my account and a friends. So explain again what I am going to have to do please thanks

just move everything in month day and year into one field?

[Pikachu2000]

This query will set the value of the newly added date field based on the value of the existing day, month, year fields. You just need to edit it to reflect your actual table and field names. Then I have to remember the query to calculate age from DOB . . . I'll work on that while you do this

 

UPDATE `table` SET `new_date_field` = (SELECT CAST(CONCAT_WS('-', `old_year_field`, `old_month_field`, `old_day_field`) AS DATE))

[adamlacombe]

Okay now my new field "bdate" contains this: 1994-02-22

is that correct?

 

I have this for a age calc:

function DetermineAgeFromDOB ($month,$day,$year)
{

//Get age 
$birthday = mktime(0, 0, 0, $month, $day, $year); 
$today = mktime(0, 0, 0, date('n'), date('j'), date('Y')); 
$age = floor(($today - $birthday) / (86400*365)); 

  return $age; 
} 

[Pikachu2000]

Yes, that's the correct format. You can get MySQL to calculate the age directly in the query string, and it will take into account leap years, using this in the query. This query uses the alias "AS age", so the result will be available in the fetched array as (for example) $array_name['age']. You just need to change the name of `table`.

 

SELECT ( (DATE_FORMAT(CURDATE(),'%Y') - DATE_FORMAT(`bdate`, '%Y') ) - ( DATE_FORMAT(CURDATE(), '00-%m-%d') < DATE_FORMAT( `bdate`, '00-%m-%d')) ) AS age FROM `table`

 

I know it looks pretty confusing, but it isn't as bad as it appears. Let me know if you have any problems with it, or need help getting it to work with an existing query.

[adamlacombe]

hmm yes i am confused lol

so I got down the drop down lists and that is in a form, submits it to search.php looks like this:

$age_from = clean_up($_POST['age_from']);
$age_to = clean_up($_POST['age_to']);

$r3 = mysql_query("SELECT * FROM users WHERE `year` BETWEEN " . $age_from . "AND " . $age_to."");

 

thats what it looks like now.. so how do I fix it to this one?

 

I'm pretty damn smart when it comes to php and mysql but damn this is all new to me lol so thanks so very much for the help

[Pikachu2000]

This is going to look even more confusing, so I'll separate it into chunks to help it make some sense.

 

It's easier to grasp when you consider that what you're really doing is selecting the result of the calculation of the bdate field, and also using just the result of that calculation in the WHERE clause.

 

SELECT 

`field_1`, 
`field_2`, 
`field_3`, 
( (DATE_FORMAT(CURDATE(),'%Y') - DATE_FORMAT(`bdate`, '%Y') ) - ( DATE_FORMAT(CURDATE(), '00-%m-%d') < DATE_FORMAT( `bdate`, '00-%m-%d')) ) AS age 

FROM `users` 

WHERE 

( (DATE_FORMAT(CURDATE(),'%Y') - DATE_FORMAT(`bdate`, '%Y') ) - ( DATE_FORMAT(CURDATE(), '00-%m-%d') < DATE_FORMAT( `bdate`, '00-%m-%d')) ) 

BETWEEN $age_from AND $age_to

[adamlacombe]

hmmm its spitting out an error

 

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 'AND' at line 10

[Pikachu2000]

Check to make sure your $age_from and $age_to variables are populated. Might be a good idea to not even let the query run if they aren't . . .

[adamlacombe]

this is what my search.php file looks like:

<?php
$title="Search";
$metakeywords="search, find";
$metadescription="Search for your love!";
include ('header.php');

$keyword = clean_up($_POST['keyword']);
$gender = clean_up($_POST['gender']);
$age_from = clean_up($_POST['age_from']);
$age_to = clean_up($_POST['age_to']);

$r3 = mysql_query("SELECT `id`, `first`, `last`, `avatar`, 
( (DATE_FORMAT(CURDATE(),'%Y') - DATE_FORMAT(`bdate`, '%Y') ) - ( DATE_FORMAT(CURDATE(), '00-%m-%d') < DATE_FORMAT( `bdate`, '00-%m-%d')) ) AS age 

FROM `users` 

WHERE 

( (DATE_FORMAT(CURDATE(),'%Y') - DATE_FORMAT(`bdate`, '%Y') ) - ( DATE_FORMAT(CURDATE(), '00-%m-%d') < DATE_FORMAT( `bdate`, '00-%m-%d')) ) 

BETWEEN $age_from AND $age_to") or die(mysql_error()); 

echo "$age_from $age_to";
echo "<table width='100%' align='center'>
<div class='content'>
Search users results for: $keyword
<br />";

while($rr3=mysql_fetch_array($r3)){
$user=clean_up($rr3[id]);
$first=clean_up($rr3[first]);
$last=clean_up($rr3[last]);
$avatar=clean_up($rr3['avatar']);

echo "<a href='index.php?action=profile&id=$user'><img src='avatars/$avatar' width='41' height='41' /> $first $last</a><hr>";
}
echo "</div></table>";


echo "</div>";

include ('footer.php'); ?>

 

 

 

and my page with the search form:

            <form action="index.php?action=search" method="post">
              <div class="search_row">
                <div class="search_column_1">
                  <label>Desired Age</label>
                </div>
                <div class="search_column_2">
                  <select class="date" name="age_from">';

$start = date('Y') - 16;
$end = date('Y') - 100;
$years = range( $start, $end );

foreach( $years as $v ) {
$age = $v - date('Y');
     echo "<option value=\"$v\">$age</option>\n";
}
echo '
                  </select>

                  <select class="date" name="age_to">';

$start = date('Y') - 16;
$end = date('Y') - 100;
$years = range( $start, $end );

foreach( $years as $v ) {
$age = $v - date('Y');
     echo "<option value=\"$v\">$age</option>\n";
}
echo '
                  </select>

 

 

and I get no results

[Pikachu2000]

You get no results at all, or you aren't getting the ages listed?

[adamlacombe]

no results at all. I echoed $age_from $age_to and they do contain years but I get no results back from the table users

[Pikachu2000]

Can you post your table structure? I'll recreate it locally and see what's going on there.

[adamlacombe]

I attached a zip of the files that are being used so just drop the files and the sql is in a file there too

 

[attachment deleted by admin]

[Pikachu2000]

I think I just figured it out without all that. Since we're comparing ages, not years, we need to change the <select> fields so the values they pass are ages. Just need to change the range() assignments to spit out the ages instead of the years. See if that fixes it.

[code=php:0]
$years = range( 16, 100 );  // <--- date() calculation no longer needed . . . 

foreach( $years as $v ) {
$age = $v - date('Y');
     echo "<option value=\"$v\">$age</option>\n";
}
echo '
                  </select>

                  <select class="date" name="age_to">';

$years = range( 16, 100 ); // <--- HERE also . . .

foreach( $years as $v ) {
$age = $v - date('Y');
     echo "<option value=\"$v\">$age</option>\n";
}
echo '</select> . . . 

[adamlacombe]

okay sweet! thanks!!

now if I wanna add in something like a gender... where do I put the AND `gender`='$gender'? because wont it mess up if I put it after the BETWEEN blank AND blank?

[Pikachu2000]

You can put it after the BETWEEN clause without affecting it.

[adamlacombe]

oh okay thanks.

I'm gunna bug you again, sorry. I actually spent two whole days trying to figure this all out lol so really thanks for helping me.

 

Now im not so advanced to know how to joi tables together.. tell me if thats what I would need to do..

I have another table called "questions" in that table there is a field called "smoke" how would I add that to the query if its in another table?

[Pikachu2000]

It depends. Are you wanting to show the user's response to the question regardless of what it is, or do you want to use the response as a condition in the WHERE clause?