Group Mysql output in PHP

Question

I have two tables in my database cad_usuario and the cad_automovel. The User registered in the first table may have several vehicles registered in the second table.

SELECT aut.usuario_id, aut.marca, aut.modelo, aut.ano, usr.id, usr.nome_completo, usr.rg FROM cad_automovel as aut INNER JOIN cad_usuario as usr on usr.id = aut.usuario_id

The problem is that when showing the result in PHP, it shows two lines to the same user

 ----------
id_usuario: 1
nome_completo: John Doe
rg: 000.000.000
aut.usuario_id: 1
aut.marca: marca1
aut.modelo: modelo1
aut.ano: 2017
---------
id_usuario: 1
nome_completo: John Doe
rg: 000.000.000
aut.usuario_id: 1
aut.marca: marca22
aut.modelo: modelo22
aut.ano: 2007

I need it to look something like this:

    ---------------
    id_usuario: 1
    nome_completo: John Doe
    rg: 000.000.000
    aut.usuario_id: 1
    aut.marca: marca1, marca22
    aut.modelo: modelo1, marca22
    aut.ano: 2017, 2007

Answer 1

To group multiple lines separating by comma, you can use the GROUP_CONCAT

SELECT
   usr.id,
   GROUP_CONCAT( aut.marca ),
   GROUP_CONCAT( aut.modelo ),
   GROUP_CONCAT( aut.ano ),
   usr.nome_completo,
   usr.rg
FROM
   cad_automovel AS aut
   LEFT JOIN cad_usuario as usr
      ON usr.id = aut.usuario_id
GROUP BY
   usr.id

See working on SQL Fiddle.

Notes

• The GROUP_CONCAT has a limit defined by group_concat_max_len. Strings longer length are truncated. This value is usually 8192 (bytes). If you need more, you need to adjust the configuration;

• Mysql does not accept space between the function name and the argument list. Correct is GROUP_CONCAT( campo ), and not GROUP_CONCAT ( campo );

• Within the function call can be used operators DISTINCT and ASC or DESC to organize the results. See more details in the manual.

Handbook: https://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html#function_group-Concat