Question 41374 in PHP Question posted in
Official manual can be found here.

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

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

Tags:

2

Answers


  1. 0

    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>
    Login or Signup to reply.
  2. 0

    It seems to me that you could benefit from a "dynamic pivot" — in the event that you don’t know exactly which fruits to expect. MySQL isn’t an elegant tool (last time I checked) when it comes to dynamic pivots.

    I would group the rows by name, and populate an associative array of fruits names pointing to empty strings so that you can populate the table heading and use these empty strings as defaults values.

    Not only do you not need to call fetch() in a loop, you can "destructure" the result objects payload as if it was an array of associative arrays.

    Code: (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
        )
    )
);

    Printing the HTML is a little bit clunky because the name column/data must be prepended to the heading row and all table body rows.

    Click on the eye-icon on the demo link to see the rendered HTML output.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search