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
