MySQL SELECT all columns but replace null or empty columns with certain default value

Question

Currently I have PHP code like this (CodeIgniter):

$detail = $this->db->query("SELECT * FROM tm_survei WHERE ID_SURVEI=5")->row_array();
foreach($detail as &$val){
    if (!!!$val) $val = '-'; //replace all the empty or null values with "-"
}

It solved the problem, but I wonder is it possible to do that in MySQL query itself without that PHP foreach e.g. SELECT IFNULL(*,'-') <original-column-name> FROM tm_survei?

Maybe this will help: stackoverflow.com/questions/3532776/… — Alex Slipknot
– Alex Slipknot, Commented Sep 7, 2017 at 9:33
You could try the if statement. More info you can find dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html — R Picheta
– R Picheta, Commented Sep 7, 2017 at 9:34
@AlexSlipknot I know about COALESCE() function, but i have no idea how to do it for all columns in one-go, so it's like SELECT COALESCE(firstname,'-') firstname,COALESCE(lastname,'-') lastname,COALESCE(gender,'-') gender FROM tm_survei (one by one). My table have like twenty columns, so it's a waste of code and also unreliable when later the table have new column added or removed. — Taufik Nur Rahmanda
– Taufik Nur Rahmanda, Commented Sep 7, 2017 at 9:41

Eugene Kaurov · Accepted Answer · 2017-09-07 09:46:44Z

1

IFNULL is really good enough like you suggested, just use SELECT with list of fields to show, not *. Usually, it's ok.

$detail = $this->db->query("SELECT id, title, IFNULL(column_name1, '-'), IFNULL(column_name2, '-') as aliasname FROM tm_survei WHERE ID_SURVEI=5")->row_array();
foreach($detail as &$val){
    $val; 
}

answered Sep 7, 2017 at 9:46

Eugene Kaurov

3,06734 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Taufik Nur Rahmanda Over a year ago

I once have though about that, then I wonder is there any way to do that for all columns in one go :/ My table have like twenty or more columns (I need them all), so it's a waste of code and also unreliable when later the table have new column added or removed. I still prefer to use my PHP foreach loop to replace the empty values more convenient than mysql IFNULL() one by one for each column.

Eugene Kaurov Over a year ago

in future development, if your tables would be that big, you will want to keep your application fast and effective. It means that you will definitely never request all the fields but exactly that you need to display, so you will always list exact fields names. Or, you will use ORM and frameworks instead of this old school SQL+PHP mess.

Taufik Nur Rahmanda Over a year ago

okay, guess this is the only way :) but this answer is only work for null column, won't work for empty column. The more correct way is to use IF() function, e.g.

SELECT IF(FIRSTNAME IS NULL OR FIRSTNAME='','-',FIRSTNAME) FIRSTNAME,IF(LASTNAME IS NULL OR LASTNAME='','-',LASTNAME) LASTNAME,IF(GENDER IS NULL OR GENDER='','-',GENDER) GENDER,IF(DOB IS NULL OR DOB='','-',DOB) DOB,IF(ADDRESS IS NULL OR ADDRESS='','-',ADDRESS) ADDRESS FROM tm_survei WHERE ID_SURVEI=5

anyway, thank you to tell me that replace all empty/null columns in one go is not (maybe not currently) possible.

Shiz Over a year ago

I have 56 columns, and about 4 of them are in NULL. Do I REALLY have to put all column names in the select? Isn't there something like Select *, IFNULL(Column1)... or something similar?

Eugene Kaurov Over a year ago

@Shizukura feel free to use *

Collectives™ on Stack Overflow

MySQL SELECT all columns but replace null or empty columns with certain default value

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related