我有两张桌子的食物和标签。每一排食物都有相应的标签。我想用这些标签输出每一行,即:
餐桌食品:
id |名称
1|面包
2|肉
表格标记:
reference_id |标记
1|面包房
1|小麦
2|奶牛
所需输出为:
{"结果":{"id":"1","name":"bread","tags":["bakery","小麦"]},
{"id":"2","name":"meat","tags":["cow"]}]}
到目前为止,我有这个:
$db = getConnection();
$stmt = $db->query($sql);//get every column from every food
$food = $stmt->fetchAll(PDO::FETCH_OBJ);
$tagsSql="select id_reference,tag FROM tags T,food F WHERE F.id=T.food_id_reference";
$stmt = $db->query($tagsSql);
$tags=$stmt->fetchAll(PDO::FETCH_OBJ);
echo '{"results":' . json_encode($food) . '}';
我曾想过骑自行车浏览每一种食物,并贴上标签,找到匹配的食物,但这对我来说似乎相当沉重(考虑到我可能有数千排)。你有什么建议吗?
未测试,但我认为这样的东西应该适用于
$db = getConnection();
$stmt = $db->query($sql);//get every column from every food
$tagsSql="select F.id as id, F.name as name, group_concat(T.tag SEPARATOR ',') as tags FROM tags T,feeds F WHERE F.id=T.feed_id_reference group by feed_id_reference";
$stmt = $db->query($tagsSql);
for($x = 0; $x < count($tags); $x++){
$tags[$x]->{"tags"} = explode(",", $tags[$x]->{"tags"});
echo '{"results":' . json_encode($tags) . '}';
}