Insert array in Database

Question

I am trying to insert an array in database but have no luck. Here is what I tried.

I am getting data from a form using the post method.

if(isset($_POST['submit'])){ 
        
    $type = $_POST['type'];
    $color = $_POST['color'];
    $thicknes = $_POST['thicknes'];
    $fill = $_POST['fill'];
    $thickness_2 = $_POST['thickness_2'];  
    $quantity  = $_POST['quantity'];
     
    $email = $_POST['email']; 
     
}

This is dump for each variable

    var_dump($type) //outputs -> array(3) { [0]=> string(21) "VALUE1" [1]=> string(13) "VALUE2" [2]=> string(21) "VALUE3" }
    var_dump($color) //outputs -> string(9) "#TEST1" [1]=> string(9) "#TEST2" [2]=> string(9) "#TEST3" }
    var_dump($thicknes) //outputs -> string(2) "40" [1]=> string(2) "30" [2]=> string(2) "30" }
    var_dump($fill) //outputs -> string(3) "55" [1]=> string(3) "66" [2]=> string(3) "77" }      
    var_dump($thickness_2) //outputs -> string(3) "0.4" [1]=> string(3) "0.3" [2]=> string(3) "0.3" } 
    var_dump($quantity) //outputs -> string(3) "500" [1]=> string(3) "555" [2]=> string(3) "999" }
    var_dump($email) //outputs -> string(13) "[email protected]" }
}

My database

ID  | TYPE  | COLOR  | FILL  |  THICNKES_2 |  QUANTITY |  EMAIL

The first transaction should be, the first element from every array. The second should be the second element from every array ...

1  | VALUE1  | TEST1   | 40  | 55 | 500 | [email protected]

Email should be the same for all rows being inserted.

I was trying to insert it by creating a nested foreach, but it doesnt work. It inserts only one row.

foreach($type as $t){
   foreach($color as $c){
     foreach($thicknesas $tt){
        foreach($fill as $f){
           foreach($quantity as $q){
              foreach($emailas $e){
                 // query
              } 
           } 
        } 
     } 
  } 
}

Hi. Can you show how your form as builded? Because fields var_dump outpus are very strange. Other thing is the nested foreach, dont use more then 3. — Rodrigo Gattermann
– Rodrigo Gattermann, Commented Nov 30, 2021 at 19:34
assuming each array has the same number of elements, just use a for loop, not a foreach. for ($i = 0; $i <= count($type)-1; $i++) { $sql = "INSERT INTO TABLE VALUES ('" . $type . "', '" . $color . "', '". $fill . "', '". $thickness . "', '". $email . "')"; } — exceptional exception
– exceptional exception, Commented Nov 30, 2021 at 19:40

u_mulder · Accepted Answer · 2021-11-30 19:35:02Z

1

Iterate over one array using keys also and take values under that key from other arrays:

foreach($type as $key => $t) {
    // insert into db values: ($t, $color[$key], $thicknes[$key], $fill[$key] etc) 
}

answered Nov 30, 2021 at 19:35

u_mulder

54.8k5 gold badges54 silver badges72 bronze badges

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

Comments

Dula · Accepted Answer · 2021-11-30 20:09:52Z

1

Nested arrays is not the way to do it.

According to your question you are getting arrays with 3 data elements. So you can iterate through one array and insert the data. You can get the data from the other arrays using the array index.

for ($i = 0; $i < count($type); $i++){

   // here comes the insert query
   // insert into 'YOUR_TABLE' (TYPE,COLOR,FILL,THICNKES_2,QUANTITY,EMAIL) values($type[$i],$color[$i],$fill[$i],$thickness_2[$i],$quantity[$i],$email);
        
}

answered Nov 30, 2021 at 20:09

Dula

1,4245 gold badges25 silver badges42 bronze badges

Comments

miken32 · Accepted Answer · 2021-11-30 20:26:11Z

The other answers are correct, but promote extremely unsafe database practices. Preparing a statement outside a loop will improve security and performance. Assuming you're using PDO:

/** @var PDO $db */
$stmt = $db->prepare("INSERT INTO `some_table` SET `type`=?, `color`=?, `thicknes_2` = ?, `quantity` = ?, `email` = ?");
for ($i = 0; $i < count($type); $i++) {
    $stmt->execute([$type[$i], $color[$i], $thickness_2[$i], $quantity[$i], $email]);
}

It does make things easier to handle if you design your HTML more carefully. You're likely using something like this:

<form>
    <input name="type[]"/>
    <input name="color[]"/>
    <input name="thickness_2[]"/>
    <input name="quantity[]"/>

    <input name="type[]"/>
    <input name="color[]"/>
    <input name="thickness_2[]"/>
    <input name="quantity[]"/>

    <input name="email"/>
</form>

By simply adding the inputs to the same subarray, you could make your PHP much simpler.

<form>
    <input name="items[0][type]"/>
    <input name="items[0][color]"/>
    <input name="items[0][thickness_2]"/>
    <input name="items[0][quantity]"/>

    <input name="items[1][type]"/>
    <input name="items[1][color]"/>
    <input name="items[1][thickness_2]"/>
    <input name="items[1][quantity]"/>

    <input name="email"/>
</form>

Now your PHP could look like this:

/** @var PDO $db */
$stmt = $db->prepare("INSERT INTO `some_table` SET `type`=?, `color`=?, `thicknes_2` = ?, `quantity` = ?, `email` = ?");
foreach ($_POST["items"] as $item) {
    $stmt->execute($item);
}

Collectives™ on Stack Overflow

Insert array in Database

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