Topic: 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