将两个数据库表中的数据分组为一对多、父子关系 - Group data from two database tables into a one-to-many, parent-child relationship

Group data from two database tables into a one-to-many, parent-child relationship

我想将一个查询中的数据合并到另一个查询的结果中，并使用一对多的关系。关系将是1个任务对应多个卡片。

我想创建如下内容:

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为任务数组。这里有多个卡片和任务。我需要在纸牌所属的位置设置任务这就是为什么我在两个数组中都有一个cardid。如果卡片数组id为1,task也有[card] =>

下面是我的代码:

$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);
    }
}

使用规则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
                        )
                )
        )
)

在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));