How to sort by Date from MYSQL using PHP

Question

I have a php page which allows a user to sort pieces of information by several factors. A new requirement is to sort by "all items which have been registered in the last 15 days". I store my dates in the MYSQL table as mm/dd/yyyy.

The information is passed and picked up on the same page using the $_GET variable but I am unable for some reason to get the code to work. I have looked on numerous website but am unable to find a solution that works.

Ultimately, the script would work as follows:

select all persons who's KDATE is within 15 days of today's date (e.g., if today is 8/19/2010, everybody who registred from 8/04/2010 and on would appear).

My script so far (which does not work) is:

if (isset($_GET['date'])) {
     $query = "SELECT * 
                 FROM persons 
                WHERE DATE_SUB(CURDATE(),INTERVAL 15 DAY) <= KDATE 
             ORDER BY KDATE ASC";
}

Update 1:

KDATE IS TEXT - i apologize but the KDATE is stored as TEXT

Update 2:

The answer provided by Colin solved my issue. I will look into trying to convert the data into datetime format but am hoping the group can provide realistic benefits of doing so.

Thank you all again

Answer 1

Because kdate is VARCHAR, you need to use STR_TO_DATE to change it to a DATETIME.

You need to fix kdate data that does not fit that pattern (mm/dd/yyyy) before running this:

  SELECT * 
    FROM persons 
   WHERE DATE_SUB(CURDATE(),INTERVAL 15 DAY) <= STR_TO_DATE(KDATE, 'm/%d/%Y')
ORDER BY STR_TO_DATE(KDATE, 'm/%d/%Y') ASC

• This means that an index on kdate is useless, because of having to change the data type.

Once it's a DATETIME, you can use DATE_FORMAT to change the format as you like.

Answer 2

First of all, it's a really bad idea to use VARCHAR instead of DATE if you want a collumn with dates only.

If you want to use a string as a date, you'll need to convert it with STR_TO_DATE() and you might wan't to use those instructions to correctly format your date.

This should do it:

SELECT * 
FROM persons 
WHERE DATE_SUB(CURDATE(),INTERVAL 15 DAY) <= STR_TO_DATE(KDATE, "%c/%d/%Y")
ORDER BY STR_TO_DATE(KDATE, "%c/%d/%Y") ASC