how to make a mysql query that contains multiple id separated by comma

Asked

Viewed 480 times

3

good afternoon, how can I make an appointment where I have in my field idProduto several ids of product separated by comma and list each of its respective. example: in my idProduto I have the ids 1,2,6 I want that in my consultation he returns me with the inner join the data of the respective products containing those 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' "
php mysql

  • Like this the contents of this variable $idPedido?

  • intact this content would be to pick up the id of the respective request for example let’s say the id of the request be 1 then it will return me only the data of this respective id, and within that request with the id=1 I have several products purchased. so this $idPedido and more to know the data of the respective selected order

  • Ever tried to use WHERE p.id IN ('$idPedido') instead of just WHERE p.id = '$idPedido' ?

  • But in case of more than one id like this variable gets? (1,2,3,4,5) ?

  • not only will there be 1 id in this variable which will exist more than one id and in the idProduct

  • I think you’ve got the wrong intention $idPedido this correct what I want and list the products that are in the field idProduto for example I put in select Prod.name I want the name of each product that contains the id = idProduto In the example I have the idProduto with 3 ids 1,2,6 yet it shows me 3 names that are in the id 1,2,6 table produto

  • Your you put 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 = '3' works?

  • if I put in the p.id that and the order id it will return me all the data where the p.id = 3 up to ai blz so I want it to return all the names of prod.nome

  • it only returns me the first name and as I have 3 ids he would have to return me the 3 names. ex: p.idProduto contains the following id 1,2,3 blz when I do the inner join prod.id = p.idProduto i speak every id of the produto it has to be the same as the one in my column idProduto or be it will look for the following ids on the table produto 1,2,3 and bring me the names that are in those ids but he brings me only the first name I want him to return to me 3 name the name of id = 1, the of id = 2, and that of id = 3, not only that of id = 1

  • INNER JOIN produtos prod ON prod.id IN (p.idProduto) The above instruction could work since the values of the p.idProduto are separated by comma, but as it returns a string it reads the number up to the first comma returning only one result. What I would do is 2 queries one relating the order with the client dai vc would then have the requests, using PHP would inject in another SQL statement making the SELECT in the product table the id’s using the WHERE id IN ().

  • I tried to make the idProduto in a variable and made the following query SELECT nome FROM produto WHERE id IN($listadeProduto) however he brought me only the first result again, he did not go through my id he only caught the first again

  • Do this, take the result with the product id’s and one explodes: $idProdutos = explode(',',$idProdutos); Now vc has transformed the string into an array, each id is now separated, now just vc inside the parentheses of the IN concatenate and make a foreach: (. foreach($idProdutos as $idProduto): echo $idProduto","; endforeach;.)

Show 7 more comments

2 answers

0

If you pass the query string with double quotes as there below you can play directly the variable as there below.

Then just use the IN clause, but have to ensure that the data comes separated by commas always.

"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 IN ($idPedido)"

0

You can user 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 row, with the product names separated by ',' or any other delimiter, you can use the GROUP_CONCAT function and group the result per 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;

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.