CODEWITHSUNDEEP
mysql
user1489049
user1489049

Reputation:

MySQL: Display multiple results as a new row?

Ok, I have to admit I am not a genius with MySQL/phpMyAdmin but I'm always willing to learn. Theres currently a problem which I can't solve - spend the last few hours with Google and decided to ask here. This is what I've got:

SELECT products.products_id AS ID, media.file AS Image 
FROM products 
LEFT JOIN media_link ON media_link.link_id = products.products_id 
JOIN media ON media.id = media_link.m_id 
ORDER BY ID;

and it works, basically. It looks like this:

ID | Image
1  | image.jpg
2  | image.jpg
2  | image2.jpg
3  | image.jpg

Is there a way to display it like this?

ID | Image     | Image2
1  | image.jpg |
2  | image.jpg | image2.jpg
3  | image.jpg |

I'm really not an expert, but is it even possible? Any help would be highly appreciated.

Upvotes: 0

Views: 36

Answers (1)

Raging Bull
Raging Bull

Reputation: 18767

That will not be a good idea if you have unknown number of images for a single product.

I would suggest you to use GROUP_CONCAT instead:

SELECT products.products_id AS ID, GROUP_CONCAT(media.file) AS Image 
FROM products 
LEFT JOIN media_link ON media_link.link_id = products.products_id 
JOIN media ON media.id = media_link.m_id 
GROUP BY products.product_id
ORDER BY products.product_id;

The result will be like:

ID  Image     
1   image.jpg 
2   image.jpg,image2.jpg
3   image.jpg

Upvotes: 2

Related Questions