PHP Date range query

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
sirstrumalot
Forum Commoner
Posts: 27
Joined: Mon May 18, 2009 10:26 pm

PHP Date range query

Post 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.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: PHP Date range query

Post 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
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Post Reply