Omitting duplicate results from query

[abbeyvet]

What I'm trying to do should be simple, but it is giving me a headache. This is it, simplified from the actual scenario to just leave the part I am struggling with, for clarity:

DB with 2 tables, products and categories from which I want to print a list of categories and their IDs, but only if there are products in the categories.

- categories.catID
- products.productCat (which corresponds to categories.catID)
- categories.catName

I have a query something like this:

$sql = "SELECT * from categories, products WHERE products.productCat=categories.catID";

Then I am printing so:

while ($newArray = mysql_fetch_array($result)){
$catname = $newArray['catName'];
print "<option value=http://www.domain.com/products.php?cat=$newArray[catID]>";
print "$catname";
print "</option>";
}

This suppresses empty categories fine, but it means that the category name is repeated in the list for each product in that category.

How do I either select only unique values of catName, or alternatively print only unique values of catName?

I am thinking it is something to do with array_intersect, but cannot figure out exactly how to use that in this scenario.

Actually I've a feeling I stuck in a warped logic and there is a different and better way of doing what I want that I'm not seeing.

[cameraman]

How about:
SELECT * from categories WHERE EXISTS (SELECT productCat FROM products WHERE productCat=catID)

[henry0]

It looks like WHERE EXISTS has somme limitation for example:
If a row exists and contains nothing but NULL value
then it will be still returned
MANUAL [dev.mysql.com]

Instead you could use a nested query such as:
select category_name
from etc..
do a first num rows $num=....
// first while
$i=0;
while ($i<$num)
{
$cat_name=.....
echo the cat name rows

// note we do not close the first curly bracket
// second query

select etc...
do a second num rows
but name $result $result 2 $num $num2 and $i $i2
(to avoid query confusion)
$i2=0;
while ($i2<$num2)
{
grab and echo the rest of your fields

++$i2;
}// ends $i2 while

++$i;
} // ends $i while

it's up to you to adding the sub query conditions (Where....)

the above will display each category with its related products one section at a time.

<edit>Typo</edit>

[edited by: henry0 at 9:04 pm (utc) on July 19, 2008]

[cameraman]

Ah, excellent observation, so a more comprehensive query would be
SELECT * from categories WHERE EXISTS (SELECT productCat FROM products WHERE productCat=catID AND catName IS NOT NULL AND otherField IS NOT NULL AND...)

[henry0]

Good move :)
but how will you turn it in its "join" form?

[cameraman]

Looks like from the original post that the only intent is to grab category names to populate a select, so a join isn't needed.

I think it can be done, but I'd probably resort to using a method similar to yours (I'd do a 'when the name changes' sort of deal instead of tracking the row counts) to display both category and product data. Although, if I were in a feisty mood, I might just work on that join for a bit! ;)

[abbeyvet]

You are correct cameraman, I am just trying to get cat names into a select.

I figured this out by using the following select, which seems to do the job perfectly:

SELECT distinct products.productCat,categories.catID, categories.catName FROM categories, products WHERE products.productCat=categories.catID ORDER BY products.productCat