PHP MYSQL INNER JOIN to JSON

Question

I had a two table,

First one : table product table product

| id | name   | code |
| 1  | blouse | AB1  |
| 2  | jeans  | BA2  |
| 3  | dress  | C61  |

and the second table color table color

| id_product | colors | amount |
|     1      | blue  |  50    |
|     1      | red   |  40    |
|     1      | yellow|  10    |
|     2      | white |  15    |
|     2      | blue  |  60    |
|     3      | purple|  110   |

Query : my query php

.../blabla
SELECT product.id, product.name, product.code,color.id_product, color.colors, color.amount
FROM product
INNER JOIN color 
ON product.id = color.id_product limit 1

while($query)) {
    $var[] = $query;
}
echo '{"status":'. json_encode($var).'}';

OUTPUT :

{
    "status":{
        "1":{
            "id":"1",
            "code":"blouse",
            "id_product":"1",
            "colors":"blue",
            "amount":"50",
        }
    }
}

what i want JSON like this :

{
    "status":{ 
        "1":{  
            "id":"1",
            "code":"blouse",
            "id_product":"1",
            "colors":"blue",
            "amount":"50",
            "id_product":"1",
            "colors":"red",
            "amount":"40",
            "id_product":"1",
            "colors":"yellow",
            "amount":"10",
        }
    }
}

Need color into one array json, is that possible?

Tech Savant · Accepted Answer · 2015-04-10 06:29:02Z

2

So you should probably use group_concat to get them into a list and then use PHP to explode the list before encoding to json.

SQL Fiddle Here

http://sqlfiddle.com/#!9/ba3b8/3

SELECT product.id, product.name, product.code,
color.id_product, color.colors, color.amount,
group_concat(color.colors) as clist,
group_concat(color.amount) as alist

from product, color
where product.id = color.id_product
group by color.id_product

Then in your PHP you would need to loop through the results, using explode to convert the list to an array and inserting the data into the results as needed PRIOR to using json_encode();

edited Apr 10, 2015 at 6:29

answered Apr 10, 2015 at 6:02

Tech Savant

3,7671 gold badge21 silver badges40 bronze badges

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

Comments

Jens · Accepted Answer · 2015-04-10 06:01:31Z

0

You should use this query:

SELECT product.id, product.name, product.code
color.id_product, color.colors, color.amount
from product
INNER JOIN color 
ON product.id = color.id_product
where product.id=1

LIMITwill give you exactly one record.

answered Apr 10, 2015 at 6:01

Jens

69.6k15 gold badges107 silver badges133 bronze badges

Collectives™ on Stack Overflow

PHP MYSQL INNER JOIN to JSON

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related