Select Statement problem

Anon

Hello everyone,

I need to select rows from a MySQL table and return records using a BETWEEN statement. I am selecting user records in an age range. Each users age is stored in the table as a DATE vaule (their date of birth), hence dob column.

This is what I wrote:

SELECT city, userid,
IF(
(EXTRACT(MONTH FROM dob) <= EXTRACT(MONTH FROM NOW())),
IF((EXTRACT(DAY FROM dob) <= EXTRACT(DAY FROM NOW())),
(EXTRACT(YEAR FROM NOW()) - EXTRACT(YEAR FROM dob)),
(EXTRACT(YEAR FROM NOW()) - EXTRACT(YEAR FROM dob)-1)
),
(EXTRACT(YEAR FROM NOW()) - EXTRACT(YEAR FROM dob)-1)
) AS age
FROM users
WHERE age BETWEEN 18 AND 39;

Without the needed WHERE clause the query works fine. The IF statements are there to calculate the users age. When I add the WHERE clause, I get the error: Unknown column 'age' in 'where clause' which I understand as 'age' does not exist in the table, age is selected using 'AS' from the column dob. Am I going about this all wrong? Would I be better off selecting the records and then filter them with with some PHP code to display only the records matching the range requested?

Anon

Try replacing the 'Where' with 'HAVING', which filters the result set after they have been selected (as you say, the where statement will not work because you aren't aliasing 'age' to a column).

The only downside to this is that all the rows will be selected into memory before the HAVING clause is used, so that may slow down your application quite substantially.

[deleted]

You can't do a 'WHERE' on a virtual column, only on real columns.

But why the elaborate IF sequence?
If you want to fetch dates that are between X and Y years old you can just calculate the date of the lower and upper limits (dob of 18y/o and dob of 39y/o)

and then select records like:

SELECT *
FROM users
WHERE dob<upper_limit AND dob>lower_limit;

Anon

Thanks Daniel for your input. The first problem with my code: it was written when I should have been sleeping! Anyway, thanks for your response. I have resolved this issue after I realized an alias column will not work in an WHERE clause.

Kevin

Daniel Zambonini wrote:

Try replacing the 'Where' with 'HAVING', which filters the result set after they have been selected (as you say, the where statement will not work because you aren't aliasing 'age' to a column).

The only downside to this is that all the rows will be selected into memory before the HAVING clause is used, so that may slow down your application quite substantially.

Anon

Hi Vincent,

Thank you for your input.

You are correct, after looking at this with a fresh start it occured to me to do just as you are suggesting and it works fine!

The elaborate IF was to get the age of the user for the dob column. I have resolved to using my original PHP script(below) to do this. Is there an easier way to get age values?

Thanks,

Kevin

Function age($age)
{
/+--------------------------------------------+
| Calculate the age of the featured profile |
| takes the dob in the form m/d/yyyy from |
| MySQL table as DATE_FORMAT(dob, '%m/%d/%Y')|
| as arugument $age. |
| Returns $years (years old) |
+--------------------------------------------+/
$currDate = getdate();
list($myDob["mon"], $myDob["mday"], $myDob["year"]) = split("[/.-]", $age);
$years = ($currDate["year"] - $myDob["year"]);

if($myDob["mon"] > $currDate["mon"]){
	$years--;
} else {
	if($myDob["mday"] > $currDate["mday"]){
		$years--; //it's not your birthday yet, years = years - 1
	} elseif ($myDob["mday"] == $currDate["mday"]){
		$years .= " - Happy BirthDay!";
	}
}
Return($years);

}

vincent wrote:

You can't do a 'WHERE' on a virtual column, only on real columns.

But why the elaborate IF sequence?
If you want to fetch dates that are between X and Y years old you can just calculate the date of the lower and upper limits (dob of 18y/o and dob of 39y/o)

and then select records like:

SELECT *
FROM users
WHERE dob<upper_limit AND dob>lower_limit;