All geek questions in one place

MySQL: if the field is empty, assign a value

Here is my request:

$sql = "SELECT u.PHONE , u.STATE , if (uo.CHANNEL='','web',channel) as TYPE FROM user LEFT JOIN user_oferts uo ON u.PHONE=uo.PHE_USER WHERE (u.STATE='active') ";

And it works great, I get what I want, but for an empty channel.

I need a channel to be “web” if it is not “wap”, so the channel will not be empty or null or nothing. How can i do this? What am I doing wrong?

thanks a lot

+4
source share
2 answers
 select ... case when channel = '' then web else channel end as TYPE....

using the CASE statement.

i.e. for your case

 $sql = "SELECT u.PHONE , u.STATE , case when coalesce(uo.CHANNEL, '') = '' then 'web' else channel end as TYPE FROM user LEFT JOIN user_oferts uo ON u.PHONE=uo.PHE_USER WHERE (u.STATE='active') ";

Edit: Added coalesce to deal with possible NULL values.

+5
source

Any reason this can't work?

 $sql = "SELECT u.PHONE , u.STATE , if (uo.CHANNEL='wap','wap','web') as TYPE FROM user LEFT JOIN user_oferts uo ON u.PHONE=uo.PHE_USER WHERE (u.STATE='active') ";

MySQL does not like to compare null with string , so you can either embed if uo.CHANNEL IS NULL in another test expression, or rather change your table so that it cannot be null and give it a default value of "wap".

+3
source

Source: https://habr.com/ru/post/1333029/

More articles:


All Articles