I was trying to create some search filter in PHP and need to show some categories from database as a checklist option. Here is an example.

Result I am expecting is

100 Cats - 2
102 Dogs - 2
103 Tiger - 1
104 Fox - 3
105 Lion - 1
108 Snake - 1

I tried the following, but coudlt achive the result, what should I do ?

 $query = "SELECT animalcode, COUNT(*) as num_items FROM dbposts GROUP BY animal";

              $statement = $connect->prepare($query);
              $statement->execute();
              $result = $statement->fetchAll();
              foreach($result as $row)
               { 
                 echo $row['animalcode']; 
                 echo $row['animal'];
                 echo " - ";
                 echo $row['num_items'];
                 echo "<br>";
               }

The above code gives me the following result without animal names.

100 - 2
102 - 2
103 - 1
104 - 3
105 - 1
108 - 1

Here is the SQL structure

CREATE TABLE IF NOT EXISTS `dbposts` (
  `id` int(6) unsigned NOT NULL,
  `animalcode` varchar(200) NOT NULL,
  `animals` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `dbposts` (`id`, `animalcode`, `animals`) VALUES
  ('1', '100', 'Cats'),
  ('2', '101', 'Dogs'),
  ('3', '100', 'Cats'),
  ('4', '103', 'Tiger'),
  ('5', '104', 'Fox'),
  ('6', '105', 'Lion'),
  ('7', '101', 'Dogs'),
  ('8', '104', 'Fox'),
  ('9', '108', 'Snake'),
  ('10', '104', 'Fox');

CodePudding user response：

You need to select animals from dbposts.

$query = "SELECT animalcode, animals, COUNT(*) as num_items 
          FROM dbposts 
          GROUP BY animalcode, animals";