MySQLi: SELECT priority using OR

Question

I have a photos table in the database that uses a status column to detect if the photo was validated or not...

so if a photo has a status = 0 then its not validated, but if status = 1 then the photo was validated.

I want to add status = 2 for selecting the photo as the 'main photo' so I can use

SELECT photo WHERE status = 2

but if there is no 'main photo' selected I want to select any other photo, but give priority to a photo with a status = 2 if available.. so I want to use something like this:

SELECT photo WHERE status = 2 OR status != 2

.. but giving priority to the photo with status 2... so if there is a photo with status 2 it will select that photo, but if there is't then can select any photo...

Its possible to do this?

Answer 1

Try this:

SELECT photo, status = 2 AS priority
FROM table
ORDER BY priority DESC

Here the expression status = 2 will be evaluated to either 1 (status = 2 is true) or 0 (status = 2 is false).

Answer 2

If status=2 is always the highest priority:

SELECT photo WHERE status = 2 OR status != 2 FROM your_table_name ORDER BY status

Or better:

 SELECT photo FROM your_table_name ORDER BY status

because status = 2 OR status != 2 is everything

Answer 3

if status=2 is NOT always the highest priority, you still can get first rows with status=2 and then all other:

SELECT photo FROM table ORDER By status=2 desc

Answer 4

I'd suggest you add another column to store this fact. If you don't, sooner or later (most likely sooner) another status value will appear and then confusion will result, particularly if the status column can then have multiple meanings (3 = validated and main photo, for instance).