Search code examples
mysqljoin

Join 2 tables, i want 1 row from first table and many value in array form from second table, in only one query

I have two tables at mysql database. Example;

tbl_news                  tbl_imgs
----------------------    --------------------------
Id | newsId | topic       Id  | newsId | imgName
1  |  1     | "top1"      1   | 1      | "img1.jpg"
                          2   | 1      | "img2.jpg"
                          3   | 1      | "img3.jpg"
                          4   | 1      | "img4.jpg"

I will join these two tables in only one query. But i want result in array form return data at tbl_imgs table. Is this possible? Or can you say something similar to this? Thanks.

Edit:
I want get caption and images in array form at one query as in this picture I want get caption and images in array form at one query as in this picture

Solution

  • Use a simple GROUP BY with the GROUP CONCAT:

    select tbl_news.id as id , GROUP_CONCAT(tbl_imgs.imgName) 
from tbl_news 
JOIN  tbl_imgs on tbl_imgs.newsId = tbl_news.newsId

    Though the grouped values will be comma separated and you can convert them into an array easily.