List:General Discussion« Previous MessageNext Message »
From:Thimble Smith Date:August 29 1999 9:37pm
Subject:Re: Adding & delete across 2 tables
View as plain text  
At 14:27, 19990829, ma lata wrote:
>CREATE TABLE customer_table (
>customer_id int not null auto_increment,
>customer_name varchar(30) not null,
>customer_address varchar(100) not null,
>customer_phone varchar(10) not null,
>primary key (customer_id),
>);
>
>CREATE TABLE order_table (
>order_id int not null auto_increment,
>customer_id int not null,
>order_item varchar(10) not null,
>order_total int not null,
>primary key (order_id),
>key (customer_id)
>);
>
>when i want to ADD/DELETE a new customer info into my database across
>this 2 tables ,what is the mysql common that i should use??(insert
>into?? delete??) 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??
>
>Please help.Thanks.

ma lata, you should look at the documentation for LAST_INSERT_ID().  That
is the special function in MySQL that will help you to tie your two tables
together when you are inserting.  For example:

    INSERT INTO customer_table (customer_id, customer_name,
                                customer_address, customer_phone)
        VALUES (NULL, 'Tim Smith', '1600 PA Ave.', '5205551212')

    SELECT LAST_INSERT_ID()

Now, store that ID in a variable in your host program.

    INSERT INTO order_table (order_id, customer_id, order_item,
                             order_total)
        VALUES (NULL, $saved_customer_id, '#A98E4%', 675)

When you are deleting, you must issue two deletes.  For example:

    DELETE FROM order_table WHERE customer_id = $saved_customer_id

    DELETE FROM customer_table WHERE customer_id = $saved_customer_id

The MySQL tutorial has a good deal of information on this.  Look at
chapter 8 in the manual (see the bottom of this e-mail for the URL
of the manual).

Tim
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