A MySQL/PHP Question

Hey Guys,

Im using MySQL, and what I want to do is show only the first occurence records with distinct id values. Let me give an example, if I had the following table,

>create Table products_categories (
> product_id int ....,
> category_id int ...,
> )

I have two other tables, "products" and "categories" and since a single
product can belong to multiple categories, I get a side effect when
displaying all products, in that a single product can be displayed multiple
times (according to how many different categories it belongs to). For
example, if product "Apple-Orange juice", which has a product_id 1, belongs
to Apples category(category_id=3) and Oranges category(category_id=5), this
will result in two records in the products_categories table, looking like

"1,3" and "1,5"

Now when I do a select that needs to gather category, products and
products_categories fields, I get multiple records with the same
product_id, so I end up displaying the same product multiple times, when
instead I only want to display it once. So thus I want to get only one
occurence each of each distinct product_id. Now, Im solving this problem
right now via a php script, but was wondering if there was an SQL(or MySQL)
solution to this problem?

Thanks everyone,

Christian

Can you put all of this into one table? I think it would make things easier. If you had a table like this:

create table products_categories (product text, prod_id int, cat_id int);

You would have values like this:
Apple-Orange Juice,1,3
Apple-Orange Juice,1,5
Apple Juice,2,3
Orange Juice,3,5

Then if you wanted to get all of the products without duplicates that matched a certain category or product id, then you would use:

SELECT DISTINCT product FROM products_categories WHERE prod_id='1';

or

SELECT DISTINCT product FROM products_categories WHERE cat_id='5';

or, for PHP,

SELECT DISTINCT product FROM products_categories WHERE prod_id='$your_var';

Hope this helps at least a little, if not, it's my $0.02..

---John Holmes
---www.SepodatiCreations.com