PHP+mySQL - Struggling with BETWEEN statement with year and date variables... Help!

Hey guys,
I'm running a mySQL query but I've looked everywhere and can't find a way of doing it..
Here's my form:http://www.thestreetlyschool.co.uk/temp/PHP2.jpg
Here's the table structure:http://www.thestreetlyschool.co.uk/temp/PHP1.jpg
And here's how the results will be displayed:http://www.thestreetlyschool.co.uk/temp/PHP3.jpg
I've tried many ways to try and list all the entries from a daterange.
E.g. From January 2008 to December 2008 etc etc..
This is what I've got so far:
PHP Code:



$queryÂÂ*= "SELECT * FROM depu_usage WHERE YEAR(datetime) BETWEEN `$year-$month` AND `$yeara-$montha` AND department_id = `$department_id` ORDER BY datetime";




I know it's not great! But I've tried all the normal ways I can think of.
I keep getting errors like "Unknown column '2008-09' in 'where clause'" and syntax errors.
Any help would be appreciated!!
Cheers,
crE





__________________<<< Please see Forum Guidelines for signature setup. >>>

Perhaps this would work for you:
Code:
WHERE `datetime` BETWEEN '$year-$month-01' AND LAST_DAY('$yeara-$montha-01')

http://dev.mysql.com/doc/refman/5.0/...functions.html
Watch out for the single quotes and backticks. (I'd also suggest you rename the datetime field to something more descriptive and less of a data-type )
Edit: Although LAST_DAY probably won't work here since the time defaults to midnight - you'd lose entries on the last day of the month. So perhaps DATE_ADD('$yeara-$montha-01', INTERVAL 1 MONTH) would work better...





__________________
Chris <ClonePanel>
"Not everything that can be counted counts, and not everything that counts can be counted" - Albert Einstein






Last edited by foobic : 12-05-2008 at 08:00 AM.

Jeez, thats worked straight away!!
Thank you so much!!





__________________<<< Please see Forum Guidelines for signature setup. >>>

I would also suggest to bind parameters you pass to your SQL.





__________________All My Data From small shared web hosting accounts to powerful dedicated servers.
Now offering Affordable UNIX shells and IRCd hosting!