php 将两个数据库表中的数据分组为一对多的父子关系

xxe27gdn 于 2023-05-12 发布在 PHP

关注(0)|答案(2)|浏览(137)

我想将一个查询中的数据合并到具有一对多关系的另一个查询的结果中。这种关系将是一个任务对许多卡片。
我想创建这样的东西：

Array
 (
    [0] => Array
      (
        [id] => 1
        [name] => open
        [task] => Array
                 (
                 [0] => Array
                 (
                     [id] => 2
                     [project_id] => 1
                     [name] => task 2 from pro1
                     [card] => 1
                 )
                 )
      )

)

用这个：
阵列1：

Array
 (
    [0] => Array
      (
        [id] => 1
        [name] => open
    )

 )

阵列2：

Array
(
[0] => Array
    (
        [id] => 2
        [project_id] => 1
        [name] => task 2 from pro1
        [card] => 1
    )
)

假设数组1是卡片数组，数组2是任务数组。有多张卡片和任务。我需要在它所属的卡上设置一个任务，这就是为什么我在两个数组中都有一个卡ID。如果卡片数组的id为1，而任务的[card] => 1，则任务在该数组中被设置。
下面是我的代码：

$get_card_qry = "select * from tts_card where project_id = '1' ";
$get_card_res = $conn -> query($get_card_qry);

$cad = $task = array();
while($row = $get_card_res -> fetch_assoc())
{
    array_push($cad, $row);
    $get_task_qry = "select * from tts_task where card = '".$row['id']."' ";
    $get_task_res = $conn -> query($get_task_qry);
    while($row1 = $get_task_res -> fetch_assoc())
    {
        array_push($task , $row1);
    }
}

php

来源：https://stackoverflow.com/questions/38091991/group-data-from-two-database-tables-into-a-one-to-many-parent-child-relationshi

2条答案

按热度按时间

6vl6ewon1#

使用常规foreach循环的简单解决方案：

$cards = [0 => ['id' => 1, 'name' => 'open']];
$tasks = [  // add one more item/task for complex case
    0 => ['id' => 2, 'project_id' => 1,  'name' => 'task 2 from pro1', 'card' => 1],
    1 => ['id' => 3, 'project_id' => 2,  'name' => 'task 3 from pro1', 'card' => 1]
];

foreach ($cards as &$card) {
    foreach ($tasks as $task) {
        if ($task['card'] === $card['id']) $card['task'][] = $task;
    }
}

print_r($cards);

输出：

Array
(
    [0] => Array
        (
            [id] => 1
            [name] => open
            [task] => Array
                (
                    [0] => Array
                        (
                            [id] => 2
                            [project_id] => 1
                            [name] => task 2 from pro1
                            [card] => 1
                        )

                    [1] => Array
                        (
                            [id] => 3
                            [project_id] => 2
                            [name] => task 3 from pro1
                            [card] => 1
                        )
                )
        )
)

赞(0）回复(0）举报 2023-05-12

bvhaajcl2#

在SQL中将数据关联到行中，然后在PHP中使用临时第一级id来形成多维数组。
在第一次遇到卡ID时，创建父数组。所有遇到给定卡id的情况，将任务数据推入父数组。循环结束后，用array_values()删除分组键。
代码：（PHPize Demo）

$sql = <<<SQL
SELECT tts_card.id,
       tts_card.name,
       tts_task.id          task_id,
       tts_task.project_id,
       tts_task.name        task_name,
       tts_task.card
FROM tts_card
JOIN tts_task ON tts_card.id = tts_task.card
WHERE project_id = '1'
SQL;

$result = [];
foreach ($mysqli->query($sql) as $row) {
    if (!isset($result[$row['id']])) {
        $result[$row['id']] = [
            'id' => $row['id'],
            'name' => $row['name'],
            'task' => [],
        ];
    }
    $result[$row['id']]['task'][] = [
        'id' => $row['task_id'],
        'project_id' => $row['project_id'],
        'name' => $row['task_name'],
        'card' => $row['card'],            
    ];
}
var_export(array_values($result));

赞(0）回复(0）举报 2023-05-12

我来回答

php 将两个数据库表中的数据分组为一对多的父子关系

2条答案

相关问题

热门标签

最新问答