PHP MySQL join table as JSON

Question

I have two different tables – one called "products" which contains information about products for an online shop and another table called "images" which contains the respective product images (several images for each product). I want to join the table "images" on the main table "products" and output the result in JSON format.

The table "products" looks like the following (simplified):

id  | name
----|--------
57  | apple
58  | tomato
59  | ...

And the table "images" for the product images:

img_id | img         | p_id | listorder
-------|-------------|------|----------
32     | apple1.jpg  | 57   | 1
33     | apple2.jpg  | 57   | 2
34     | tomato1.jpg | 58   | 1
35     | ...         | ...  | ...

By now, my Query is this:

$sql = "SELECT
        p.id as p_id,
        p.name as p_name,
        i.*
        FROM products p
        JOIN (SELECT * FROM images WHERE listorder=1)
        AS i ON (i.p_id = p.id)";

Hence the output (after fetching the data into an array and converting to JSON) looks something like this:

[{
  "id": "57",
  "name": "apple",
  "img": "apple1.jpg"
}, {
  "id": "58",
  "name": "tomato",
  "img": "tomato1.jpg"
}]

So my question is: How can I output the following?

[{
  "id": "57",
  "name": "apple",
  "img": [{"img_id": "32","img": "apple1.jpg"}, {"img_id": "33","img": "apple2.jpg"}]
}, {
  "id": "58",
  "name": "tomato",
  "img": [{"img_id": "34","img": "tomato1.jpg"}]
}]

How did you obtain your current JSON output? Is this from MySQL or PHP? — Tim Biegeleisen, Nov 26 '16 at 13:59
most probably manually i think you would have to make your own array and json encode that i think using multiple queries — Buddhi741, Nov 26 '16 at 14:11

score 2 · Answer 1 · answered Nov 26 '16 at 15:19

SELECT
    p.id as p_id,
    p.name as p_name,
    group_concat(concat('{"img_id":"',i.img_id,'"img":"',i.img,'"}') separator ',') as img
FROM products p
    JOIN (SELECT * FROM images WHERE listorder=1)
    AS i ON (i.p_id = p.id)
GROUP BY p.id,p.name

score -2 · Answer 2 · answered Nov 26 '16 at 14:07

-2

Decode the JSON to Array and merge them, then Encode to JSON Again. Try doing it

Two Functions:

array_merge($array1, $array2)

json_encode()

answered Nov 26 '16 at 14:07

user7213311

1

Thanks for the answer - while it is often useful to link to documentation elsewhere online, it would also be really helpful if you could demonstrate with some code how these functions can be used to approach the original question. – Alex Young Nov 26 '16 at 14:21

PHP MySQL join table as JSON

2 Answers2