looping mysql query to get multiple rows of data from a table and insert into another table

Question

I am working on a small project and is having some issues with looping MySQL insert. I currently have 2 database tables. I am fetching information from one to another.

table with data:

$q = "SELECT * FROM HARDWARE WHERE ID_2=".$db->qstr(20);
$rss = $db->execute($q);
$re2=$rss->GetArray();

so I am getting the array of data fine.

inserting the data only if the id is {20} Currently I have 2 rows with ID_2 = 20 but it is only inserting one row not both. here is my insert query.

$sql = "INSERT INTO PARTS SET
          IN_ID     =". $db->qstr($in_id).",
      ER_ID     =". $db->qstr( $er_id).",
      ITEM      =". $db->qstr( $re2[0]['ITEM']   ).",
      NAME      =". $db->qstr( $re2[0]['NAME']   );

it inserts the data fine, just one row not multiple rows. any suggestions?

Thanks.

yea I need to insert in loop. from what I know my query should work — user3620142, May 19 '14 at 12:20

score 0 · Accepted Answer · edited May 23 '17 at 12:27

0

Insert your query in a loop

foreach ($re2 as $r): $sql = "INSERT INTO PARTS SET IN_ID =". $db->qstr($in_id).", ER_ID =". $db->qstr( $er_id).", ITEM =". $db->qstr( $r['ITEM'] ).", NAME =". $db->qstr( $r['NAME'] ); endforeach;

or use multiple inserts Inserting multiple rows in mysql

both should be fine at what you have there.

edited May 23 '17 at 12:27

Community

1
1

answered May 19 '14 at 12:22

Marius.C

700
6
14

can I not just use foreach in this case? – user3620142 May 19 '14 at 12:27
`var_dump` your array to see what's in there. I'll update my post with a foreach loop. – Marius.C May 19 '14 at 12:50
I tried this too but it is inserting incorrect data. I am getting 4 and 5 as values – user3620142 May 19 '14 at 14:21
ok I made a mistake. this is working well. thank you Marius C – user3620142 May 19 '14 at 14:31
foreach ($re2 as $r){ $sql = "INSERT INTO PARTS SET IN_ID =". $db->qstr($in_id).", ER_ID =". $db->qstr( $er_id).", ITEM =". $db->qstr( $r['ITEM'] ).", NAME =". $db->qstr( $r['NAME'] ); } – user3620142 May 19 '14 at 14:44

looping mysql query to get multiple rows of data from a table and insert into another table

1 Answers1