Page 1 of 1
Problem Using BETWEEN with dates from mysql table
Posted: Fri Feb 13, 2009 9:05 pm
by chopper_pc
I have had a script for some time that puts a record in a DB that includes a date field created using date("m/d/y"); in a php script.
I am now building a script to build reports on users queries from a html form. They have the option to select a date range.
My code in the script to build the query is this:
Code: Select all
$sql = mysql_query("SELECT * FROM foo WHERE date BETWEEN '$startdate' AND '$enddate' ORDER BY date");
Works great other than it skips the records on "startdate"
This can't be to difficult, but I can't seem to find out where I've gone wrong.
Any suggestions?
Re: Problem Using BETWEEN with dates from mysql table
Posted: Fri Feb 13, 2009 9:11 pm
by John Cartwright
Between, by definition, returns dates
between the range.
If you want to include the boundaries as well, you will need to do
Code: Select all
WHERE date >= '$startdate' AND date <= '$enddate'
P.S., your dates are not stored in the correct format if you are doing natural ordering.
02/01/08 becomes 020108
01/01/09 becomes 010109
Clearly that latter should not be evaluated as a larger number. Instead, you should use mysql's date field, whereby the fields are stored as YYYY/MM/DD, which can be naturally sorted.
Re: Problem Using BETWEEN with dates from mysql table
Posted: Fri Feb 13, 2009 9:58 pm
by chopper_pc
Thanks for the input John,
Code: Select all
1. WHERE date >= '$startdate' AND date <= '$enddate'
Returns no rseult, no errors, yet no results.
I understand I should be using Mysql date field, my dilemma was that is not originally how I was sorting. So I wasn't worried and I wanted dates presented to my users in the 02/01/08 format.
Ohh the glory of learning.....

Re: Problem Using BETWEEN with dates from mysql table
Posted: Fri Feb 13, 2009 10:03 pm
by John Cartwright
I'm not exactly sure why you wouldn't get any results back (getting a bit late here, I'll check back tommorow if this hasn't been answered). However, the output you want to show the enduser has nothing to do with how you should store your values in the database. Both date and timestamp field types can easily be converted into any format (which would also make this trivial).
Re: Problem Using BETWEEN with dates from mysql table
Posted: Fri Feb 13, 2009 10:12 pm
by chopper_pc
John Cartwright wrote:However, the output you want to show the enduser has nothing to do with how you should store your values in the database. Both date and timestamp field types can easily be converted into any format (which would also make this trivial).
Thats really what I'm starting to realize. As i'm learning i'm starting to understand that data should be handled correctly in the first place. Three months after building a DB and php code I run into a roadblock because I took a shortcut. As for the conversions, I need to do some studying. I still haven't figured out how exactly they work but I have been searching. I figured they must be trivial once understood,
Thanks!