I have a MySQL table where I need in a single result fields that are in 3 different columns. I'll give you 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 fetches the result in 3 columns and returns me in a single array.
Thanks for the force ...
The following query will return exactly as you requested: 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
As we all know, the default way to declare arrays in PHP is as follows:
$ names = array ('joaquim', 'Ricardo', 'Rafael');
As of PHP5.4, we have a new way of declaring arrays
$ names = ['joaquim', 'Ricardo', 'Rafael'];
Regardless of your question, there are several interpretations of the table and column names. Depending on the names the querys can be:
SELECT DISTINCT col.A FROM col UNION SELECT DISTINCT col.B FROM col UNION SELECT DISTINCT col.C FROM col
or, which gives 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 have even a dot in the names then the names of the columns in the query must be enclosed in inverted commas:
$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);
Only using MySQL!
You can use CONCAT_WS to grab all columns separated by commas will result in:
Joaquim, Sérgio, Ricardo
Ricardo, Maria, César
Rafael, Solange, Joaquim
Then use GROUP_CONCAT to join them all in one line, like this:
Joaquim, Sérgio, Ricardo, Ricardo, Maria, César, Rafael, Solange, Joaquim
At the end you will have this:
SELECT GROUP_CONCAT(
CONCAT_WS(',', 'Col.A', 'Col.B', 'Col.C'))
FROM tabela
Okay, this will do all the work without PHP. However, you should note the setting of group_concat_max_len in your my.cnf , you can also see using ( SHOW VARIABLES LIKE '%group_concat_max_len%' ) and if GROUP_CONCAT is too long will be limited by this setting.
The use of the CONCAT_WS () function in my point of view is much better, and native!
There's no need to use the application in this case, unless it's something interim.