循环数组和if键值存在时为结构化数组添加1

I have a MySQL result that looks as follows in json

[
   {
      industry: "Building",
      province: "Gauteng",
      status: "review"
   },
   {
      industry: "Building",
      province: "Gauteng",
      status: "sent"
   },
   {
      industry: "Air Conditioning",
      province: "Cape Town",
      status: "free"
   }
]

I and trying to take this result and generate an array that looks as follows

{
   Gauteng: {
      Building: {
         sold: 2,
         free: 0
      },
   },
   Cape Town: {
      Air Conditioning: {
          sold: 0,
          free: 1
      }
   }
}

Items are grouped by province and industry and the last level of the array needs to contain 2 keys "sold" & "free". Sold is classified as anything with sent or review in it and free is anything with a status of free.

My problem is I can make the array to the point of Province > industry by not sure how to check and add up all the values for sold and free.

This is my current code in using.

$query = DB::select("SELECT industry, province, status FROM messages WHERE MONTH(updated_at) = MONTH(CURDATE())");

$result = array();

    foreach($query as $item) {
      $result[$item->province][$item->industry] = array(
        "sold" => 12,
        "free" => 23
      );
    }

    echo json_encode($result);

which gives me the result above.

Any help is greatly appreciated thanks in advance.

With your existing MySQL query I believe this achieved what you wanted:

foreach($query as $item) {
        if(empty($result[$item->province][$item->industry])){
            $result[$item->province][$item->industry] = array(
                "sold" => 0,
                "free" => 0
            );
        }
        switch($item->status){
            case('free'):
                $result[$item->province][$item->industry]['free']++;
                break;
            case('sent' || 'review'):
                $result[$item->province][$item->industry]['sold']++;
                break;
        }
    }

If that specific province, industry combination doesn't exist yet, create an array item with sold and free set to 0.

And then depending on the item status +1 to the necessary key.

Pretty self explanatory.