Mysql - merge results from multiple columns

Question

I have a Mysql table where I need in a single result fields that are in 3 different columns. I’ll give an example:

Col.A         Col.B       Col.C
Joaquim       Sérgio      Ricardo
Ricardo       Maria       César
Rafael        Solange     Joaquim

The result must come:

Joaquim, Sérgio, Ricardo, Maria, César, Rafael, Solange

That is, it looks for the result in 3 columns and returns me in one array.

Thank you for the force...

Answer 1

The query below will return exactly as you asked: Joaquim, Sérgio, Ricardo, Maria, César, Rafael, Solange

   $result = mysql_query("SELECT DISTINCT colA FROM SuaTabela UNION SELECT DISTINCT colB FROM SuaTabela UNION SELECT DISTINCT colC FROM SuaTabela");

    while($row = mysql_fetch_array($result))
    {
        $nome=$row['colA'];
        $array=$array.$nome.",";
    }
    //retira a última virgula do array
    $array = substr($array,0,-1);

Considerations

1. Mysql extension is discontinued, so the most ideal is to use mysqli or PDO.

2. As we already know, the default way to declare arrays in PHP is as follows:

   $names = array('Joaquim', 'Ricardo', 'Rafael');

   From PHP5.4, we have a new way to declare arrays

   $names = ['John', 'Richard', 'Raphael'];

However, your question leaves room for various interpretations of the table name and columns. Depending on the names the querys may be:

 SELECT DISTINCT col.A FROM col UNION SELECT DISTINCT col.B FROM col UNION SELECT DISTINCT col.C FROM col

or, which is the same as above

 SELECT DISTINCT A FROM col UNION SELECT DISTINCT B FROM col UNION SELECT DISTINCT C FROM col

Finally if the name of your columns has the same point in the names then the names of the columns in the query should be in inverted quotes:

  $query = mysql_query("SELECT DISTINCT `col.A` FROM stabela UNION SELECT DISTINCT `col.B` FROM stabela UNION SELECT DISTINCT `col.C` FROM stabela");

    while($row = mysql_fetch_array($query))
    {
        $nome=$row['col.A'];
        $array=$array.$nome.",";
    }
    $array = substr($array,0,-1);

Answer 2

Only using Mysql!

You can use the CONCAT_WS to pick up all columns separated by comma and shape will result in:

Joaquim, Sérgio, Ricardo
Ricardo, Maria, César
Rafael, Solange, Joaquim

Then use the GROUP_CONCAT to bring them all together in one line, like this:

Joaquim, Sérgio, Ricardo, Ricardo, Maria, César, Rafael, Solange, Joaquim

---

In the end you’ll have this:

SELECT GROUP_CONCAT(
       CONCAT_WS(',', `Col.A`, `Col.B`, `Col.C`)) 
FROM tabela

Ready, this will do all the work without PHP. However, you should note the configuration of group_concat_max_len in his my.cnf, you can also see using (SHOW VARIABLES LIKE '%group_concat_max_len%') and whether the GROUP_CONCAT use is too long will be limited by this setting.

---

Answer 3

the use of CONCAT_WS() function in my view is much better, and native!

There is no need to use the application in this case unless it is something interim.