Help with a query

[KingSpongo]

Hi everyone,

I have made two tables (Product and Customer). They look like:

 

 

What I want to do is set Customer_ID in the Product table to a foreign key but have no clue how to do that.

I also want to make a query to show in a web page a table that looks like:

 

 

If the product doesnt have a customer am i able to click on the empty customer name box, input a name and add it to the mysql database?

Everytime i refresh the page i want to see every product and if a product has a customer. I don't really want somone to do this all for me but maybe help me understand how to do it. I look forward to every reply.

 

Many thanks. 

 

[jskywalker]

1st of all, you do use InnoDB to store your tables?

Otherwhise it makes no sence creating foreign keys

(http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-foreign-keys.html)

 

ALTER TABLE `product`

ADD CONSTRAINT FOREIGN KEY (`Customer_ID`)

REFERENCES (`Customer`.`Customer_ID`

(http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html)

 

but i'm no expert in foreign-keys, because i dont use InnoDB, and ...... 

[fenway]

Where's the many-to-many table?

[zeodragonzord]

I suggest you create a new table that links both those tables together.  That way, you have only product information in a product table, only customer information in a customer table, and another table to determine who has what.  This also will provide the many-to-many relationship you will want. For example, it's possible that a customer may have more than one product.  Similarly, a product can be purchased by many customers.

 

[product]

product_id

product_name

product_description

 

[customer]

customer_id

firstname

lastname

email

 

[customer_product]

customer_product_id

customer_id

product_id