Merge SQL query result for each entity in PHP multi-dimensional array

Question

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

Answer 1

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>