How to group these arrays accordingly?

Question

I'm using PDO to run a mysql query with 2 joins and this is the result I get from the database:

Array
(
[0] => Array
    (
        [id] => 489
        [cluster_id] => 
        [label_value_id] => 4
        [label_id] => 1
        [int_value] => 40
    )

[1] => Array
    (
        [id] => 489
        [cluster_id] => 
        [label_value_id] => 6
        [label_id] => 2
        [int_value] => 20
    )

[2] => Array
    (
        [id] => 489
        [cluster_id] => 
        [label_value_id] => 9
        [label_id] => 3
        [int_value] => 10
    )

)

The idea is that there will be multiple id's later on but for simplicities sake I only have 1 entry with id 489 for now.

I need the 3 arrays (cause of the joins) to be 1 array that looks something like this, I want a subarray based on the label-value_id => int_value relationship:

Array
(
[0] => Array
    (
        [id] => 489
        [cluster_id] => 
        [label_value_id] => 4
        [label_id] => 1
        [int_value] => 40
        [vector_data] => Array
        (
            [4] => 40
            [6] => 20
            [9] => 10
        )
    )
)

I'd rather have this done in the PHP and not the Query because I dont have control over the queries when I implement this into the live application.

Any help is much appreciated!

You will need to iterate the array. Inside the loop compare the label value id and if match add another array to it with the key vector data. — frz3993, Apr 20 '15 at 13:30
@AshwaniGoyal label_id is irrelevant at this point. Usable answer is provided by Ghost — Jebble, Apr 20 '15 at 13:36
Did you try `$sth->fetchAll(PDO::FETCH_ASSOC|PDO::FETCH_GROUP);`? — cn007b, Apr 20 '15 at 13:49
There is a FETCH_GROUP?! I'll try that tomorrow. That might actually give a performance boost on my algorithm — Jebble, Apr 20 '15 at 19:32

Kevin · Accepted Answer · 2015-04-20T13:45:02.073

1

You can use another container for gathering all batches which share the same id using its keys to transfer them. On initialization, create another container for that vector, then just push them line any normal array value:

$result = array();
foreach($array as $values) {
    // initialization
    if(!isset($result[$values['id']])) {
        $result[$values['id']] = $values;
    }

    $result[$values['id']]['vector_data'][$values['label_value_id']] = $values['int_value'];
}

// $result = array_values($result); // optional key reindex

Sample Output

edited Apr 20 '15 at 13:45

answered Apr 20 '15 at 13:29

Kevin

41,694
12
53
70

Exactly what I needed, works as intended with multiple id's as well. Thanks! – Jebble Apr 20 '15 at 13:35
@Jebble yes, it will work on that as well. im glad this helped – Kevin Apr 20 '15 at 13:38
I see now that I had exactly this except for your !isset check which is so logical now! – Jebble Apr 20 '15 at 13:39
@Jebble yes, you'll only need to set the group once – Kevin Apr 20 '15 at 13:42
What about `$sth->fetchAll(PDO::FETCH_ASSOC|PDO::FETCH_GROUP);`? – cn007b Apr 20 '15 at 13:50
@VladimirKovpak no, it won't group that, `vector_data` is a group of elements, not a group of batches – Kevin Apr 20 '15 at 13:56

score 0 · Answer 2 · answered Apr 20 '15 at 13:31

You can process your array from DB like this:

$pdo = array(
    array(
        "id" => 489,
        "label_value_id" => 4,
        "int_value" => 40,
    ),
    array(
        "id" => 489,
        "label_value_id" => 6,
        "int_value" => 20,
    ),
    array(
        "id" => 489,
        "label_value_id" => 9,
        "int_value" => 10,
    ),
);

$result = array();
foreach ($pdo as $array) {
    if (!isset($result[$array['id']])) {
        $result[$array['id']] = array(
            "id" => $array['id'],
            "vector_data" => array()
        );
    }

    $result[$array['id']]['vector_data'][$array['label_value_id']] = $array['int_value'];
}

score 0 · Answer 3 · answered Apr 20 '15 at 13:48

0

I have the same situation, and i solve it through PDO. Here available examples. I reach same result with code:

$sth->fetchAll(PDO::FETCH_ASSOC|PDO::FETCH_GROUP);

think you should try it...

answered Apr 20 '15 at 13:48

cn007b

16,596
7
59
74

PDO::FETCH_GROUP does not provide the wanted result. – Jebble Apr 22 '15 at 11:33
It's a pity) but maybe need to juggling with fields... because it's very convenient... – cn007b Apr 22 '15 at 11:53
It is indeed, but for what I needed it was not a viable solution. Ghost has already given the solution. – Jebble Apr 22 '15 at 12:35
It's better because it just select already prepared data, and no need to process data through foreach... – cn007b Apr 22 '15 at 12:39

How to group these arrays accordingly?

3 Answers3