CODEWITHSUNDEEP
phpmysqldatetimestamp
Stephen S.
Stephen S.

Reputation: 835

MYSQL order by publish date and timestamp irregularities

I'm trying to list out news using php and mysql based on the publish date and the time-stamp of the news article. I need all articles to list with the newest publish date and most recent time-stamp. I'm getting some irregularities in the listings when i attempt to accomplish this.

To note, some news articles could have a timestamp of today but a publish date of 10 from now.

SELECT * FROM table_news WHERE pubdate <= ".time()." ORDER BY pubdate,date DESC

SELECT * FROM table_news WHERE pubdate <= ".time()." ORDER BY date,pubdate DESC

SELECT * FROM table_news WHERE pubdate <= ".time()." ORDER BY pubdate,date ASC

SELECT * FROM table_news WHERE pubdate <= ".time()." ORDER BY date,pubdate ASC

None of these work...

I can list some of the results but i thought it might be a bit redundant. Let me know if you'd like to see them.

I'm either missing something obvious (long hours this week...) or there is a more creative approach to this.

Thanks for the help!

Upvotes: 1

Views: 2458

Answers (4)

Stephan B
Stephan B

Reputation: 3701

I am guessing here, but I think I know what you expected ORDER BYto do.

When ordering by two columns, you will not get the most recent and newest, you will get all entries ordered by the first column in that ORDER BY clause, and only if two rows have the same value these two will be ordered by the second column.

You may try if this works as expected:

SELECT *
FROM table_news
WHERE pubdate <= ".time()."
ORDER BY MAX(pubdate,date) DESC

This will order by whatever date is more recent.

Upvotes: 1

Isotopp
Isotopp

Reputation: 3393

You need to use a MySQL formatted date in your query string. Try

SELECT NOW();

to see what a MySQL formatted date looks like. You may want to use the NOW() function directly in your query instead of using the PHP time() function.

SELECT * FROM table_news WHERE pubdate <= NOW() ORDER BY ...

The PHP function time() returns a Unix Timestamp (seconds since midnight 1st of January, 1970 UTC). The MySQL functions FROM_UNIXTIME() and UNIX_TIMESTAMP() can be used to convert from and to Unix Timestamps.

In order to be able to use an index, the name of the column you compare against must be used free standing and not be used in a function. That is,

SELECT * FROM table_news WHERE pubdate < FROM_UNIXTIME(1306421037)

can use an index on pubdate, but

SELECT * FROM table_news WHERE unix_timestamp(pubdate) < 1306421037

cannot use an index on pubdate, because the index is on pubdate and not on unix_timestamp(pubdate) (MySQL does not support functional indices).

Upvotes: 1

Marc B
Marc B

Reputation: 360782

Is your pubdate field a unix timestmap (e.g. an integer field), or an actual mysql date/time field? time() returns a unix timestamp, not a date/time string. Use this instead:

SELECT *
FROM table_news
WHERE pubdate <= now()
ORDER BY pubdate DESC, date DESC

Note the DESC on both fields in the order by clause. unless you specify asc/desc explicitly on each field in an order-by clause, SQL specifies defaulting to ASC.

Upvotes: 4

George Cummins
George Cummins

Reputation: 28936

SELECT * FROM table_news WHERE pubdate <= NOW() ORDER BY pubdate,date DESC

PHP's time() returns the current time as a Unix timestamp, which uses a format very different from the MySQL timestamp. You can use NOW() to get the current timestamp in MySQL.

Upvotes: 1

Related Questions