SQL help

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

SQL help

Post 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
User avatar
Skittlewidth
Forum Contributor
Posts: 389
Joined: Wed Nov 06, 2002 9:18 am
Location: Kent, UK

Post by Skittlewidth »

$years isn't a column in the users table.
Last edited by Skittlewidth on Wed Aug 03, 2005 5:03 am, edited 1 time in total.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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?
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

its not possible to do it with 3 separate columns using strtotime?
User avatar
Skittlewidth
Forum Contributor
Posts: 389
Joined: Wed Nov 06, 2002 9:18 am
Location: Kent, UK

Post 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.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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) .. ;)
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

My apologies, you're right.
Post Reply