looping through array and add to database for each value

Question

I have got the following code:

if (count($sales) > 0) {
    foreach ($sales as $sale) {
        //Generate variables
        $user_id = $_SESSION['user_id'];
        $sales_id = $sale['sales_id'];
        $product = $sale['product'];
        $quantity = $sale['quantity'];
        $default_printed = 0;
        $datetime = $_SESSION['session_date']." ".date('G:i:s');

        echo $sales_id.", ".$product.", ".$quantity.", ".$user_id."<br />";

        if ($stmt = $mysqli->prepare("INSERT INTO sales (uid,product,printed,saledatetime,jpgID,quantity) VALUES (?,?,?,?,?,?)")) {
            $stmt->bind_param('isisii',$user_id,$product,$default_printed,$datetime,$sales_id,$quantity);
            $stmt->execute();
            $stmt->close();
        }
    }
}

It should work, but for some reason it only adds to the database the FIRST time it runs... the echo produces the following, just to prove that the array is actually being looped through.

83, KR, 2, 2
84, KR, 1, 2
84, LR, 1, 2
85, KR, 1, 2
86, LR, 2, 2
87, KR, 1, 2
87, LR, 3, 2
89, KR, 2, 2

Why are the values not being added to the database on subsequent runs?

What exactly is your question? How does the desired output look like? — kero
– kero, Commented Jan 30, 2014 at 22:52

Roxxorfreak · Accepted Answer · 2014-01-30 22:56:45Z

1

Maybe the subsequent insert statements fail because a database constraint prevents the data from being inserted. Have you tried executing the statements "by hand" one after the other?

answered Jan 30, 2014 at 22:56

Roxxorfreak

3802 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

raidenace · Accepted Answer · 2014-01-30 23:10:13Z

Do not close the statement and connection inside the loop, close it after all the executions are done. Also you do not really need to 'prepare' the statement inside the loop. The whole idea is that you prepare() the statement outside the loop, then inside the loop you only pass values to execute() and once loop is completed, you close() the loop:

if (count($sales) > 0) {
    foreach ($sales as $sale) {
        //Generate variables
        $user_id = $_SESSION['user_id'];
        $sales_id = $sale['sales_id'];
        $product = $sale['product'];
        $quantity = $sale['quantity'];
        $default_printed = 0;
        $datetime = $_SESSION['session_date']." ".date('G:i:s');

        echo $sales_id.", ".$product.", ".$quantity.", ".$user_id."<br />";

        if ($stmt = $mysqli->prepare("INSERT INTO sales (uid,product,printed,saledatetime,jpgID,quantity) VALUES (?,?,?,?,?,?)")) {
            $stmt->bind_param('isisii',$user_id,$product,$default_printed,$datetime,$sales_id,$quantity);
            $stmt->execute();

        }
    }
            $stmt->close(); //move close() here
}

Martyn Ball · Accepted Answer · 2014-01-30 23:06:48Z

0

Just checked the database and I must of forgot to change the ID (Primary Key) to AUTO_INCREMENT so it was just being overwritten.

Cheers guys!

answered Jan 30, 2014 at 23:06

Martyn Ball

4,92510 gold badges66 silver badges147 bronze badges

Collectives™ on Stack Overflow

looping through array and add to database for each value

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related