MySQL query

[ddmobley]

I have a query:

$query = "SELECT id, catid, title, description, date, updated, hits FROM #__weblinks "
. "WHERE published='1' AND checked_out='0' "
. "ORDER BY date DESC LIMIT 5";
In this record, there is a record creation date ("date") and a record update date ("updated"). At record creation, the current date and time is entered in the format "YYYY-MM-DD HH:MM:SS" in the "date" field, and the "updated" field is filled with "0000-00-00 00:00:00". When the record is updated, the "date" field remains the same, but the "updated" field is updated with the current date and time.

In my query, it will fetch the 5 most recently added records. I want the query to check if "updated" has a value other than "0000-00-00 00:00:00" and if so use that field to ORDER BY, otherwise use the "date" field to ORDER BY, again, limiting the results to the 5 most recently added OR updated records.

I originally tried using a UNION, but this would return duplicate records if a record was both new and updated within the timeframe that separated the top five results. Obviously I need to use an IF or CASE statement in the ORDER BY, but I could get anything but errors when trying. Any ideas?

[ddmobley]

Let me answer my own question. My new query looks like this:

$query = "SELECT id, catid, title, description, CASE WHEN updated = '0000-00-00 00:00:00' "
. "THEN date ELSE updated END AS dated, hits FROM `#__weblinks` "
. "WHERE published='1' AND AND checked_out='0' "
. "ORDER BY dated DESC LIMIT 5";
THis works fine now. Sorry for the post, but I learned in the process.

[guelphdad]

even better. don't use fake values. Use NULL in place of the 000000 etc.

then do:


ORDER BY
COALESCE(updated,date)


if updated is null it gets skipped and the date value gets used.

[ddmobley]

even better. don't use fake values.Good point, but I was working within the constraints of an existing CMS software package, so the need for the CASE solution came into play. Thanks anyway!