homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

Omitting duplicate results from query

 3:38 pm on Jul 19, 2008 (gmt 0)

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.



 5:22 pm on Jul 19, 2008 (gmt 0)

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


 8:42 pm on Jul 19, 2008 (gmt 0)

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
while ($i<$num)
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)
while ($i2<$num2)
grab and echo the rest of your fields

}// ends $i2 while

} // 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.


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


 8:58 pm on Jul 19, 2008 (gmt 0)

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...)


 9:03 pm on Jul 19, 2008 (gmt 0)

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


 10:04 pm on Jul 19, 2008 (gmt 0)

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! ;)


 1:55 pm on Jul 20, 2008 (gmt 0)

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

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved