CODEWITHSUNDEEP
phpmysqlechocategories
user961644
user961644

Reputation: 75

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. Can anybody point me in the right direction?

Upvotes: 1

Views: 308

Answers (2)

Mark Byers
Mark Byers

Reputation: 838106

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.

Upvotes: 3

Anson
Anson

Reputation: 2674

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.

Upvotes: 2

Related Questions