• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6032
  • Last Modified:

MySQL Select Distinct

Hi

I want to access a list of database items without duplication of one particular column.

Usually I would have "select * from products where section='$section'"

Now I have "select distinct sub_cat from products where section='$section'"...

But I want access to the other columns (e.g."select distinct sub_cat, * from products...." - but this doesn't work)

How can I do this so I can use the following..

while(list($num, $sub_cat, $image... etc)=mysql_fetch_row($res)){
   print "<img src='images/user_thumbs/".$num.".JPG' align='left'>$p_subcat";
}

Thanks
0
milkmon123
Asked:
milkmon123
1 Solution
 
Ryan ChongCommented:
you can try using "Group By", like:

Select field1, field2 from table1 group by field1, field2
0
 
geotigerCommented:

If sub_cat is not one-to-one related to $num and $image and you want to have one sub cat per img tag, then you would have to do something like this:

my $sql = "select sub_cat, max( num) as num, max(image) as image
from products
where section='$section'
group by sub_cat";


0
 
capileCommented:

Usualy using distinct in your query should result in distinct rows that match your query, not distinct values for a specific column -- so if you have the same "sub_cat", but different products on each subcategory, distinct will only work if you ommit the other values that change.

Another workaround can be made with the scripting -- you could set all "sub_cat" that were found on a variable and check if a a value was printed before printing it -- from your code:

    $existing_sub_cat = array();
    while(list($num, $sub_cat, $image... etc)=mysql_fetch_row($res)){
        if (!in_array($sub_cat, $existing_sub_cat)) {
            $existing_sub_cat[] = $sub_cat;
            print "<img src='images/user_thumbs/".$num.".JPG' align='left'>$p_subcat";
        }
    }

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now