PHP MySql 4 table query

[smilesinblues]

Hi,
I have these 3 tables in my DB.

1. tbl.Member

a. name
b. email
c. username(pri key) ...and other stuff

2. tbl.Product

a. id (pri key)
b. product name
c. category .... and other stuff
d. company(from table maker)

3. tbl.Maker

a. id (pri key)
b. company
c. email and other stuff

When the members are browing products they choose and purchase most of
the products(atleas t they apply). These orders will be stored in the
4th table.

4. orders

a. id
b. product name
c. maker
d. username (from tbl.members)... .and other stuff.

The question is when a member with username jaunty is browsing the
products I want to show him an indication that he has already purchased
item no 5,9,15 etc.

assuming that I use the following query on the browse product page..

$query = select * from products;

how should I modify $query so that I can also indicate that jaunty has
purchased some of them.

I am very sure this should be a small trick I just want to make sure my
DB structure and queries are proper.

Please help me
Thanks

[Rich Ryan]

SELECT
ProductName
,(SELECT CASE
WHEN ProductName IN
(SELECT
ProductName
FROM
ORDERS
WHERE
Name = 'Jaunty'
)
Then 'Yes'
Else 'No'
END
) AS 'Order History'
FROM
PRODUCT

A few suggestions:

- with your design a product can belong to only one category. You might want
to create another table called CATEGORIES and
an intesection table called PRODUCT_CATEGOR IES to handle a m:m
- don't use "tbl" as a prefix for table names
- it is typical to break out your ORDER table into two table 1)ORDERS
2)ORDER_DETAILS .
<sm***********@ gmail.com> wrote in message
news:11******** *************@z 14g2000cwz.goog legroups.com...

Hi,
I have these 3 tables in my DB.

1. tbl.Member

a. name
b. email
c. username(pri key) ...and other stuff

2. tbl.Product

a. id (pri key)
b. product name
c. category .... and other stuff
d. company(from table maker)

3. tbl.Maker

a. id (pri key)
b. company
c. email and other stuff

When the members are browing products they choose and purchase most of
the products(atleas t they apply). These orders will be stored in the
4th table.

4. orders

a. id
b. product name
c. maker
d. username (from tbl.members)... .and other stuff.

The question is when a member with username jaunty is browsing the
products I want to show him an indication that he has already purchased
item no 5,9,15 etc.

assuming that I use the following query on the browse product page..

$query = select * from products;

how should I modify $query so that I can also indicate that jaunty has
purchased some of them.

I am very sure this should be a small trick I just want to make sure my
DB structure and queries are proper.

Please help me
Thanks