Two for each loops for a query

Question

I am trying to make a loop for 2 arrays and I run into conflicts where it creates duplicate entries...I figure I am just creating the loop wrong.

        foreach($productIds as $productID){

            foreach($qty as $q) {
$sql = "INSERT INTO orderedProducts (productID, orderID, qty) VALUES 
((select productID from products where productID ='$productID'), '$orderID', '$q')";
           execute_query($sql);

                 }
           }

If I remove the qty loop and just hardcode the qty in it works fine. Is there some sort of way to combine the two loops?

INSERT INTO orderedProducts (productID, orderID, qty) VALUES ((select productID from products where productID ='1'), '24', '1000')Error updating database: Duplicate entry '1-24' for key 'PRIMARY' Basically the productID may not be duplicated in the table. The orderID can. — Butterflycode, Mar 25 '13 at 07:20
The products do not have duplicates in the array...$productIds = array("1","2","3"); — Butterflycode, Mar 25 '13 at 07:21
there is no duplicacy but when you try to run it with second loop it will create problem... because assume $qty = array("1","2") so insert will be run twice for each product id causing the duplicate insertion — alwaysLearn, Mar 25 '13 at 07:30
I have tried to give a solution.. hope it gives you some idea — alwaysLearn, Mar 25 '13 at 07:37
Ohh I didnt realised that .. what is your primary key in orderedProducts.. ? — alwaysLearn, Mar 25 '13 at 08:17

score 1 · Answer 1 · edited May 23 '17 at 10:25

1

use INSERT INTO...SELECT statement on this,

INSERT INTO orderedProducts (productID, orderID, qty) 
SELECT productID, '$orderID', '$q'
FROM   products
WHERE  productID = '$productID'

INSERT INTO... SELECT Syntax

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

edited May 23 '17 at 10:25

Community

1
1

answered Mar 25 '13 at 07:10

John Woo

258,903
69
498
492

Completely offline system, not worried about that. – Butterflycode Mar 25 '13 at 07:11
btw, same issue, Error updating database: Duplicate entry '1-23' for key 'PRIMARY'....The productID can not be duplicated. – Butterflycode Mar 25 '13 at 07:19
it because there may have been multiple `productID` on table `products`. What exactly do you want on table `orderedProducts`? – John Woo Mar 25 '13 at 07:22
The array for productIDs is $productIds = array("1","2","3"); No chance there are duplicates. But for some reason it loops through trying to add 1 again. – Butterflycode Mar 25 '13 at 07:24

score 0 · Accepted Answer · answered Mar 25 '13 at 08:19

Figured out the problem...can't use foreach loops it just messes up, I just used the index's of the arrays

    for($i =0 ; $i < sizeof($productIds);$i++){ 
$sql = "INSERT INTO orderedProducts (productID, orderID, qty) VALUES ((select productID from products where productID ='$productIds[$i]'), '$orderID', '$qty[$i]')";
        execute_query($sql);
       }

Two for each loops for a query

2 Answers2