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

Store your dates using the MySQL Date (or DateTime) type and you won't have these issues ;-)... — ircmaxell
– ircmaxell, Commented Aug 19, 2010 at 20:56
@JM4: There's a difference between overkill and just plain wasteful/inefficient. It would be like bringing a nuclear missile to a knife fight. Sure, it's more powerful, but it's also a lot harder to move around with (and hence slows you down a lot)... And it's completely wasting the resources at hand... — ircmaxell
– ircmaxell, Commented Aug 20, 2010 at 12:27
Oh and @OMG: The TEXT data type supports up to 65,536 characters (I say up to, since it depends on the characters being stored). And it's not just overkill, it's inefficient since the data isn't even stored with the row (so doing selects on it will thrash the drives with seeks since it can't just use the row buffer). Only use TEXT if you know you need more than 255 characters. Otherwise use either CHAR or VARCHAR (Which store their data in the row itself)... — ircmaxell
– ircmaxell, Commented Aug 20, 2010 at 12:46

Colin Hebert · Accepted Answer · 2010-08-19 21:11:18Z

3

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

edited Aug 19, 2010 at 21:11

answered Aug 19, 2010 at 21:06

Colin Hebert

93.5k15 gold badges164 silver badges154 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JM4 Over a year ago

this only orders the data, it does not return any results relatively to my main query

ircmaxell Over a year ago

Be advised that this style query will not be able to use indexes. Which means it will require a full table scan for every single query. If you have lots of rows, or lots of these queries it will bring your server to its knees VERY quickly... This will work to solve your problem, but the better solution would be to change KDATE to a DATE column data type...

OMG Ponies · Accepted Answer · 2010-08-19 21:02:41Z

2

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.

answered Aug 19, 2010 at 21:02

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

2 Comments

JM4 Over a year ago

so if I understand correctly - you are saying that my overall purpose is impossible as long as my dates are in VARCHAR format?

OMG Ponies Over a year ago

@JM4: Not impossible. Try the query, but it's very possible you will get an error because at least one kdate value doesn't match the date format. So you need to fix those before the query will work.

Collectives™ on Stack Overflow

How to sort by Date from MYSQL using PHP

Update 1:

Update 2:

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Update 1:

Update 2:

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related