Filter by current year

brattt

I am trying to add the results from a 'points' column that only occurred this year. However, as this needs to be dynamic I need to have the year as a variable. At the moment I have this:

<?php
$var1_allMemberPointsRS = "0";
if (isset($_GET['year'])) {
  $var1_allMemberPointsRS = (get_magic_quotes_gpc()) ? $_GET['year'] : addslashes($_GET['year']);
}
mysql_select_db($database_luminary, $luminary);
$query_allMemberPointsRS = sprintf("SELECT t.memberID, t.firstName, t.lastName,  e.memberID, date_format(e.date, '%%D %%M, %%Y') as date,  date_format(e.date, '%%Y') as year, e.eventName, e.points, e.sw_points,  (select SUM(e.points) FROM tbl_events e WHERE t.memberID = e.memberID AND e.date LIKE '%%%s%%')  AS totalPoints,  (select SUM(e.sw_points) FROM tbl_events e WHERE t.memberID = e.memberID AND e.date LIKE '%%%s%%' )  AS totalswPoints  FROM tbl_events e  LEFT JOIN tbl_members t ON t.memberID = e.memberID ORDER BY t.lastName ASC,  e.date ASC", $var1_allMemberPointsRS,$var1_allMemberPointsRS);
$allMemberPointsRS = mysql_query($query_allMemberPointsRS, $luminary) or die(mysql_error());
$row_allMemberPointsRS = mysql_fetch_assoc($allMemberPointsRS);
$totalRows_allMemberPointsRS = mysql_num_rows($allMemberPointsRS);
?>

This is not working as it adds up all the points and not just the points from 2006.

Could anybody please tell me what I need to change so that it will only add up the points (and sw_points) for the current year.

bogu

U forgot your where condition ...

brattt

Thanks for your reply bogu. I have added the WHERE condition and still get the total for the points for all years. At the moment my statement looks like this:

<?php
$var1_allMemberPointsRS = "0";
if (isset($_GET['year'])) {
  $var1_allMemberPointsRS = (get_magic_quotes_gpc()) ? $_GET['year'] : addslashes($_GET['year']);
}
mysql_select_db($database_luminary, $luminary);
$query_allMemberPointsRS = sprintf("SELECT t.memberID, t.firstName,
t.lastName,  e.memberID, date_format(e.date, '%%D %%M, %%Y') as date,
date_format(e.date, '%%Y') as year, e.eventName, e.points, e.sw_points,
(select SUM(e.points) FROM tbl_events e WHERE t.memberID = e.memberID AND
e.date LIKE '%%%s%%')  AS totalPoints,  (select SUM(e.sw_points) FROM
tbl_events e WHERE t.memberID = e.memberID AND e.date LIKE '%%%s%%' )
AS totalswPoints  FROM tbl_events e  LEFT JOIN tbl_members t ON t.memberID =
e.memberID WHERE t.memberID = e.memberID AND e.date LIKE '%%%s%%'
ORDER BY t.lastName ASC,  e.date ASC", $var1_allMemberPointsRS,$var1_allMemberPointsRS,$var1_allMemberPointsRS);
$allMemberPointsRS = mysql_query($query_allMemberPointsRS, $luminary) or die(mysql_error());
$row_allMemberPointsRS = mysql_fetch_assoc($allMemberPointsRS);
$totalRows_allMemberPointsRS = mysql_num_rows($allMemberPointsRS);
?>

Any ideas?

bogu

I have no idea what select u wrote there, but the where condition should be like this:

WHERE DATE_FORMAT(e.date, '%Y') = '2006' and ...