PHP MySql 4 table query

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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(atleast 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

Re: PHP MySql 4 table query

      WHEN ProductName IN
        Name = 'Jaunty'
    Then 'Yes'
    Else 'No'
  ) AS 'Order History'

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_CATEGORIES 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
Quoted text here. Click to load it

Site Timeline