How do I insert data into a mysql database using a procedure?

Question

I have a single procedure that has two insert statements in it for two different tables. I must insert data into table1 before I can insert into table2. I'm using PHP to do the data collection. What I'd like to know is how to insert multiple rows into table2, which can have many rows associated with table1. How would I do this?

I want to only store the person in table1 just one time but table2 requires multiple rows. If these insert statements were in separate procedures, I wouldn't have a problem but I just don't know how I would insert more than one row into table2 without table1 rejecting a second duplicate record.

BEGIN

INSERT INTO user(name, address, city) VALUES(Name, Address, City);

INSERT INTO order(order_id, desc) VALUES(OrderNo, Description);

END

Answer 1

I'd suggest you do it separately, otherwise you'd need a complicated solution which is prone to error if something changes.

The complicated solution is:

• join all orderno and descriptions with a separator. (orderno#description)
• join all orders with a different separator. (orderno#description/orderno#description/...)
• pass it to the procedure
• in the procedure, split the string by order separator, then loop through each of them
• for each order, split the string by the first separator, then insert into the appropriate columns

As you can see, this is bad.

Answer 2

I am sorry, but what's stopping you from inserting data into these (seemingly unrelated) tables in separate queries? If you don't like the idea of it failing halfway through, you can wrap it into a transaction. I know, mysqli and pdo can do that just fine.

Answering your question directly, insert's ignore mode turns errors during insertion into warnings, so upon attempting to insert a duplicate row the warning is issued and the row is not inserted, but there is no error.

Answer 3

You could use the IGNORE keyword on the first statement.
http://dev.mysql.com/doc/refman/5.1/en/insert.html:

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error is issued.

But somehow this seems rather inefficient to me, a "stabbed from behind through the chest in the eye"-solution.