List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:August 29 1999 10:11pm
Subject:Re: Adding & delete across 2 tables
View as plain text  
On Sun, 1999-08-29 14:27:14 -0700, ma lata wrote:
> CREATE TABLE customer_table (
[...]
> CREATE TABLE order_table (
[...]
> i can't found any documentation that indicated how to use INSERT
> INTO / DELETE across the 2 tables on the web.  (Note: the html form
> required the customer to enter CUSTOMER_NAME, CUSTOMER_ADDRESS,
> CUSTOMER_PHONE, ORDER_ITEM, ORDER_TOTAL) .
> Is this the above database structure ok enough to implement this
> task??

Yes, I think the db scheme is fine.

You don't need to do everything in a single SQL query!
Just do two seperate INSERTs, one for each table.

Of course you only should insert a new customer into your database,
if she's not already stored.
For a new inserted customer you have to retrieve the automatically
created customer_id, so you can do the second INSERT for the order.

Alltogether this looks like this:

  If new customer,
     then we have to store him first:
        INSERT INTO customer_table
               VALUES (NULL, 'Martin Ramsch', 'my address','my phone');
     Note: the newly created customer_id now can be accessed
     with LAST_INSERT_ID().  So let's get this number:
        SELECT LAST_INSERT)ID();

  If old customer,
     then we already know his customer_id
     (looked up in the db, or input by user), e.g. 42.

  And now insert the order:
        INSERT INTO order_table
               VALUES (NULL, 42, 'coffee mug', 1);

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
Adding & delete across 2 tablesma lata29 Aug
  • Re: Adding & delete across 2 tablesThimble Smith29 Aug
    • Re: Adding & delete across 2 tablesJames Manning30 Aug
      • Re: Adding & delete across 2 tablesThimble Smith30 Aug
  • Re: Adding & delete across 2 tablesMartin Ramsch30 Aug
  • Re: bdb table data deletedSinisa Milivojevic1 Feb