SQL + PHP Display multidimensional array

Question

My SQL Querie returns those values :

Column1     Column2
V1          V1
V1          V2
V1          V3
V1          V4
V2          V1
V2          V2
V3          V1

Fetchall() stores result in a Array like that.

Array
(
    [0] => Array
        (
            [Column1] => V1         
            [Column2] => V1
        )

    [1] => Array
        (
            [Column1] => V1         
            [Column2] => V2
        )

    [2] => Array
        (
            [Column1] => V1         
            [Column2] => V3
        )

    [3] => Array
        (
            [Column1] => V1         
            [Column2] => V4
        )
    [4] => Array
        (
            [Column1] => V2
            [Column2] => V1
        )
    [5] => Array
        (
            [Column1] => V2
            [Column2] => V2
        )
    [6] => Array
        (
            [Column1] => V3
            [Column2] => V1
        )
)

I would like to display like that. I try with double foreach ...

HI, show us the code you have written, it will help us to provide a better answer — RiggsFolly
– RiggsFolly, Commented Sep 5, 2018 at 18:10
I don't see the logic in your output. You have 14 values but only want to display 10. But it seems almost random. — Andreas
– Andreas, Commented Sep 5, 2018 at 18:12

RiggsFolly · Accepted Answer · 2018-09-05 18:50:12Z

1

A simple foreach loop should do this quite happily. All you have to remember the value of Column1 so you can tell when the primary key for want of a better word has changed.

I tested this on the CLI so you may have to amend it to use some HTML if you want it to show nicely ona browser.

$arr = [
        ['Column1'=>'V3' , 'Column2'=>'V1'],
        ['Column1'=>'V1' , 'Column2'=>'V1'],
        ['Column1'=>'V2' , 'Column2'=>'V1'],
        ['Column1'=>'V1' , 'Column2'=>'V2'],
        ['Column1'=>'V1' , 'Column2'=>'V3'],
        ['Column1'=>'V1' , 'Column2'=>'V4'],
        ['Column1'=>'V2' , 'Column2'=>'V2'],
];

$col1 = array_column($arr, 'Column1');
$col2 = array_column($arr, 'Column2');

array_multisort($col1, SORT_ASC, $col2, SORT_ASC, $arr);

$lastKey = null;
foreach($arr as $subarr) {
    if ( $lastKey != $subarr['Column1'] ) {
        echo $subarr['Column1'] . PHP_EOL;
        echo "\t" . $subarr['Column2'] . PHP_EOL;
        $lastKey = $subarr['Column1'];
    } else {
        echo "\t" . $subarr['Column2'] . PHP_EOL;
    }
}

RESULT

edited Sep 5, 2018 at 18:50

answered Sep 5, 2018 at 18:28

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Andreas Over a year ago

Perhaps sort the array on column1 first to make sure there is no V1 further down?

RiggsFolly Over a year ago

@Andreas Yup, that would be a good idea. I was assuming the database query would have done that, and would be the better place to do it to be honest

Andreas Over a year ago

Assuming is the first step towards a bug ;-)

RiggsFolly Over a year ago

@Andreas To be fair, OP does start the question with My SQL Querie returns those values

RiggsFolly Over a year ago

@Andreas No problem, was just chatting not having a go at you :)

|

Minwork · Accepted Answer · 2018-09-06 07:19:20Z

0

From what I see you want to group results by Column1 value. In that case you can simply write:

$result = [];

foreach ($array as $row) {
    // Using PHP 7.1+
    ['Column1' => $key, 'Column2' => $value] = $row;
    // Using PHP before 7.1
    $key = $row['Column1'];
    $value = $row['Column2'];

    if (! array_key_exists($key, $result)) {
        $result[$key] = [$value];
    } else {
        $result[$key][] = $value;
    }
}

answered Sep 6, 2018 at 7:19

Minwork

1,07211 silver badges10 bronze badges

Collectives™ on Stack Overflow

SQL + PHP Display multidimensional array

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related