CODEWITHSUNDEEP
phpmysqldatatables
remyremy
remyremy

Reputation: 3758

Convert PHP array to new data-structure

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

visual table

[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;
}

Upvotes: 0

Views: 86

Answers (1)

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

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

Upvotes: 1

Related Questions