PHP and MYSQL query to display records between 2 dates

my database stores the date for a record as type date (0000-00-00) with the name 'dateOfSale'

Now I have a form that allows a user to select a start date and and end date using a javascript calendar. The dates returned from this are in the format 03 Jun 2008

So I have the following variables for example

$fromDate = 03 Jun 2008
$toDate = 05 Jun 2008

And here is the query I am trying to get to work

$query="select * FROM sales WHERE (dateOfSale BETWEEN STR_TO_DATE($fromDate, '%d %m %Y') AND STR_TO_DATE($toDate, '%d %m %Y'))";

Code (markup):

Thanks!

 

Why using those dates, try to convert them to unix time and then query your database with something like this

SELECT * FROM table WHERE unix_timestamp(datefield) >= yourdate AND unix_timestamp(datefield) <= yourdate

 

Or you can use simple string comparison, as the dates are in YYYY-MM-DD format, so a simple query like this :

SELECT * FROM sales WHERE dateOfSale>='2008-03-01' AND dateOfSale<='2008-03-30'

should work, and give you all the sales between March 1st and March 30th

 

Thanks for the replies but unfortunately still can't get it to work.

Eric: I have tried the following with no joy.

$query="select * FROM sales WHERE dateOfSale >= unix_timestamp($fromDate) AND dateOfSale <= unix_timestamp($toDate) ";

Freud: Only the date in the database is stored as YYYY-MM-DD format. The dates for the range are in the format 05 Jun 2008

So here are some example values for the 3 date variables

dateOfSale: 2008-06-04

fromDate: 05 Jun 2008
toDate: 12 Jun 2008

I could really do with some help solving this as I have tried so many variations of syntax and ddate formats

 

Well then you'll have to make a conversion of the dates in format 05 Jun 2008 (not really hard) to YYYY-MM-DD format, and then it'll work