IF in ORDER BY?

[glenelkins]

Hi, Here is a mysql query:

 

"SELECT * FROM `private_messages` WHERE ( `sender_id` = '$user_id' AND ( `receiver_updated` = '1' || `sender_updated` = '1' ) AND `sender_deleted` = '0' ) || ( `receiver_id` = '$user_id' AND `receiver_deleted` = '0' ) AND `parent_id` = '0'"

 

I want to add an ORDER BY but It needs to evaluate different depending on the fields set. So for example, if the field `sender_updated` = '1' then it needs to order by `sender_updated_datetime` . If `receiver_updated` = '1' then i need to order by `receiver_updated_datetime`

 

Is there a way to put an IF statement on the ORDER BY clause?

 

[harkly]

You can do something like this

 

if (sender_updated == 1) {
  $order = "ORDER by sender_updated_datetime";
else if (receiver_updated == 1) {
  $order = "ORDER by receiver_updated_datetime";
}

"SELECT * FROM `private_messages` WHERE ( `sender_i ......... $order");

[DavidAM]

You can use an IF in a query. The syntax (for what you want) would be:

SELECT *
FROM private_messages
WHERE ... 
ORDER BY IF (sender_updated = '1', sender_updated_datetime, receiver_updated_datetime)

 

If it doesn't work in the ORDER BY, or if you want to be able to see the value used, you can add it to your SELECT clause, give it an alias, and ORDER BY the alias:

SELECT M.*, 
  IF (sender_updated = '1', sender_updated_datetime, receiver_updated_datetime) AS SortDate
FROM private_messages AS M
WHERE ... 
ORDER BY SortDate

(Note: mySql seems to require an alias on the "*" when adding other fields to the select list, so I put the "AS M" and "M." in there.)