Group Mysql result in PHP

Question

Group Mysql result in PHP

Navigation

#1 by (3 votes)

1

I have two tables in my database, the cad_usuario and the cad_automovel . The user registered in the first table can 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 it shows the result in PHP, it shows two lines for 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

php mysql sql

asked by anonymous 11.04.2017 / 00:57

1 answer

BonitaSoft Form Query to the Web Service of the City of São Paulo always returns "Request XML Message of the service without content"

score 3 · Accepted Answer

To group multiple lines by separating by commas, you can use 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 in SQL Fiddle .

Notes

The GROUP_CONCAT has a limit defined by group_concat_max_len . Strings of 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. The correct is GROUP_CONCAT( campo ) , not GROUP_CONCAT ( campo ) ;
Within the function call, the DISTINCT and ASC or DESC operators can be used to organize the results. See the manual for more details.

Manual: link