date_sub()

[AV1611]

//edit figured it out, but will leave up if anyone cares:
// this was the problem line:
// AND DATE_SUB(NOW(),INTERVAL 30 DAY)<=surveyresults.DATE");
//forgot the surveyresults. part

neither of these will work...

[code]
$result1 = mysql_query("Select
count( distinct surveyresults.ID)
From
surveyresults
Inner Join survey ON surveyresults.ID = survey.ID
Where
survey.INACTIVE Not Like 'YES'
AND DATE_SUB(NOW(),INTERVAL 30 DAY)<=DATE");
if ($row = mysql_fetch_array($result1)){


$result2 = mysql_query("Select
surveyresults.TEAM_LEADER,
surveyresults.FNAME,
surveyresults.LNAME,
surveyresults.ID,
Avg(surveyresults.RATING) AS RATING
From
surveyresults
Inner Join survey ON surveyresults.ID = survey.ID
Where
survey.INACTIVE Not Like 'YES'
AND DATE_SUB(NOW(),INTERVAL 30 DAY)<=DATE
Group By
surveyresults.ID
ORDER BY
RATING desc");
[/code]

here is my table structure:

# Table "surveyresults" DDL

CREATE TABLE `surveyresults` (
`TEAM_LEADER` varchar(255) NOT NULL default '',
`FNAME` varchar(255) NOT NULL default '',
`LNAME` varchar(255) NOT NULL default '',
`ID` varchar(255) NOT NULL default '',
`RATING` varchar(255) NOT NULL default '',
`EVALUATOR` varchar(255) NOT NULL default '',
`DATE` date NOT NULL default '0000-00-00',
`IP` varchar(255) NOT NULL default '',
`HOSTNAME` varchar(255) NOT NULL default '',
`REMARKS` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 13312 kB; InnoDB free: 13312 kB; InnoDB free: 1'