Page 1 of 1

PHP Date range query

Posted: Thu Dec 03, 2009 12:25 pm
by sirstrumalot
So I have an existing database system that is using the date for customer records in a TEXT format. The format being used is:

MM/DD/YYYY

What I am wanting to do is finish my reporting system but need to query records in a MySQL query between a date range.

What is the most effective way to do this without changing the entire database and the thousands of records?

Rephrase, I basically want a query to select X number of records if their date is between start date MM/DD/YYYY and end date MM/DD/YYYY.

Thoughts?

Thanks in advance.

Re: PHP Date range query

Posted: Thu Dec 03, 2009 2:30 pm
by AbraCadaver
Assuming you have $start_date and $end_date in the YYYY-MM-DD format (easy to do with date()) then something like this may work (not tested):

Code: Select all

SELECT * FROM table_name WHERE
   CAST(CONCAT_WS('-', RIGHT(date_field, 4), LEFT(date_field, 2), MID(date_field, 4, 2))) AS DATE
      BETWEEN $start_date AND $end_date
If that doesn't work, then in PHP you're going to have to pull all records and when you loop through them do a strtotime() on the date_field and compare (assuming $start_date and $end_date are unix timestamps). Something like:

Code: Select all

while($row = mysql_fetch_array($result)) {
   $date = strtotime($row['date_field']);
 
   if($date >= $start_date && $date <= $end_date) {
      $in_range[] = $row;
   }
}
It really shouldn't be that hard to modify the DB though. You just add a new field of type 'DATE', with PHP query all rows, loop through and strtotime() and date() format the old date and insert into the new field. Then you can begin using the new field, or delete the old field and rename the new field to the old one.

HTH
-Shawn