create table category(id int, category_name varchar(255));
insert into category values (1,'Цветы');
select * from category;
create table objects(id int, category_id int, object_name varchar(255));
insert into objects values (1,1,'Розы'),(2,1,'Астры');
select * from objects;
create table variations(id int, object_id int, variation_name varchar(255));
insert into variations values
(1,1,'Розовые розы')
,(2,1,'Желтые розы')
,(3,1,'Красные розы')
,(4,2,'Красные астры')
,(5,2,'Белые астры')
;
select * from variations;
select o.object_name,c.category_name
,(select group_concat(variation_name)
from variations v where v.object_id=o.id) variations
from objects o
left join category c on o.category_id=c.id;
<?php
use Carbon\Carbon;
$now = Carbon::now()->format('Y-m-d');
printf("Today is %s\nCurrent PHP version: %s \n", $now, phpversion());
$stmt = $pdo->prepare("select version() as version;" );
$stmt->execute();
$res=$stmt->fetch(PDO::FETCH_ASSOC);
printf("MySQL version is %s \n\n", $res["version"]);
$query = "
select o.id,o.object_name,c.category_name
,(select group_concat(variation_name)
from variations v where v.object_id=o.id) variations
from objects o
left join category c on o.category_id=c.id;";
$stmt = $pdo->prepare($query);
$result=$stmt->execute();
printf('Result: ' . PHP_EOL);
$rows = $stmt->fetch(PDO::FETCH_ASSOC);
while ($rows){
$target=array();
$id=$rows["id"];
$target[$id]=array();
$target[$id]["id"]=$rows["id"];
$target[$id]["name"]=$rows["object_name"];
$target[$id]["category"]=$rows["category_name"];
$target[$id]["variations"]=explode(",",$rows["variations"]);
print_r($target[$id]);
$rows = $stmt->fetch(PDO::FETCH_ASSOC);
};