php - 从具有公共(public)变量的多个表中选择不同的列

我有三张桌子：—food-toys-animals
每个表都有列-id，color，item
表有额外的列-toys，sn和更多
需要从带有date的all表中选择all项，并在客户端分别获取它们
类似于：

$color = 'red';
$sql = "select * from food, toys, animals where color = :acolor";
$st = $db->prepare($sql);
$st->execute([":acolor" => $color]);
$food = $toys = $animals = '';
while($row = $st->fetch()){
        $food .= "<div class='food' data-id = " . $row['food.id'] . ">" . $row['food.item'] . "</div>\n";
        $toys .= "<div class='toys' data-id = " . $row['toys.id'] . " data-serial = " . $row['toys.sn'] . " data-date = '" . $row['toys.date'] . "'>" . $row['toys.item'] . "</div>\n";
    }
        $animals .= "<div class='animals' data-id = " . $row['animals.id'] . ">" . $row['animals.item'] . "</div>\n";
    }
$arr = [];
array_push($arr, $food, $toys, $animals);
echo json_encode($arr);

客户端

...
data = JSON.parse(data);
$('#wrapfood').html(data[0]);
$('#wraptoys').html(data[1]);
$('#wrapanimals').html(data[2]);

最终结果是：
color = red应该有5个类wrapfood的div
food应该有9个类wraptoys的div
toys应该有21个类wrapanimals的div
我尝试了以上代码的不同版本，但没有成功——在服务器端得到错误。
有什么帮助吗？

最佳答案

你可以用这个

$data = array('food' => array(),'toys' => array(),'animals' => array());
$color = 'red';
$foodSql = "select * from food where color = :acolor";
$toySql = "select * from toys where color = :acolor";
$animalSql = "select * from animals where color = :acolor";
$ft = $db->prepare($foodSql);
$tt = $db->prepare($toySql);
$at = $db->prepare($animalSql);
$ft->execute([":acolor" => $color]);
$tt->execute([":acolor" => $color]);
$at->execute([":acolor" => $color]);
$food, $toys, $animals = '';
while($row = $ft->fetch()){
        $food .= "<div class='food' data-id = " . $row['food.id'] . ">" . $row['food.item'] . "</div>\n";
}
array_push($data['food'], $food);

while($row = $tt->fetch()){
        $toys .= "<div class='toys' data-id = " . $row['toys.id'] . " data-serial = " . $row['toys.sn'] . " data-date = '" . $row['toys.date'] . "'>" . $row['toys.item'] . "</div>\n";
}
array_push($data['toys'],$toys);

while($row = $at->fetch()){
        $animals .= "<div class='animals' data-id = " . $row['animals.id'] . ">" . $row['animals.item'] . "</div>\n";
}
array_push($data['animals'],$animals);
echo json_encode($data);

然后在客户端，您可以通过

data = JSON.parse(data);
$('#wrapfood').html(data.food);
$('#wraptoys').html(data.toys);
$('#wrapanimals').html(data.animals);

关于php - 从具有公共(public)变量的多个表中选择不同的列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/57387646/