CODEWITHSUNDEEP
phpmysql
zkanoca
zkanoca

Reputation: 9918

How to select column conditionally according to another one in MySQL

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?

Upvotes: 4

Views: 2470

Answers (3)

Thilina Rubasingha
Thilina Rubasingha

Reputation: 1089

try this 

SELECT CASE
    WHEN photo_select=1 then personal_photo
    ELSE system_photo
END AS display_photo from users 
where userid=1

Upvotes: 1

Mariano Montañez Ureta
Mariano Montañez Ureta

Reputation: 1156

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

Upvotes: 5

lauw
lauw

Reputation: 545

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

Upvotes: 6

Related Questions