How to select column conditionally according to another one in MySQL

Question

I have a table (users) which has columns as the following:

userid  name  email  system_photo  personal_photo  photo_select

photo_select can have two values (1 or 0). I want to select system_photo or personal_photo according to photo_select value. If photo_select is 1 then personal_photo should be selected, else if photo_select is 0 then system_photo should be selected.

system_photo value comes from another service. If the user does not like that photo, s/he can upload a new one. On another day, s/he will be able to switch between the two photos.

I have first selected all three columns, and in the view section of my code, I have written

if($user->photo_select === '0')
    echo $user->system_photo;
else if($user->photo_select === '1')
    echo $user->personal_photo;

but then I thought that this is a costly way. And I have decided to select only one photo column (system_photo or personal_photo) in the model section. What should be my MySQL SELECT statement to achieve this?

Answer 1

you can do the following:

select *, if(photo_select = 0, system_photo,personal_photo) as photo
from users

you can use if statements in your query based if its true or false you will select 1 column or the other.... then finally you cast it as whatever you want to call it maybe..... photo

Answer 2

SELECT IF(photo_select = 1, personal_photo, system_photo) as photo from users