PHP implode alternative inside mysql queries

Question

I need some alternative of implode for mysql queries.After research found concat functions.

Question is, can I concat by whitespace like that CONCAT_WS(' ',sender.fname, sender.mname, sender.lname) AS sender_name ? is there any chance that it will give error if one of values is null?

Here is my query.

SELECT message.id, message.from_id, message.to_id, message.subject, 
                    message.date, message.deleted, message.read, 
                    CONCAT_WS(' ',sender.fname, sender.mname, sender.lname) AS sender_name, 
                    CONCAT_WS(' ',recipient.fname, recipient.mname, recipient.lname) AS recipient_name,  
                    FROM msghistory AS message 
                    LEFT JOIN users AS sender ON sender.id=message.from_id, 
                    LEFT JOIN users AS recipient ON recipient.id=message.to_id 
                    GROUP BY message.id DESC

One more question, in this query CONCAT_WS(' ',recipient.fname, recipient.mname, recipient.lname) AS recipient_name might not be found (if there is no matched row in users table). Will this give any error?

Niet the Dark Absol · Accepted Answer · 2012-02-13 23:54:20Z

1

CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.

That should answer your question.

answered Feb 13, 2012 at 23:54

Niet the Dark Absol

326k86 gold badges480 silver badges604 bronze badges

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

2 Comments

heron Over a year ago

One more question, in this query CONCAT_WS(' ',recipient.fname, recipient.mname, recipient.lname) AS recipient_name might not be found (if there is no matched row in users table). Will this give any error?

Niet the Dark Absol Over a year ago

If there are not matched rows, the function won't be called. Simple as that.

Mark Byers · Accepted Answer · 2012-02-13 23:52:53Z

1

Yes, you can use CONCAT_WS like that. If one of the arguments is NULL, there will be no error. The NULL argument is just not used.

It says this in the manual:

CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.

It's also very easy to verify this for yourself:

SELECT CONCAT_WS(' ', 'abc', NULL, 'def')
'abc def'

answered Feb 13, 2012 at 23:52

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

1 Comment

heron Over a year ago

One more question, in this query CONCAT_WS(' ',recipient.fname, recipient.mname, recipient.lname) AS recipient_name might not be found (if there is no matched row in users table). Will this give any error?

Kokers · Accepted Answer · 2012-02-13 23:54:16Z

1

Yes you can. If there will be null there wouldn't be any errors

answered Feb 13, 2012 at 23:54

Kokers

1,82814 silver badges19 bronze badges

6 Comments

heron Over a year ago

is there any way to prevent this extra space?

Kokers Over a year ago

I corrected my answer seconds before your comment, extra space will be if one of them will be just empty. If it's null they are skipped.

Kokers Over a year ago

So SELECT CONCAT_WS(' ','sth','sth',null,'sth',null) will return sth sth sth but SELECT CONCAT_WS(' ','sth','sth','','sth','') will return sth sth sth I don't think there is way to prevent extra space in second example

heron Over a year ago

One more question, in this query CONCAT_WS(' ',recipient.fname, recipient.mname, recipient.lname) AS recipient_name might not be found (if there is no matched row in users table). Will this give any error?

Kokers Over a year ago

It shouldn't. You should just get empty result for that CONCAT_WS

|

gahooa · Accepted Answer · 2012-02-13 23:52:17Z

0

Even though verbose, you need to use the COALESCE function when dealing with the potential for unacceptable NULL values:

CONCAT_WS(' ', COALESCE(val1, ''), COALESCE(val2, ''), ...)

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce

answered Feb 13, 2012 at 23:52

gahooa

138k14 gold badges101 silver badges104 bronze badges

1 Comment

heron Over a year ago

One more question, in this query CONCAT_WS(' ',recipient.fname, recipient.mname, recipient.lname) AS recipient_name might not be found (if there is no matched row in users table). Will this give any error?

Collectives™ on Stack Overflow

PHP implode alternative inside mysql queries

4 Answers 4

2 Comments

1 Comment

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related