How do I get a query to return an array in MYSQL?

Question

The query code

 SELECT * FROM products 
              LEFT JOIN (SELECT * FROM product_images ) as images ON products.id = images.productId
              LEFT JOIN (SELECT * FROM product_shippings ) as shipping ON products.id = shipping.productId
              WHERE products.id = :productID

I have multiple images in product_images, but it's only returning one instance.

What I want to do is get it all in a associate array, like images containing a list of images, shipping containing a list of shipping items, etc.

I know how to achieve this using separate queries, but that is way too slow according to my benchmarks.

So I want something like

 {
images:[...],
shipping[...],
productId:2,
productTitle:'Hello'
}

How do I fix this issue?

Rick James · Accepted Answer · 2019-07-19 04:50:38Z

1

SELECT *
    FROM products 
    LEFT JOIN product_images as images      ON products.id = images.productId
    LEFT JOIN product_shippings as shipping ON products.id = shipping.productId
    WHERE products.id = :productID

Or, to get commalists (not "arrays")

SELECT  p.id, p.title,
        GROUP_CONCAT(i.image) AS images,
        GROUP_CONCAT(s.shipping) AS shippings
    FROM products AS p
    LEFT JOIN product_images AS i     ON p.id = i.productId
    LEFT JOIN product_shippings AS s  ON p.id = s.productId
    WHERE p.id = :productID

answered Jul 19, 2019 at 4:50

Rick James

144k15 gold badges144 silver badges255 bronze badges

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

2 Comments

calimses Over a year ago

Is what Im trying to achieve even better than running seperate queries to fetch the data individually?

Rick James Over a year ago

@calimses - "Better" in what sense? Multiple queries would be slower (in this case). It depends on what you do with the results -- which set of output is easiest to work with? Also, note that GROUP_CONCAT has a limit of 1024 bytes, which could lead to chopping off results.

drussey · Accepted Answer · 2019-07-19 04:40:39Z

0

If you take away the GROUP BY you will get all the images for the product.id in the WHERE clause.

answered Jul 19, 2019 at 4:40

drussey

7234 silver badges9 bronze badges

1 Comment

calimses Over a year ago

I updated my question, was a bit obtuse but I hope it makes things more clear.

Collectives™ on Stack Overflow

How do I get a query to return an array in MYSQL?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related