how to do a mysql query containing several id separated by commas

Question

how to do a mysql query containing several id separated by commas

Navigation

#1 by (0 votes)

3

Good afternoon, how can I make a query where I have in my field idProduto several% com_dependent comma% and list each of their respective. For example, in my ids I have the idProduto I want that in my query it returns me with ids 1,2,6 the data of the respective products that contains those inner join how can I do that?

"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' "

php mysql

asked by anonymous 22.11.2017 / 17:31

1 answer

Inserting Vue Component in the DOM via jQuery Form does not download from memory

score 0 · Answer 1

You can use the find_in_set function of mysql, your query looks 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';

If you want to return the results in just one row, with the names of the products 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;