合并PHP多维数组中每个实体的SQL查询结果

v64noz0r 于 2022-12-10 发布在 PHP

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

First I get my SQL query result that I put in an array like this :

$stmt= $conn->prepare("SELECT p.*, f.*
                        FROM fruit f
                        left JOIN person p ON f.fk_p_id = p.p_id
                        ");
$stmt->execute();
$result= $stmt->get_result();
while ($data= $result->fetch_assoc()) {
   $arr = array('name' => $data['name'], 'fruit' =>data['fruit']);
   $props[] = $arr;
}

var_export($props);

And I get this :

array ( 0 => array (name => CHRISTIAN fruit => apple, ),
1 => array ( name => CHRISTIAN, fruit => pear, ),
2 => array ( name => CHRISTIAN, fruit  => strawberry, ),
3 => array ( name => CHRISTIAN, fruit => banana, ),
4 => array ( name => CHRISTIAN, fruit => lime, ),
5 => array ( name => JOSEF, fruit => apple, ),
6 => array ( name => JOSEF, fruit => pear, ),
7 => array ( name => BOB, fruit => apple , ),
8 => array ( name => BOB, fruit => banana, ),)

But I would like my array to be merged like this :

array ( 0 => name => CHRISTIAN, fruit => apple , pear, strawberry, banana, lime, ),
1 =>name => JOSEF, fruit => apple, pear, ),
2 =>name => BOB, fruit => apple, banana, ), )

The purpose would be to fill a table like this :
| | apple | pear | strawberry | banana | lime |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| CHRISTIAN | x | x | x | x | x |
| JOSEF | x | x | | | |
| BOB | x | | | | x |
How should I do to get this result with a multi-demensional array ? Thanks

php

来源：https://stackoverflow.com/questions/74687989/merge-sql-query-result-for-each-entity-in-php-multi-dimensional-array

2条答案

按热度按时间

ctzwtxfj1#

我的假设是您需要数组。
如果您尝试以下操作...您创建一个数组并遍历查询结果。然后使用data['name']作为键并将不同的fruit作为数组添加到fruit键中。

$arr = [];
while ($data= $result->fetch_assoc()) {
   $arr[$data['name']]['fruit'][] = $data['fruit'];
}

输出如下所示：

Array
(
    [CHRISTIAN] => Array
        (
            [fruit] => Array
                (
                    [0] => apple
                    [1] => pear
                    [2] => strawberry
                    [3] => banana
                    [4] => lime
                )

        )

    [JOSEF] => Array
        (
            [fruit] => Array
                (
                    [0] => apple
                    [1] => pear
                )

        )

    [BOB] => Array
        (
            [fruit] => Array
                (
                    [0] => apple
                    [1] => banana
                )

        )

)

对于该表，您可以尝试以下操作：

<table>
    <tr>
        <th></th>
        <th>Apple</th>
        <th>Pear</th>
        <th>Stawberry</th>
        <th>Banana</th>
        <th>Lime</th>
    </tr>
<?php foreach ($arr as $name => $value) { ?>
    <tr>
        <td><?php echo $name ?></td>
        <td><?php if (in_array('apple', $value['fruit'])) { echo "X"; } ?></td>
        <td><?php if (in_array('pear', $value['fruit'])) { echo "X"; } ?></td>
        <td><?php if (in_array('strawberry', $value['fruit'])) { echo "X"; } ?></td>
        <td><?php if (in_array('banana', $value['fruit'])) { echo "X"; } ?></td>
        <td><?php if (in_array('lime', $value['fruit'])) { echo "X"; } ?></td>
    </tr>
<?php } ?>
</table>

赞(0）回复(0）举报 2022-12-10

anauzrmj2#

在我看来，您可能会从“动态枢纽”中受益--如果您不确切地知道预期会有哪些成果的话。当涉及到动态枢纽时，MySQL不是一个优雅的工具（上次我检查过）。
我将按名称对行进行分组，并填充一个指向空字符串的fruits名称的关联数组，以便您可以填充表标题并使用这些空字符串作为默认值。
您不仅不需要在循环中调用fetch()，还可以“解构”结果对象的有效负载，就像它是一个关联数组的数组一样。
代码：（Demo）

$defaults = [];
$grouped = [];
foreach ($stmt->get_result() as ['name' => $name, 'fruit' => $fruit]) {
    $grouped[$name][$fruit] = 'x';
    $defaults[$fruit] = '';
}

$table = <<<HTML
<table border=1>
    <tr><th>%s</th></tr>%s
</table>
HTML;

$tableRow = <<<HTML

    <tr><td>%s</td></tr>
HTML;

printf(
    $table,
    implode('</th><th>', array_merge([''], array_keys($defaults))),
    implode(
        array_map(
            fn($name, $row) => sprintf($tableRow, implode('</td><td>', array_merge([$name], $defaults, $row))),
            array_keys($grouped),
            $grouped
        )
    )
);

打印HTML有点笨拙，因为名称列/数据必须附加到标题行和所有表体行之前。
单击演示链接上的眼睛图标以查看呈现的HTML输出。

赞(0）回复(0）举报 2022-12-10

我来回答

合并PHP多维数组中每个实体的SQL查询结果

2条答案

相关问题

热门标签

最新问答