MySQL / PHP：从Database中的1列中获取多个值

Question

I'm trying to select multiple values from 1 column in my MySQL database. I have a Table 'products' with a column 'category'. Categories: Home, Garden, Cars, Bicycle etc. I want to fetch the number of products with these categories for statistics. It sounds simple but I can only get it done with allot of code. I want all of these categories to be variables so I only have to put my variables in my statistics engine to do the calculation. Right now this is my code to fetch the number of products with category 'Garden':

$query = "SELECT * FROM products WHERE category='Garden'";
$result= mysql_query($query);
$row   = mysql_fetch_array($result);

echo "$row[category]";

Repeating this for every category does 't seem right to me.. Does anyone understand my question and have a solution?

Answer 1

try this

   $query = "SELECT *, count(*) as counts FROM products group by category";
   $result= mysql_query($query);
   while($row   = mysql_fetch_array($result))
   {
   echo $row['category']." ". $row['counts'];

   }

you have to use while to fetch multiple categories.

you need to group by category to get distinct categories.