PHP echo category names in mysql table

Question

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?

Mark Byers · Accepted Answer · 2011-09-25 09:34:38Z

3

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.

answered Sep 25, 2011 at 9:34

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

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

1 Comment

user961644 Over a year ago

MySQL GROUP BY and COUNT is the way to go. See below. Working code: <?php $query = "SELECT category,count(*) AS col_num FROM news GROUP BY category ASC"; $result = mysql_query($query); while($row = mysql_fetch_array($result,MYSQL_ASSOC)) { while(list($key, $value) = each($row)) { if($key!="col_num") $name = $value; else $count[$name]=$value; } } while(list($key, $value) = each($count)) echo "$key ( $value )<br>"; ?>

Anson · Accepted Answer · 2011-09-25 09:12:48Z

2

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:

Select all rows from your database.
Create an associative array where category name is the key and number of appearances is the value.
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.

answered Sep 25, 2011 at 9:12

Anson

2,67423 silver badges18 bronze badges

1 Comment

user961644 Over a year ago

Thanks for the help. Homework indeed, at least I know where to get started now. Cheers mate.

Collectives™ on Stack Overflow

PHP echo category names in mysql table

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related