Category Counting 1

[shop73]

How with Dreamweaver make category counting like

Digital camera (5)
Computers (3)
Cars (0)
....


I have two tables, categories and products

categories: categoryID, categoryName
products: prodID, prodName, categoryID

 

[ChrisHirst]

and the server side code is?

and the database server is?



Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
People Counting Systems

So long, and thanks for all the fish.

 

[shop73]

PHP and MySQL.

 

[ChrisHirst]

to clarify

you want to display the categories and the product count for that category, is that correct?




Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
People Counting Systems

So long, and thanks for all the fish.

 

[shop73]

That is correct.

Carduel.

 

[ChrisHirst]

$result = mysql_query("select C.categoryname, count(P.prodname) as prodcount from categories as C join products as P on p.categoryid = C.categoryid group by C.categoryname");

while ($rows = mysql_fetch_assoc($result)) {
echo $rows("categoryname);
echo "(";
echo $rows("prodcount");
echo ")";
echo "<br>";
echo "\n";
}
mysql_free_result($result)

looks about right,
NOTE no connection and NO error checking done.


Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
People Counting Systems

So long, and thanks for all the fish.

 

[shop73]

How to show category with zero products?

cars (0)

 

[ChrisHirst]

make it into a LEFT JOIN

Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
People Counting Systems

So long, and thanks for all the fish.

 

[shop73]

Thank You very much!

This works.

Carduel

 

[shop73]

Chris,

in table products I have prodShow (enum, Y, N, Required). How to add this item in sql query to show all categories, product counting for products with sign 'Y' in prodShow field?

 

[ChrisHirst]

... WHERE P.prodShow = 'Y' group by C.categoryname");

Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
People Counting Systems

So long, and thanks for all the fish.

 

[shop73]

Thanks Chris!