Extracting one of each in a field

not all of the field with duplicates.

[undream2]

Hi,

I knew I would have this problem. When it came down to it, but I can't get it figured out.

I have a database that is full of records. And, they all have a field called category. Where the category field has the same information for some of the rows.

I need to display only one category each. And, not have the database return all the rows for the category field.

e.g.
things category
row one
row two
row one
row one
row four
row five
row two

I want to only return a list: (one of each)

one
two
four
five

Any help would be appreciated. Thanks..

[undream2]

Alos, I forgot. I also would like to have a way for the field to return just blank, if nothing is in the category yet.

I have a certain html code, and I want to follow it exactly, and have the page update automatically, depending on the changes made..

So, if the database is blank for that spot in a list.. I want it blank.

I figure I would go about this part with .$row[0}. $row[1] in the html after some if statments.

[rocknbil]

Without seeing your database structure, we'll be shooting in the dark. But I am **presuming** your categories are all in the single table. So you want something like

select distinct category from table;

However, the way you **should** do this is have the categories in a relational table. Then you can use an integer field representing the category ID, which equates to faster searching (integers are always faster than searching on text.)

products 
¦id ¦category ¦ title 
1  ¦12    ¦Red 
2  ¦12    ¦Green 
3  ¦12    ¦Blue 
4  ¦22    ¦Red 
5  ¦22    ¦Green 
6  ¦22    ¦Blue 

categories 
¦id ¦cat_id  ¦title 
1  ¦12    ¦Large 
2  ¦22    ¦Medium 
3  ¦32    ¦Small 

There are two distinct advantages to this (among others.) If you want a category list, you never need to touch the products table. This will just return the categories. Second, it allows you to do a join on the two tables, which is a slightly more complex query but will return results for null values. That is, as you request, if the category is "blank" in products, it will still return that row.

select * from products where cat_id=2 left join categories on products.category=categories.cat_id;

The previous query would do pretty much what you're getting now, and is really to exemplify how you'd get all products in a single category.

A last note, you may question why I don't just use the id field in the category table as the join to the products table. If set up correctly, id will be an auto_increment field. Category might GET it's original value from id, but it allows it to change so if you move your database, it will always repopulate correctly and maintain it's relationship to the products table.