Foreach Loop over INSERT INTO statement

woodburndesigns

Hey Guys,

I am currently trying to insert data into two tables. The first table is a basic table containing registration information. The second table is a many-to-many table containing the ID from the first table and workable_area_ID provided by checkboxes from a form. Workable_area_ID gets posted as an array since there are multiple selections. I then need to take that array and place that info into the second table. I thought a foreach() loop would work best but I am getting all sorts of errors.

Can someone please help me out?

The code is as follows:

foreach($workable_areas as $area_ID){
   $insert_nanny_area .= "INSERT INTO nanny_areas(nanny_ID, workable_area_ID)VALUES(LAST_INSERT_ID(),{$area_ID})";
}

laserlight

Instead of appending to $insert_nanny_area on each iteration, execute the statement. If possible, it is usually a good idea to begin a transaction before the loop, then commit after the loop.

YAOMK

your code should be defined as follows:

foreach($workable_areas as $key => $area_ID){
   $insert_nanny_area = "INSERT INTO nanny_areas(nanny_ID, workable_area_ID)VALUES(LAST_INSERT_ID(), '$area_ID')";
// run query ....
}

But since you are getting the last inserted id, running that query consecutively will present a logic problem as you won't be getting the last inserted id for the corresponding record in your other table, unless all the associations in this table correspond to a single record in your registration table.

EDIT: Heh my post got to you a tad too late, but yeah as laserlight said you could benefit from implementing transactions.

woodburndesigns

Thank you for your quick response!

Is there a better way to accomplish what I am trying to do? I need the ID that is created from inserting the registration but since both of the tables are created from the same form I don't know how to accomplish that.

Do you have any suggestions?

YAOMK

Here is a tutorial on how to implement transactions
Then you could:

BEGIN //the transaction...
//insert registration info...
//run loop for your many to many relationship table.
END //the transaction.

woodburndesigns

Great! Thank you. I'll do that tutorial right now.

YAOMK

Also remember that your tables need to use the innodb engine in order to support transactions. Good luck.

bpat1434

If you can't do transactions (or don't want to) then remember that mysql_query() can only call one query at a time. So a query like this:

INSERT INTO `table` VALUES (1,2);
INSERT INTO `table` VALUES (3,4);
INSERT INTO `table` VALUES (5,6);

won't execute the last two since it will be truncated to the first real query. To run multiple queries you'd have to execute each individual query on the loop, or use a "combined" format where you do something like:

INSERT INTO `table` VALUES (1,2), (3,4), (5,6);