netdjw netdjw - 4 months ago 11
MySQL Question

How to join strings in MySQL as PHP join function?

I have this data structure in MySQL:

+-----------+-----------+--------------+
| name | barcode | factory_code |
+-----------+-----------+--------------+
| product 1 | 123456789 | |
| product 2 | 987654321 | |
| product 3 | | AAAAAAAAAAAA |
| product 4 | | BBBBBBBBBBBB |
| product 5 | 111111111 | CCCCCCCCCCCC |
+-----------+-----------+--------------+


And now I need to generate this result and show in HTML:

+-----------+-------------------------+
| name | code |
+-----------+-------------------------+
| product 1 | 123456789 |
| product 2 | 987654321 |
| product 3 | AAAAAAAAAAAA |
| product 4 | BBBBBBBBBBBB |
| product 5 | 111111111, CCCCCCCCCCCC |
+-----------+-------------------------+


The fields are not
NULL
, just empty.

I think I generate this with a MySQL query directly, to code less and maybe it will be work faster a little bit. I can do it with PHP's / Perl's
join
function.

It is possible to do directly in MySQL?

Answer

You can use concat_ws() in MySQL. Since you don't have NULL but empty strings, you will need to implement a bit of logic to filter out those empty fields.

SELECT 
    name, 
    concat_ws(
        ', ', 
        IF(barcode = '', NULL, barcode), -- explicit check for empty string
        IF(factory_code = '', NULL, factory_code)
    ) as 'code' 
FROM Table1;

Try it here.

However, it's not going to give you a lot more speed to have the DB do it. For this rather small amount of data, it is negligible and I think it's not even worth thinking about.

In Perl, you would also have to add checks for empty strings.

while (my $res = $sth->fetchrow_arrayref) {
    my $name = shift @$res;
    my $code = join ', ', grep { $_ } @$res;
    # and then do stuff
}

The grep { $_ } @$res filters out all values that are not considered to be a true value. That is, empty strings as well as NULL values, which are represented by undef.

From a maintenance perspective (if that matters), going with doing it in the application rather than the SQL is preferable1. If it's throwaway code, I would personally go with the one that takes me the least amount of thinking.

1) That is, if we are talking about plain SQL mixed inside of a small application. On a larger scale, and if there is an actual model layer, I would move the aggregation into that model.