Select records in order

Asked

Viewed 44 times

2

I’m making a virtual store from scratch and the customer asked me to make the following logic:

  • Select first available products with photo
  • Then select the available ones without photo
  • Then select unavailable with photo
  • then selects the unavailable without photo

The problem is that I have no idea how to do this, because that I know only to perform the selection with the Where status = '1' AND photo != '', how can I follow the order above ?

php mysql

  • Tried to list all products and sort according to the criteria?

  • No, but I thought about the possibility of using a foreach() to help me. I just wanted to see if you can do it directly in the SQL command

  • Puts the table structure

  • status = 1 (available) / 0 (unavailable) photo = '' (no photo) / 'image.png' (with photo)

  • Order by status desc , case when foto is null then 0 Else 1 end)

1 answer

1

It is possible to do using only the ORDER BY mysql

SELECT * FROM `tb_produto`
ORDER BY `status` DESC, LENGTH(`foto`) > 0 DESC;

When using the function LENGTH() the values '' (empty) and NULL for photo will be displayed last.

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.