把一对多相关的表放在一起，pdo

Question

我已经安装了PDO，它可以正常工作。我可以添加、编辑和删除等。所以问题就在这里。DB中存在one to many关系，当我获取父表时，我希望它能到达相关的子表。

与PDO的数据库连接。

private $server = "localhost";
private $username = "name";
private $password = "password";
private $database = "database_name";
protected function connect(){
$dns = "mysql:host=" . $this->server . ";dbname=" . $this->database;
$pdo = new PDO($dns, $this->username, $this->password);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
return $pdo;
}

用户类别

class Users extends Dbc {
public function get_users(){
$sql = "SELECT user.id as u_id, 
user.user as u_name, 
schedule.id as s_id, 
schedule.status as s_status, 
schedule.content as s_content,
schedule.return_date as s_date,
schedule.remarks as s_remarks
FROM users user
JOIN schedules schedule ON schedule.user_id = u.id";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$data = [];
while ($row = $stmt->fetch()) {
$schedules = [
'id' => $row['s_id'],
'status' => $row['s_status'],
'content' => $row['s_content'],
'date' => $row['s_date'],
'remarks' => $row['s_remarks'],
];
if (!isset($data[$row['c_id']])) {
$row['u_id'] = [
'id' => $row['u_id'],
'name' => $row['u_name'],
'schedules' => [$schedules]
];
} else {
$row['u_id']['schedules'][] = $schedules;
}
echo json_encode($data);

}   
}

当然，这只返回users表，那么获取包含子表的users表的正确方法是什么呢。。。

Answer 1

PDO中没有任何可更改的内容。您只需要更改SQL及其处理方式。我在关于使用PDO获取一对多的文章中对此进行了解释，但简而言之，您需要编写一个使用JOIN从子表中获取记录的查询，然后在获取循环中添加一个条件，以避免结果数组中的重复。

这个例子取自我的文章，因为你没有提供关于你的表的任何细节：

$sql = "SELECT c.id as c_id, c.name as c_name, c.url, 
p.id as p_id, p.name as p_name, p.price
FROM category c
JOIN product p ON p.category_id=c.id
ORDER BY c.name, p.name
LIMIT ?,?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$start, $limit]);
$data = [];
while ($row = $stmt->fetch()) {
$product = [
'id' => $row['p_id'],
'name' => $row['p_name'],
'price' => $row['price'],
];
if (!isset($data[$row['c_id']])) {
$row['c_id'] = [
'name' => $row['c_name'],
'url' => $row['url'],
'products' => [$product]
];
} else {
$row['c_id']['products'][] = $product;
}
}