phparraysmultidimensional-arraypivotgrouping

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 }

Solution

  • 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);