How do I filter ranked results from mysql table?

I have the following code working perfectly well to rank my students in different classes by highest mark, but when I want to filter by year it’s returning Column ‘year’ in where clause is ambiguous. Help!!!


$currentPage = $_SERVER["PHP_SELF"];

$colname_Recordset2 = "-1";
if (isset($_GET['recordID'])) {
  $colname_Recordset2 = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']);
mysql_select_db($database_arundel, $arundel);
$query_Recordset2 = sprintf("SELECT v1.s_surname, v1.mark, v1.s_name, v1.year, v1.class, COUNT(v2.mark) AS Rank   
FROM biology v1 
JOIN biology v2 ON v1.mark < v2.mark OR (v1.mark=v2.mark and v1.s_surname = v2.s_surname)
WHERE `year` = '%s'
GROUP BY v1.s_surname, v1.mark 
ORDER BY  v1.mark DESC", $colname_Recordset2);
$Recordset2 = mysql_query($query_Recordset2, $arundel) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);



you’ve got two biology tables in the query

what you want is WHERE v1.year = ‘%s’ or WHERE v2.year = ‘%s’

hi there, thanks fro the response. If I type either v1.year = ‘%s’ or v2.year = ‘%s’ its returning undefined variable, I wonder why its doing that,

If YEAR exists in more than one table then you have to preface it with the table name or alias (i.e. v1.year) otherwise it won’t know which column you are referring to.

you should specify the table alias as well.
so instead of year
It is probably because v2 also has a ‘year’ column?

Can you post the output of a SHOW CREATE TABLE for the biology table?

When working with joins, unless your using an alias for a field you should always refer to a field using the syntax table_name.field_name

Oh thanks a lot! the v1.year worked!!!