CODEWITHSUNDEEP
mysql
shin
shin

Reputation: 32721

How to add if statement in SQL?

I have a following MySQL with two parameters, $catname and $limit=1. And it is working fine.

SELECT P.*, C.Name AS CatName 
                   FROM omc_product AS P
                   LEFT JOIN omc_category AS C
                   ON C.id = P.category_id
                   WHERE C.Name = '$catname'
                   AND p.status = 'active'
                   ORDER BY RAND()
                   LIMIT 0, $limit

Now I want to add another parameter $order. $order can be either

ODER BY RAND()

or

ORDER BY product_order

in the table omc_product.

Could anyone tell me how to write this query please?

Thanks in advance.

Upvotes: 1

Views: 446

Answers (1)

Oded
Oded

Reputation: 498972

Taken from the comments to this MySQL 5.0 reference article:

You can ORDER BY a dynamic column_name parameter using a CASE expression in the ORDER BY clause of the SELECT statement:

CREATE PROCEDURE `orderby`(IN _orderby VARCHAR(50))
BEGIN

  SELECT id, first_name, last_name, birthday
  FROM table
  ORDER BY

  -- numeric columns
  CASE _orderby WHEN 'id' THEN id END ASC,
  CASE _orderby WHEN 'desc_ id' THEN id END DESC,
  -- string columns
  CASE _orderby WHEN 'first_name' THEN first_name WHEN 'last_name' THEN last_name END ASC,
  CASE _orderby WHEN 'desc_first_name' THEN first_name WHEN 'desc_last_name' THEN last_name END DESC,
  -- datetime columns
  CASE _orderby WHEN 'birthday' THEN birthday END ASC,
  CASE _orderby WHEN 'desc_ birthday' THEN birthday END DESC;

END

Upvotes: 1

Related Questions