How to display records in a specified date range (pulls records for all years)

[kds2]

I am having trouble showing reports for a given date range. Currently if I specify something like 11/03/2010 to 11/05/2010 I get results for all years within that month and day such as I may get results for 11/03/2008 11/03/2009 11/03/2010 11/04/2008 11/04/2009 11/04/2010 11/05/2008 11/05/2009 11/05/2010.

I am using the following code

$result = mysql_query("SELECT * FROM report WHERE date>='$date_begin' and date<='$date_end' ORDER BY 'date'",$db);

 

I use the following format in my date feild mm/dd/yyyy

[PFMaBiSmAd]

You can only do greater-than/less-than date comparisons when the dates are formatted, left-to-right, most significant field-to-least significant field or have an integer representation where the magnitude of the integer allows greater-than/less-than comparisons. This is one of the reasons why database DATE data types are formatted YYYY-MM-DD.

 

Your first step will be to use a DATE data type.

[kds2]

Gotcha. So basically I am going to have to write a script to fix all my dates in the date fields and then change my data type, and then edit all my code. Yikes, I’ll have to do that over winter break.

Is their anything I can do as a temporarily solution untill then?

 

[PFMaBiSmAd]

fix all my dates in the date fields and then change my data type

 

^^^ Backup your database, then simply add a new column of type DATE. Perform one UPDATE query using the mysql  STR_TO_DATE() function in it (a query without a WHERE clause will update all the rows at once) to populate the new DATE column from your existing data. Remove the old 11/04/2009 format column. You can do this using your favorite database management tool, no php code is necessary.

 

then edit all my code

 

^^^ You can convert your 11/04/2009 format date into a DATE type when you insert/update it using the STR_TO_DATE() function directly in your queries. You can retrieve a DATE type in any format you want by using the DATE_FORMAT() function directly in your queries.

 

Other than editing your queries, you don't need to change anything.

[kds2]

Ok I backup my database and I made a new field called datum and set the type to date.

So now I have a field labeled date with all my dates as mm/dd/yyyy now I am a little lost on getting them populated to the datum field using the proper format of yyyy/mm/dd.

I am using phpmyadmin. Sorry, I am still a amateur at php and myadmin. Someone said I sould not use a field labeled date as it Is a reserved keyword and will cause problems. I assume this is true.

 

[PFMaBiSmAd]

You can use date as a column name and in fact that is what your existing column is named.

 

To populate your new datum column with DATE values from your existing column, execute the following UPDATE query -

 

UPDATE report SET datum = STR_TO_DATE(date,'%m/%d/%Y');