homepage Welcome to WebmasterWorld Guest from 107.21.186.38
register, login, search, subscribe, help, library, PubCon, announcements, recent posts, open posts,
Pubcon Website
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library : Charter : Moderators: coopster & eelixduppy & jatar k

PHP Server Side Scripting Forum

    
Omitting duplicate results from query
abbeyvet




msg:3702470		 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.

 

cameraman




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

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




msg:3702637		 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
$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




msg:3702644		 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...)
henry0




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

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




msg:3702679		 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! ;)

abbeyvet




msg:3702929		 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.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
WebmasterWorld ® and PubCon ® are a Registered Trademarks of Pubcon Inc.
© Pubcon Inc. 1996-2012 all rights reserved