php – how to make a mysql query that contains multiple comma-separated id

Question:

good afternoon, how can I do a query where I have in my field idProduto several ids separate product comma and list each of your respective. example: in my idProduto I have the ids 1,2,6 I want that in my query it returns me with the inner join the data of the respective products that contain these ids how can I do this?

"SELECT p.id, p.idProduto, p.total, p.rastreio, p.envio, p.qtd, p.status, p.data, c.nome AS nomeCliente, c.email, c.telefone, c.cpf, c.cep, c.nResidencial, prod.nome FROM pedidos p INNER JOIN clientes c ON p.id_cliente = c.id INNER JOIN produtos prod ON prod.id = p.idProduto WHERE p.id = '$idPedido' "

Answer:

You can use the mysql find_in_set function, your query would look like this:

SELECT
    p.id,
    p.idProduto,
    p.total,
    p.rastreio,
    p.envio,
    p.qtd,
    p.status,
    p.data,
    c.nome AS nomeCliente,
    c.email,
    c.telefone,
    c.cpf,
    c.cep,
    c.nResidencial,
    prod.nome
FROM
    pedidos p
INNER JOIN clientes c ON p.id_cliente = c.id
INNER JOIN produtos prod ON FIND_IN_SET(prod.id,p.idProduto)
WHERE
    p.id = '$idPedido';

and if you want to return the results in just one line, with the product names separated by ',' or any other delimiter, you can use the GROUP_CONCAT function and group the result by order, which would look like this:

SELECT
    p.id,
    p.idProduto,
    p.total,
    p.rastreio,
    p.envio,
    p.qtd,
    p. STATUS,
    p. DATA,
    c.nome AS nomeCliente,
    c.email,
    c.telefone,
    c.cpf,
    c.cep,
    c.nResidencial,
    GROUP_CONCAT(prod.nome SEPARATOR ',') AS nome 
FROM
    pedidos p
INNER JOIN clientes c ON p.id_cliente = c.id
INNER JOIN produtos prod ON FIND_IN_SET(prod.id,p.idProduto)
WHERE
    p.id = '$idPedido'
GROUP BY p.id;
Scroll to Top