Separate a mysql array into a query

Question

I've a problem with MySQL query. I'm trying select products info over the order products list, but it are selecting only the first record, being that the products list is a array. Example: being orders.itens = "10,11,12,13", the "IN" selects only the first ID, "10". How I do to select all ID's, something that sounds like implode function in PHP?

SELECT
    orders.id,
    products.name,
    products.price
FROM 
    orders,
    products
WHERE
    products.id IN (orders.itens)

Thank you

You need to give the values seperately in th IN clause like IN ("10","11","12","13") — 웃웃웃웃웃
– 웃웃웃웃웃, Commented Mar 20, 2014 at 14:41
I'm using a multiple mysql query, so I can't use any PHP functions.. The orders.itens row is exactly how I posted: "10,11,12,13". I need a MySQL function to divide.. — Cristian Augusto
– Cristian Augusto, Commented Mar 20, 2014 at 14:48

Gordon Linoff · Accepted Answer · 2014-03-20 14:49:41Z

2

You want to do a join:

SELECT o.id, p.name, p.price
FROM orders o join
     products p
     on find_in_set(p.id, o.items) > 0;

Unfortunately, there is no way to optimize this query. You should created an association/junction table, probably called OrderProducts that contains one row for each product in an order.

answered Mar 20, 2014 at 14:49

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Cristian Augusto Over a year ago

But if I'll create a row for each product, the table will be giant

Gordon Linoff Over a year ago

@CristianAugusto . . . This is how databases are designed to be used. What is "giant" anyway? Unless you are talking about billions of rows, then your table is not "giant".

Cristian Augusto Over a year ago

But if a order have 10 products, how I do to create a row for each product?

Gordon Linoff Over a year ago

@CristianAugusto . . . You need to re-organize your database so you have an OrderProducts table. Such as table would have rows like (Order=1, Product=a), (Order=1, Product=b), and (Order=1, Product=c). A separate row for each product in an order. Google "junction table" or "association table" -- you'll get lots of hits with lots of explanation.

웃웃웃웃웃 · Accepted Answer · 2014-03-20 14:52:31Z

1

I guess you need something like this

select CONCAT('"',CONCAT(Replace("10,11,12,13", ',', '","'),'"')) as str

And use this as the value for in clause

answered Mar 20, 2014 at 14:52

웃웃웃웃웃

12k15 gold badges63 silver badges95 bronze badges

Collectives™ on Stack Overflow

Separate a mysql array into a query

2 Answers 2

4 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Related