Inserting with foreign keys

Hi I have 3 tables

Products

  • product_id
  • product

Customer

  • cust_id
  • name

Orders

  • order_id
  • cust_id
  • product_id

My Orders table is currently empty and I want to insert some data. I want to use 2 listboxes so that I can select the records available. I have inserted into the Orders table by just selecting ID from 2 list boxes Customer ID and Product ID. And placed the results into variable and insert into the Orders table which worked fine. However I would like for the list box to display the Names/Product instead of ID to make it clearer who and what product I’m selecting but when I select an item I want the id to be selected for the insert statement not the text. So the text for display and the ID for the insert/

So for example in customer listbox I select ‘Shawn’ then in product listbox I selected ’ Hat’ I need the id for these to be placed in the orders table. My problem is I don’t know how to join them to do this.

If you just want to know how to set up the database query, your thread will probably be better off in the databases forum, so I’m going to move it there to see what kind of responses you get.

1 Like

this isn’t really a database question

you will use 2 queries to retrieve the data to display –

SELECT product_id
     , product
  FROM Products
[/code]  [code]
SELECT cust_id
     , name
  FROM Customer

after that, you will have to write the correct HTML and PHP (or whatever you’re using) to pull the id values for the insert

INSERT INTO Orders ( cust_id , product_id ) VALUES ( $c_id , $P_id )

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.