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!!!
<?php
$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);
?>