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 :

How should I do to get this result with a multi-demensional array ? Thanks

CodePudding user response：

My assumption was that you needed an array.

If you try the following... You create an array and iterate over the query results. Then you use the data['name'] as a key and add the different fruits to the fruit key as an array.

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

The output looks something like this:

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
                )

        )

)

For the table you can try something like this:

<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>