PHP echo category names in mysql table

I have a table in my mysql database that looks like the following:

    id         |   category  |     title   
     1         |     blue    |     title1
     2         |    green    |     title2
     3         |     red     |     title3
     4         |    purple   |     title4
     5         |     red     |     title5
     1         |     blue    |     title1
     2         |    green    |     title2
     3         |     blue    |     title3
     4         |     blue    |     title4
     5         |     red     |     title5

.....

I want to echo the category names on my page and then count how many items there are in each category.

Goal is something like this:

blue(4)
green(2)
red(3)
purple(1)

I have tried echoing the categories but I just receive a long list of duplicates. C开发者_开发问答an anybody point me in the right direction?

No need to mess with associative arrays. You can easily do this in SQL using GROUP BY and COUNT. See the MySQL manual for an example.

I'm not going to give you code, since you should be trying to write that yourself and since this sounds like homework.

Here's a basic idea of what your PHP script can look like:

1. Select all rows from your database.
2. Create an associative array where category name is the key and number of appearances is the value.
3. Iterate over the table data you got back from your query, and for each category, increment the respective count in your array.

Good luck and feel free to ask another question once you've got some code written.