Using a user variable within WHERE clause

Databases
1

Hello all,

I’ll building a database for a school, here’s my query:

SELECT *, DATE_ADD(students.dob, INTERVAL -students.year_advancement YEAR) AS dobaltered FROM students
	WHERE school = '$school' AND @dobaltered > $marker
	ORDER BY dobaltered DESC, surname ASC
	LIMIT $pgOffset, $numPerPage

The DATE_ADD functions accounts for students who have been put forward in the school year. The value of year_advancement is therefore between 0-3.

Each student has his/her date of birth entered (dob). The database is for students within year groups 7-11. All students entered into the database are year 7 at a minimum. However, as soon as they reach year 12 their details are to be kept but not displayed. I therefore want to dis-include students who are too old.

From what I’ve read, it seems my problem is that my variable ‘dobaltered’ is created during the SELECT clause, however this is executed after the WHERE part of the query?

‘$marker’ is a PHP variable - it’s the cut-off point date.

Any help would be greatly appreciated.

Thanks

2 
SELECT *
  FROM ( SELECT *
              , dob - INTERVAL year_advancement YEAR
                   AS dobaltered 
           FROM students
          WHERE school = '$school' ) AS foo
 WHERE dobaltered > $marker
ORDER 
    BY dobaltered DESC, surname ASC
 LIMIT $pgOffset, $numPerPage
3 
$marker = date("Y");
$marker = $marker - 16;
$marker .= "-08-31";

if (!$result = @mysql_query("
	SELECT *
  FROM ( SELECT *
              , dob - INTERVAL year_advancement YEAR
                   AS dobaltered 
           FROM students
          WHERE school = '$school' ) AS foo
 WHERE dobaltered > $marker
ORDER 
    BY dobaltered DESC, surname ASC
 LIMIT $pgOffset, $numPerPage")) {
	die('<p>Student query failed. '.mysql_error().'</p>.'); 
}

Here’s my complete code now. No joy, I get no results. To debug, I tried changing greater than to less than ($marker) and it still doesn’t work?

Many thanks for your help

4 
WHERE dobaltered > [COLOR="Red"][B]'[/B][/COLOR]$marker[COLOR="Red"][B]'[/B][/COLOR]

:slight_smile:

5

Many, many, many thanks!

6

And they all went home for tea.