Total Number of Manufacturers

[azimpact]

I'm trying to give a list of the number of manufacturers for a specific category, so that above the product listing, it would say:

 

We have (this Many) Manufacturers in this category.

 

I've been able to display the total number of all manufacturers but can't seem to narrow it down to a category.

 

Here is the code I wrote that worked displaying all:

 ?////counting providers for each category
?	$query = "SELECT count(manufacturers_id) AS quantity_count FROM manufacturers "; 
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_assoc($result); 
echo "We have ".$row['quantity_count']." manufacturers in this Category.";

 

I've tried all types of INNER JOINS and JOINS and can not seem to get it to work.

 

Any help forming the query would be greatly appreciated!

[azimpact]

I'm trying to give a list of the number of manufacturers for a specific category, so that above the product listing, it would say:

 

We have (this Many) Manufacturers in this category.

 

I've been able to display the total number of all manufacturers but can't seem to narrow it down to a category.

 

Here is the code I wrote that worked displaying all:

 ?////counting providers for each category
?	$query = "SELECT count(manufacturers_id) AS quantity_count FROM manufacturers "; 
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_assoc($result); 
echo "We have ".$row['quantity_count']." manufacturers in this Category.";

 

I've tried all types of INNER JOINS and JOINS and can not seem to get it to work.

 

Any help forming the query would be greatly appreciated!

<{POST_SNAPBACK}>

 

Anyone? :(

[Guest]

you need to combine the manufacturers, categories and products. So your query is more likely:

 

        $mfg_query= "select count(*) as total from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_MANUFACTURERS . " m where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and p.products_id = p2c.products_id and p2c.categories_id = '" . (int)$current_category_id . "' order by m.manufacturers_name";

 

key here is the $current_category_id because I assume you'll use it with a categories listing

[azimpact]

you need to combine the manufacturers, categories and products. So your query is more likely:

 

 ? ? ? ?$mfg_query= "select count(*) as total from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_MANUFACTURERS . " m where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and p.products_id = p2c.products_id and p2c.categories_id = '" . (int)$current_category_id . "' order by m.manufacturers_name";

 

key here is the $current_category_id because I assume you'll use it with a categories listing

<{POST_SNAPBACK}>

 

Engima1,

 

Thank you for the push in the right direction. The code counts all the titles in the category and gives a total count of those titles.

 

I'm trying to get a total count of manufacturers in that specific category, so if there are say 10 products, but only 2 manufacturers, it will say there are 2 two total manufacturers.

 

I'll play around with the query you provided and see what I can come up with.

 

Thank you again for replying!!

[Guest]

just change the count(*) to distinct m.* (whatever) should get the manufacturers sql array