2
votes

I am trying to insert multiple rows into two tables connected by a foreign key that is autoincrement. I can't seem to find a good solution. Tables:

  • eav_attribute_option

    • option_id (PK, Autoincrement)
    • attribute_id
    • sort_order
  • eav_attribute_option_value

    • value_id (PK, Autoincrement)
    • option_id (FK)
    • store_id
    • value

I want to do this:

insert into eav_attribute_option(attribute_id) values(100,101,102,103,...);
insert into eav_attribute_option_value(option_id,store_id,value) values 
    (1,0,"English"),(1,1,"German"),(2,0,"English1"),(2,1,"German2")

What would be the best approach to this, I can't seem to find a good one. :

  • Get next autoincrement then insert with it (need to lock table between)
  • Insert first part, then retreive PK values, build second part and insert (data incomplete for some time, what happens on error in second part?)
  • Some way to insert with join if it's possible?

Edit: Just to clarify, I am looking to use the least amount of queries possible. I know I can do last inserted id, but I don't want to kill the server with thousands of inserts.

2
AFAIK it's not possible to retrieve AUTO_INCREMENT generated values for multi inserts.Vatev

2 Answers

0
votes

You can try something like this:

insert into eav_attribute_option (attribute_id) values(100);

insert into eav_attribute_option_value (option_id, store_id, value)
values (LAST_INSERT_ID(), 0, "English");

But you will need to insert the rows one by one. Consider doing a loop in your application.

0
votes
$count = count('Count post value'); // $_POST value count

for($a=0;$a<$count;$a++)
{
    $insert = 'insert into eav_attribute_option(attribute_id,sort_order) values (value1,value2)';
    mysql_query($insert);
    $insert_id = mysql_insert_id();

    $insert2 = 'insert into eav_attribute_option_value(option_id,store_id,value) values 
                ($insert_id,0,"English")';
    mysql_query($insert2);
}