homepage Welcome to WebmasterWorld Guest from 54.167.10.244
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
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.
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