Page 1 of 1

SQL help

Posted: Wed Aug 03, 2005 4:45 am
by s.dot
I want to pick out all of my usernames from table 'users' where their age is < 18.

I have their age stored as their birthdate in 3 separate fields -> agemonth, ageday, age year

I have attempted the following queries to do this, but it seems to pick out all of the users, ignoring my WHERE $years < 18 clause

Code: Select all

$query = mysql_query("SELECT agemonth, ageday, ageyear FROM users WHERE agemonth != ''"); // initial query to turn birthdate into age in years
while($array = mysql_fetch_assoc($query))
{
	$month = $array['agemonth'];                          // this should
	$day = $array['ageday'];                                 // turn their
	$year = $array['ageyear'];                              // birthdate
	$seconds = strtotime("$month $day, $year");   // into a
	$time = time();                                              // variable
	$diff = $time - $seconds;                                // named $years
	$years = substr($diff/31556926,0,2);              // for 2nd query

	$query2 = mysql_query("SELECT username FROM users WHERE $years < '18'");  // this should pick out users under 18, correct?
                while($array2 = mysql_fetch_assoc($query2))
                {
                      echo $array2['username'];
                      echo "<BR>";
                 }
}
The $years is correct.. I print it and I get the correct age for each person. I just can't seem to get it to query for people unde 18

Posted: Wed Aug 03, 2005 4:51 am
by Skittlewidth
$years isn't a column in the users table.

Posted: Wed Aug 03, 2005 4:58 am
by s.dot
hahhahaha durrr... I can't believe I didn't recognize that... or why I would even attempt that.

Anywho... how would I go about picking out users < 18 based on my first query?

Posted: Wed Aug 03, 2005 4:59 am
by onion2k
Store the date of birth as a date field rather than 3 seperate columns. Then, to get all the user's whose age is under 18:

Code: Select all

select * from users where dateofbirth > DATE_SUB(NOW(), INTERVAL 18 YEAR);
Easy.

Posted: Wed Aug 03, 2005 5:13 am
by s.dot
its not possible to do it with 3 separate columns using strtotime?

Posted: Wed Aug 03, 2005 5:35 am
by Skittlewidth
Its easier to store the date as a whole and then use Date_format in your sql query to get the individual parts of the date if you need to. I'd take Onion2k's advice and your queries will be much simpler and take less coding.

Posted: Wed Aug 03, 2005 5:43 am
by onion2k
scrotaye wrote:its not possible to do it with 3 separate columns using strtotime?
Sure it's possible.

It's also a sign you're completely mad.

Posted: Wed Aug 03, 2005 9:18 am
by timvw
Imho, bday, bmonth and byear can't be separated because they adhere to each other.

Code: Select all

SELECT *
FROM table
WHERE YEAR(birthdate) - YEAR(NOW()) < 18

Posted: Wed Aug 03, 2005 11:31 am
by onion2k
timvw wrote:Imho, bday, bmonth and byear can't be separated because they adhere to each other.

Code: Select all

SELECT *
FROM table
WHERE YEAR(birthdate) - YEAR(NOW()) < 18
I considered that method before I wrote my post up there .. but .. if a user birthdate is 1/1/1987, and another user birthdate is 1/12/1987 .. that code is going to report them as both over 18.. when the reality is that one of them isn't. You really have to take the entire date into consideration, not just the year.

And you meant YEAR(NOW()) - YEAR(birthdate) .. ;)

Posted: Wed Aug 03, 2005 11:34 am
by timvw
My apologies, you're right.