Getting null array PHP

Question

i have a database named "products" which has a column "categories". This table contain four category of products namely electronic,Decoration,clothes and vehicle. My target to show these category with their count ie:if there are four products belongs to category electronic, then output should be like this :electronic=4 and so on

My code

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

I want show output like this

'electronic'=>'4',

'Decoration'=>'2',

'clothes'=>'2',

'vehicle'=>'1' according to data in database but iam getting nothing ,[]

A l w a y s S u n n y · Accepted Answer · 2015-05-31 08:15:39Z

1

You can GROUP BY your categories like this way when you COUNT

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

For Idea: http://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-count-with-group-by.php

EDIT: Though i am not familiar with laravel5 syntax but this may work for you

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

edited May 31, 2015 at 8:15

answered May 31, 2015 at 5:23

A l w a y s S u n n y

38.8k9 gold badges68 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ranjeet Karki Over a year ago

how can i use query builder for this in laravel5

A l w a y s S u n n y Over a year ago

@RanjitKarki i have updated my answer. let me know it works for you or not?

Karl Casas · Accepted Answer · 2015-05-31 06:02:59Z

You used isset($records[0]->count) but the column name for the count will be count(id). Name the count as count like this "SELECT count(id) AS count FROM products WHERE categories='$arrayCategory'". And you wont be able to get the count just by checking if it is set. Remove the isset and just use $records[0]->count. The code should look like:

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

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

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

Collectives™ on Stack Overflow

Getting null array PHP

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related