How can i reformat array this array?

So i have this database table

From that table i want to achievie this (My Expected result)

I don’t know the best way to handle this, so for now i create two query.

My first query

SELECT * FROM ( select convert(created_date,date) created_date,code,'' code2,count(code) total, 0 total2
            from morning_briefing
            where  convert(created_date,date) =  convert('" . $selected_date . "',date) and code IS NOT NULL
            group by code order by created_date desc) a order by total desc

My second query

SELECT * FROM ( select convert(created_date,date) created_date,''code,code2,0 total,count(code2) total2
            from morning_briefing
            where  convert(created_date,date) =  convert('" . $selected_date . "',date)
            and code2 IS NOT NULL
            group by code2 order by created_date desc) a order by total2 desc

Below is result for my query

Array
(
    [0] => stdClass Object
        (
            [created_date] => 2021-03-16
            [code] => AA
            [code2] => 
            [total] => 2
            [total2] => 0
        )

    [1] => stdClass Object
        (
            [created_date] => 2021-03-16
            [code] => AB
            [code2] => 
            [total] => 1
            [total2] => 0
        )

    [2] => stdClass Object
        (
            [created_date] => 2021-03-16
            [code] => BB
            [code2] => 
            [total] => 1
            [total2] => 0
        )

)

then my second query result is this

Array
(
    [0] => stdClass Object
        (
            [created_date] => 2021-03-16
            [code] => 
            [code2] => AB
            [total] => 0
            [total2] => 2
        )

    [1] => stdClass Object
        (
            [created_date] => 2021-03-16
            [code] => 
            [code2] => AA
            [total] => 0
            [total2] => 1
        )

)

So, i want to merge both of the array , then i want to order total column desc, the second order is total2

Btw, i’m using mysql table. So how can i achieve my expected result (above), thanks in advance.

Answer

You can loop through your $results1 and $result2 and create a new array that contains data in your expected result format.

$total = max(count($result1), count($result2));
$formatted = [];

for($i = 0; $i < $total; $i++) {

    $formatted[] = [
        'code' => isset($result1[$i]) ? $result1[$i]->code : '-',
        'total' => isset($result1[$i]) ? $result1[$i]->total : 0,
        'code2' => isset($result2[$i]) ? $result2[$i]->code2: '-',
        'total2' => isset($result2[$i]) ? $result2[$i]->total2 : 0
    ];
}

Advertisement

Answer