Problem Using BETWEEN with dates from mysql table

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
chopper_pc
Forum Newbie
Posts: 15
Joined: Fri May 30, 2008 10:55 pm

Problem Using BETWEEN with dates from mysql table

Post 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?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Problem Using BETWEEN with dates from mysql table

Post 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.
User avatar
chopper_pc
Forum Newbie
Posts: 15
Joined: Fri May 30, 2008 10:55 pm

Re: Problem Using BETWEEN with dates from mysql table

Post 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..... :|
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Problem Using BETWEEN with dates from mysql table

Post 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).
User avatar
chopper_pc
Forum Newbie
Posts: 15
Joined: Fri May 30, 2008 10:55 pm

Re: Problem Using BETWEEN with dates from mysql table

Post 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!
Post Reply