php - 获取空数组PHP

我有一个名为“ products”的数据库，其中有一个列“ categories”。该表包含四个类别的产品，即electronic，Decoration，clothes和vehicle。我的目标是显示这些category及其count i e:if there are four products belongs to category electronic, then output should be like this :electronic=4等

我的密码

    public function category()
      {
      $arrayCategorys = ['electronic','Decoration','clothes','vehicle'];
      $data = [];
      foreach($arrayCategorys as $arrayCategory)
       {
      $sql = "SELECT  count(id) FROM products WHERE   categories='$arrayCategory'";
      $records = \DB::select($sql);
      $data = array_merge_recursive($data, [

                  "{$arrayCategory}" =>isset($records[0]->count),

            ]);
      $data=array_filter($data);
      dd($data);
      }
 }

我想要显示这样的输出

'electronic'=>'4',

'Decoration'=>'2',

'clothes'=>'2',

'vehicle'=>'1'根据数据库中的数据
但我什么也没得到，[]

最佳答案

当您GROUP BY时，您可以像这样COUNT您的类别

SELECT categories,COUNT(*)
FROM products
GROUP BY categories;

对于想法：http://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-count-with-group-by.php

编辑：虽然我不熟悉laravel5语法，但这可能对您有用

$result = DB::table('products')
            ->select('products.categories',
                      DB::raw('count(products.id) as category_count')
                    )
            ->orderBy('products.id', 'asc')
            ->groupBy('products.categories')
            ->get();