RE: FN-FORUM: SQL help

date posted 12th January 2007 13:10

Make sure your date formats are correct, mySQL is very picky (yyyy-mm-dd
hh:mm:ss)

Also not a good thing to use request() statements directly in an SQL
statement, can be used for SQL injection hacks... Process / Validate them
first.


Regards

Sam Morgan

WiredEyes
tel (Uk): +44 (0)20 7100 2178
tel (Italy): +39 070 766 242
web : www.wiredeyes.com
----
umop ap!sdn w,! aw dlaH


> -----Original Message-----
> From: [EMAIL REMOVED] [EMAIL REMOVED] On Behalf Of Paul
> Cooper
> Sent: 12 January 2007 14:53
> To: FN-FORUM / [EMAIL REMOVED]
> Subject: Re: FN-FORUM: SQL help
>
>
> Thanks. Both these work as expected (but without the date condition):
>
> strSQL1 = "SELECT l.LicenseeID,l.Company,count(*) AS NumScores FROM
> tblScores AS s INNER JOIN tblGroups AS g ON s.GroupID = g.GroupID
> INNER JOIN tblLicensees AS l ON g.LicenseeID = l.LicenseeID GROUP BY
> l.LicenseeID,l.Company ORDER BY l.Company ASC;"
>
> strSQL1 = "SELECT Company, COUNT(ScoreID) AS NumScores FROM tblScores,
> tblGroups, tblLicensees WHERE tblScores.GroupID = tblGroups.GroupID
> AND tblGroups.LicenseeID = tblLicensees.LicenseeID GROUP BY Company
> ORDER BY Company;"
>
> However when I add the date condition the SQL runs fine but does not
> return any records. This is my statement:
>
> strSQL1 = "SELECT l.LicenseeID,l.Company,count(*) AS NumScores FROM
> tblScores AS s INNER JOIN tblGroups AS g ON s.GroupID = g.GroupID
> INNER JOIN tblLicensees AS l ON g.LicenseeID = l.LicenseeID WHERE
> s.GeneratedDate > '" & Request("startdate") & "' AND s.GeneratedDate <
> '" & Request("enddate") & "' GROUP BY l.LicenseeID,l.Company ORDER BY
> l.Company ASC;"
>
> I've tried hard coding some dates with no joy. It took me a while to
> discover mySQL uses single quotes not hashes so I'm hoping there's
> something obviously wrong with the above statement. I've also tried
> using BETWEEN . AND instead of greater/less as posted yesterday.
> There are definitely records there. GeneratedDate is a DATETIME data
> type. Is there a property in mySQL which could resolve this?
>
> TIA
> Paul
>
> --
> Freelancers, contractors earn more with Prosperity4
> Call 0870 870 4414 or visit www.prosperity4.com
> and benefit from Inland Revenue approved expenses today.
>
> To advertise here: http://www.freelancers.net/advertising.html
>
>