Insert values into database by using foreach loop

Question

I get values in array for one parameter which is width.

HTML file

<div class="col-sm-2"><input type="number" name="width[]" class="form-control"></div> 
<div class="col-sm-2"><input type="number" name="width[]" class="form-control"></div> 
<div class="col-sm-2"><input type="number" name="width[]" class="form-control"></div>

After I submit values I get only one record in db for width, other variables are all the same except for width which is different.

this is php code

 if (isset($_POST['submit'])) {

    $width  = $_POST['width']; 
    $quantity =   $_POST['quantity'];
    $length =  $_POST['length']; 
    $productID =  $_POST['productID'];

    $date = date("Y-m-d H:i");

    foreach ($width as $value) { 

        $stmt=$conn->prepare("INSERT INTO testDB (width, quantity, length, productID)                 
        VALUES (:witdh, :quantity, :length, :productID) "); 

        $stmt->bindParam(':witdh',$value);
        $stmt->bindParam(':quantity',$quantity);
        $stmt->bindParam(':length',$length);
        $stmt->bindParam(':productID',$productID); 

        if($stmt->execute()){
            echo "success";
            exit();
        } 
        else{
            print_r($stmt->errorInfo()); 
        }  
    }   
}

are the spaces in your html correct ? "width []" should be "width[]" — cli-ish
– cli-ish, Commented Oct 17, 2019 at 9:53
Although it may be misleading. The typo doesn't really matter as it's consistent between the SQL statement and the bindParam call. — Nigel Ren
– Nigel Ren, Commented Oct 17, 2019 at 10:01
The bind which sets the quantity in the SQL only ever uses the value $quantity, as this doesn't change in the loop, it will always be the same for each record. The only value which is different is the width field. — Nigel Ren
– Nigel Ren, Commented Oct 17, 2019 at 10:03

Rahul · Accepted Answer · 2019-11-06 06:43:01Z

1

When you do

    if($stmt->execute()){
        echo "success";
        exit();
    }

This will execute the prepared statement and if it succeeds (even in the first time around the loop) it will exit the code!

You should also prepare the statement before the loop, so use...

$stmt=$conn->prepare("INSERT INTO testDB (witdh, quantity, length, productID)                 
    VALUES (:witdh, :quantity, :length, :productID) "); 
$stmt->bindParam(':witdh',$value);
$stmt->bindParam(':quantity',$quantity);
$stmt->bindParam(':length',$length);
$stmt->bindParam(':productID',$productID); 
foreach ($width as $value) { 

    if($stmt->execute()){
        echo "success";
    } 
    else{
        print_r($stmt->errorInfo()); 
    }  
}

You can set a flag to say if anything fails, how you handle this is up to you.

edited Nov 6, 2019 at 6:43

Rahul

18.6k7 gold badges43 silver badges64 bronze badges

answered Oct 17, 2019 at 9:53

Nigel Ren

57.3k11 gold badges49 silver badges59 bronze badges

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

9 Comments

cli-ish Over a year ago

This is not wrong, but not part of his current problem. He got the name wrong.

Nigel Ren Over a year ago

@BlackNetworkBit, even if the name is corrected, they will still only get 1 record. It's a case of having to fix 2 problems to get it working.

cli-ish Over a year ago

I think the exit() is just there to test.

Nigel Ren Over a year ago

@Rahul, still not sure as one of the problems is that OP says that the other fields don't change (I think) which as they aren't changed means they may be expecting something else. Have to wait and see.

Rahul Over a year ago

Then, OP has to figure out by 'emself.

|

Rahul · Accepted Answer · 2019-10-17 10:04:53Z

0

You have to remove space from name input element.

<div class="col-sm-2"><input type="number" name="width[]" class="form-control"></div> 
<div class="col-sm-2"><input type="number" name="width[]" class="form-control"></div> 
<div class="col-sm-2"><input type="number" name="width[]" class="form-control"></div>

If you check, I removed space from the name after width.

If will give proper output in print_r($_POST['width']);

EDIT: Please remove exit(); as it is stopping the loop after the first insert.

edited Oct 17, 2019 at 10:04

answered Oct 17, 2019 at 9:55

Rahul

18.6k7 gold badges43 silver badges64 bronze badges

5 Comments

Marko Over a year ago

I made mistake when I was typing. I got an array with values, but each widh should be new record in table. I got only one record

Rahul Over a year ago

if you are getting $width multiple values, then there is no problem going forward.

Nigel Ren Over a year ago

The edit portion which is basically saying what I answered 10 minutes ago.

Marko Over a year ago

@Rahul exit was problem.I have three places to enter width, if I only enter 1 width it inserts three rows in database ? How I can prevent that

Rahul Over a year ago

Replace your$width = $_POST['width']; with $width = array_filter(array_map('trim',$_POST['width'])); and check

Collectives™ on Stack Overflow

Insert values into database by using foreach loop

2 Answers 2

9 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related