sharing information between tables

[mindapolis]

Hi, I have an order table that contains data like product ordered, quantity, price, shipping  information.  I have a customer tables that has name, address etc.  The primary keys for each table are customer_id and order_number.  What is the  best way to link the customer first and last name to the order table.  Can I put them as foreign keys or should I create a query? 

[Barand]

Can I put them as foreign keys or should I create a query? 

 

Both. The order table needs a column for the customer_id, then you need a query containing a join to get the data from the orders and matching company.

[mindapolis]

So, insert the customer_id field into the order table like any other field, right?

[Barand]

Defined as a foreign key as you mentioned.

[mindapolis]

how do you put a key as a foreign key in phpmyadmin? 

[mindapolis]

I have tried

ALTER TABLE orders 
ADD CONSTRAINT 
FOREIGN KEY (customer_id) REFERENCES customers (customer_id)

 

it says customer_id does not exist but it does. 

[smoseley]

And then when you query it, you can

 

SELECT orders.*, customers.first_name, customers_last_name FROM orders INNER JOIN customers ON orders.customer_id = customers.id

 

to get your customer name related to an order, or

 

SELECT customers.id, COUNT(orders.id) FROM customers LEFT JOIN orders ON customers.id = orders.customer_id

 

to get the number of orders per customer...

 

or lots of other nifty things.

[mindapolis]

but I can't get customer_id to be the foreign key in the orders table. 

[Barand]

If you are using MyISAM tables you'll have to settle for an ordinary key as foreign keys supported by InnoDB.

[mindapolis]

I'm using phpmyadmin. 

[awjudd]

phpmyadmin is a tool to allow you to access your database in a user-friendly way.  MyISAM and InnoDB are storage engines for the tables in a database.

 

~awjudd