MySQL IF Statement Tally

Question

I have a table in MySQL that has the following fields:

• an id field
• a picture field that the filename or empty
• the same type of field for a telephone number.

I want to run an if statemtent that checks if the row has an image and telephone number

A yes to either would add one to the tally and then I need to order the results by that tally.

Is this possible?

I.e

ID   Pic   Phone
1           231
2    img    412 
3            

And then the order of display would be ID 2,1,3.

Answer 1

If your pic and phone columns are empty and are nulled you can do

select * from your_table
order by if(pic is null, 0, 1) + if(phone is null, 0, 1) desc

If your pic and phone columns are empty and contain empty strings you can do

select * from your_table
order by if(pic = '', 0, 1) + if(phone = '', 0, 1) desc

Answer 2

I'd go with the following:

SELECT * FROM table
ORDER BY (
  IF(pic IS NOT NULL AND pic != '', 1, 0) +
  IF(phone IS NOT NULL AND phone != '', 1, 0)
) DESC

This takes care of both the cases where pic or phone are NULL or empty strings.