CONCAT n VALUES

Question

I have multiple fields name as this

custom_1_field
custom_2_field
custom_3_field
.
.
custom_N_field

I never know how much custom_N_fields. I need to concat all of them.

I can concate with this code if i kwow how much fields i have

CONCAT_WS(' ',custom_1_field,CONCAT('<hr>',custom_2_field)) AS new_field

But if i don't know how much N custom_N_fields I have, what can i do?

There are something like CONCAT custom_N_fields where 'N' it's any number?

EDIT: i use php+sql

Thanks

Answer 1

If you don't like the SQL answer I suggested above, you could very easily (much more easily!) solve the problem within PHP (or whatever programming platform you are using). Simply do your query and grab the list of fields from the resulting query on the first fetch and then construct your string from those fields. Something like this:

$sql = 'SELECT ...';
$results = mysqli_query($conn, $sql);
$field_list = array();
while ($row = mysqli_fetch_array($results, MYSQLI_ASSOC)) {
    if (!$field_list) {
        $field_list = preg_grep('/^custom_\d+_field$/', array_keys($row));
    }
    // work with $row until you need the concatenated field...
    // then use this snippet to construct the concatenated field
    $newstr = $sep = '';
    foreach ($field_list as $f) {
        $newstr = $sep.$row[$f];
        $sep = ' '; // this will be put in front of all the rest of the fields
    }
    // now echo your $newstr or whatever you want to do with it
}

I would be interested in feedback from others on the construction of the string using $sep as I have done it - it's a construction I use often and it feels inelegant and inefficient and I'd be interested in a better way to do it.