Convert PHP array to new data-structure

Question

I have a MYSQL query which returns the following PHP array $result:

[0] => Array (
    [resourceKey] => cloth
    [date] => 2020-09-06
    [quantity] => 8
    )
[1] => Array (
    [resourceKey] => ebony
    [date] => 2020-09-06
    [quantity] => 4
    )
[2] => Array (
    [resourceKey] => gems
    [date] => 2020-09-06
    [quantity] => 0
    )
[3] => Array (
    [resourceKey] => lead
    [date] => 2020-09-06
    [quantity] => 0
    )
[4] => Array (
    [resourceKey] => limestone
    [date] => 2020-09-06
    [quantity] => 8
    )
[5] => Array (
    [resourceKey] => cloth
    [date] => 2020-09-05
    [quantity] => 6
    )
[6] => Array (
    [resourceKey] => ebony
    [date] => 2020-09-05
    [quantity] => 3
    )
[7] => Array (
    [resourceKey] => gems
    [date] => 2020-09-05
    [quantity] => 0
    )
[8] => Array (
    [resourceKey] => lead
    [date] => 2020-09-05
    [quantity] => 0
    )
[9] => Array (
    [resourceKey] => limestone
    [date] => 2020-09-05
    [quantity] => 6
    )

And I need to convert it is a certain way to display it with jquery Datatables:

$data= array(
    array("Date" => "2020-09-05","cloth"=>"6","ebony"=>"3","gems"=>"0","lead"=>"0","limestone"=>"6"),
    array("Date" => "2020-09-06","cloth"=>"8","ebony"=>"4","gems"=>"0","lead"=>"0","limestone"=>"8")
);

I've been trying for a while now but can't get a good way to iterate through the results...

I have already taken care of building the columns to pass to Datatables:

$columns = array();
$columns[] = array('data' => 'Date', 'title' => 'Date');
foreach ($result as $key => $row_data) {
    $newResourceKey = $myUtilities->in_array_recursive($row_data['resourceKey'], $columns);
        
    if ($newResourceKey === FALSE) {    // Only if column name not yet in array
        $columns[] = array('data' => $row_data['resourceKey'], 'title' => $row_data['resourceKey']);
    }
        
}

This is an example of the many things I've tried:

$isDateInArray = $myUtilities->in_array_recursive($row_data['date'], $data);
if ($isDateInArray === FALSE) { // Only if Date is not in array yet
    array_push($data, array('Date' => $row_data['date'], $row_data['resourceKey'] => $row_data['quantity']));   // Push date and data
}
else {
    array_push($data, array($row_data['resourceKey'] => $row_data['quantity']));    // Only push data. Can't find a way to add it to existing Date array
}

Update:

public function in_array_recursive($needle, $haystack, $strict = false) {
    foreach ($haystack as $item) {
        if (($strict ? $item === $needle : $item == $needle) || (is_array($item) && $this->in_array_recursive($needle, $item, $strict))) {
            return true;
        }
    }

    return false;
}

Php to json is not my problem. It is the way the PHP array is structured, it needs to be organized in a different way. All the resourceKeys for a specific date need to be in the same entry — remyremy
– remyremy, Commented Sep 6, 2020 at 19:46
What's in $myUtilities->in_array_recursive Method? Sounds like you just need to loop the data & build a new data-structure with it, then save — admcfajn
– admcfajn, Commented Sep 6, 2020 at 19:58
You are right @admcfajn I need to build a new data-structure (similar to my example $data) but I don't manage to do that. Any hint? — remyremy
– remyremy, Commented Sep 6, 2020 at 20:02

Slava Rozhnev · Accepted Answer · 2020-09-06 21:13:25Z

1

You can get the desired result by next simple array transformation:

$res = [];
foreach ($result as $row) {
    $res[$row['date']][$row['resourceKey']] = $row['quantity'];
}

$final = [];

foreach ($res as $d=>$r) {
    array_push($final, array_merge(['date'=>$d], $r));
}
print_r($final);

Here you can try the PHP code

answered Sep 6, 2020 at 21:13

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

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

1 Comment

remyremy Over a year ago

Perfect thanks! The array_merge did the trick and your code produces the expected result

Collectives™ on Stack Overflow

Convert PHP array to new data-structure

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related