PHP/MySQL Sort By Date

Question

I'm trying to pull records out of my database based on the oldest date.

The MySQl table looks like this:

status, date, last, url

1, 2010-12-30 17:59:54, 2011-01-03 06:26:04, site1.com

1, 2010-12-28 12:16:10, 2011-01-03, 06:25:24, site2.com

The date and last rows are datetime.

Here are two types of queries I tried:

mysql_query("SELECT * FROM links WHERE status=1 ORDER BY last DESC LIMIT 0,25");
mysql_query("SELECT * FROM links WHERE status=1 ORDER BY DATE(last) DESC LIMIT 0,25");

The query works for the most part but always leaves some of the oldest records out...

Any ideas?

Thank you

Well, you're limiting at 25 results so you'll only see the last 25. The first query is enough, no need to use DATE on last. — nico
– nico, Commented Jan 3, 2011 at 13:01
The table has 100's of rows. I only wanted the 25 with the oldest dates. — R3dBu77
– R3dBu77, Commented Jan 3, 2011 at 13:03

Norm · Accepted Answer · 2011-01-03 13:09:23Z

1

I'm going to assume "last" means last clicked or updated right? I also think you want ascending order (12/20, 12/21) because you want the oldest dates first. "ORDER by last LIMIT 25" should be fine as ASC is implicit.

answered Jan 3, 2011 at 13:09

Norm

5835 silver badges15 bronze badges

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

1 Comment

R3dBu77 Over a year ago

Yes, that is 100% what I want. I'll give it at try. :)

CarpeNoctumDC · Accepted Answer · 2011-01-03 13:15:20Z

1

What are date and last's data types? timestamp or ?

try this:

mysql_query("SELECT * FROM links WHERE status=1 ORDER BY UNIX_TIMESTAMP(last) DESC LIMIT 0,25");

answered Jan 3, 2011 at 13:15

CarpeNoctumDC

1,7501 gold badge12 silver badges16 bronze badges

1 Comment

R3dBu77 Over a year ago

Date and Last are both DATETIME.

Collectives™ on Stack Overflow

PHP/MySQL Sort By Date

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related