CODEWITHSUNDEEP
mysql
Mihai Iorga
Mihai Iorga

Reputation: 39704

MySQL JOIN wrapping into an array

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?

Upvotes: 13

Views: 37282

Answers (2)

Quassnoi
Quassnoi

Reputation: 425341

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

Upvotes: 31

Naktibalda
Naktibalda

Reputation: 14100

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

Upvotes: 15

Related Questions