MySQL JOIN wrapping into an array

Question

I have some minor problem with an SQL query. I have to select data from multiple tables like:

offers:

| id | offer      | info  
| 1  | City break | information

pictures:

| id | id_offer | picture_name  | title  
| 1  | 1        | bucharest.jpg | Bucharest  
| 2  | 1        | london.jpg    | London

sql query:

SELECT offers.* as t1, pictures.* as t2
FROM offers  
JOIN t2 ON t1.id=t2.id_offer  
WHERE t1.id = '1'

The code is much larger but I don't understand how to wrap results from t2 into an array. Because the length of the array returned is made by t2 which is the pictures table. This will return an array with 2 objects.

It is possible to return one object in the array with both pictures in it?

Quassnoi · Accepted Answer · 2010-06-28 12:33:08Z

31

MySQL does not support array datatypes.

You can return a comma separated list of values instead:

SELECT  o.*, GROUP_CONCAT(picture_name ORDER BY p.id)
FROM    offers o
JOIN    pictures p
ON      p.id_offer = o.id
GROUP BY
        o.id

answered Jun 28, 2010 at 12:33

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

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

1 Comment

Termato Over a year ago

Don't forget the AS part so you know what the variable comes back as: GROUP_CONCAT(picture_name ORDER BY p.id) AS pictureNames so then you can use it like this: results.pictureNames :D

Naktibalda · Accepted Answer · 2010-06-28 12:32:32Z

15

Arrays doesn't exist in mysql. But you can use GROUP_CONCAT to return all images in comma separated list

SELECT offers.*, GROUP_CONCAT(t2.picture_name) AS pictures
FROM offers AS t1
JOIN pictures AS t2 ON t1.id=t2.id_offer
WHERE t1.id = '1' 
GROUP BY t1.id

answered Jun 28, 2010 at 12:32

Naktibalda

14.2k5 gold badges37 silver badges53 bronze badges

1 Comment

Termato Over a year ago

Thanks for the AS part. Makes thing look cleaner.

Collectives™ on Stack Overflow

MySQL JOIN wrapping into an array

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related