arrays or queries?

[Anon]

i have a table named ISSUES and another table named PRODUCTS in a mysql database.

ISSUES shares the PRODUCT_ID field with PRODUCTS. PRODUCTS has a field named PRODUCT_NAME.

Each product has a unique PRODUCT_ID, but the PRODUCT_NAME field is not unique because multiple people can have the same model.

i am trying to make a query something like this:

SELECT COUNT(ISSUE_ID) for each PRODUCT_NAME. .. but i can't figure it out. i would have posted in database section, but i think i have to use arrays too.

This would be the end result, MODEL_A is the PRODUCT_NAME

MODEL_A has 50 issues.
MODEL_C has 34 issues.
MODEL_B has 21 issues.

can anyone help me?

[Anon]

you could try doing something like this...

<?php

$result = mysql_query("SELECT Count(Issue_ID) AS IssueCount, Issue_ID FROM Issues;");
$r = mysql_fetch_array($result);

$count = $r[IssueCount];

for($i = 0; $i < $count; $i++) {
$Product = mysql_query("SELECT PRODUCT_NAME FROM PRODUCTS WHERE Product_name = $r[Issue_ID];");

$p = mysql_fetch_array($product);
$prod = $p[Product_Name];
echo $prod;
}

?>

That code is completly off the top of my head as I am very tired 🙂 Hope it works for you and if it doesn't, Im sorry it didn't help 😉

Regards, Stezz.

[tomhath]

Just join on PRODUCT_ID and group by PRODUCT_NAME.

select PRODUCT_NAME, count(*) as cnt
from ISSUES, PRODUCTS
where ISSUES.PRODUCT_ID = PRODUCTS.PRODUCT_ID
group by PRODUCT_NAME