CODEWITHSUNDEEP
phparraysmultidimensional-arraypivotgrouping
Shiromi
Shiromi

Reputation: 157

Pivot database query result by date to create a date-grouped 2d array

This is my code in php.

$approveddata = "SELECT DATE AS DATE, company_id, count As Approved
                 FROM tbl_summary_order
                 WHERE DATE >= DATE( NOW( ) ) - INTERVAL 7 DAY
                     AND STATUS = 4
                 GROUP BY company_id, DATE( DATE ) 
                 ORDER BY DATE ASC "

while ($_row = $rsqueryapproveddata->read()) {
    $list1[$_row['DATE']][$_row['company_id']] = $_row['Approved'];
}

I am getting the output as

 "data": {
    "2018-01-30": { "1": "10", "2": "5" },
    "2018-01-31": { "1": "10" }
}

But I need a format to include the date value inside every row.

Something like the below format

data: [{ date: '2018-01-30', 1: 10, 2: 5, }, { date: '2018-01-31', 1: 10 }

Upvotes: 1

Views: 56

Answers (2)

mickmackusa
mickmackusa

Reputation: 47863

To group by dates and continuously extend the associative pairs in each group without leveraging first-level keys to be removed later, you can push reference variables into the result array and merely modify the references as needed.

Code: (Demo)

$result = [];
while ($_row = $rsqueryapproveddata->read()) {
    if (!isset($ref[$_row['DATE']])) {
        $ref[$_row['DATE']] = ['date' => $_row['DATE']];
        $result[] =& $ref[$_row['DATE']];
    }
    $ref[$_row['DATE']][$_row['company_id']] = $_row['Approved'];
}
var_export($result);

Output:

array (
  0 => 
  array (
    'date' => '2018-01-30',
    1 => '10',
    2 => '5',
  ),
  1 => 
  array (
    'date' => '2018-01-31',
    1 => '10',
  ),
)

Upvotes: 0

YouneL
YouneL

Reputation: 8351

You could initialize $list1 with an associative array that contains the current date value, and then when the loop is finished use array_values function to get the result as a numeric array:

while ($_row = $rsqueryapproveddata->read()) {

    if ( !isset( $list1[ $_row['DATE'] ] ) {
        $list1[ $_row['DATE'] ] = [ 'date' => $_row['DATE'] ]
    }

    $list1[ $_row['DATE'] ] [ $_row['company_id'] ] = $_row['Approved'];  

}

$result = array_values($list1);

Upvotes: 1

Related Questions