Inserting arrays into SQL with for loop

Question

I have a form on a page which is created by a for loop.

$scripte = ($_POST["scriptake"]);
$scriptot = ($_POST["scriptot"]);
include 'config.php';

echo '<h2>'.$scripte.' Equipment</h2>';
echo '<h2>Total Hours '.$scriptot.'</h2>';

echo '<table class="table table-responsive">
        <tr>
          <form action="equiptest.php" method="post">
          <th>Script Hour</th>
          <th>Equipment Required</th>
          <th>Stage</th>
        </tr>';
$x = 1;
$p = 1;
$r = 1;
while($x <= $scriptot ) {
  echo "<tr>
          <td>".$x++."</td>
          <td>
            <input name='equip[".$r++."]'>
          </td>
          <td>
            <input name='stage[".$p++."]'>
            <input name='ohyeah' type='hidden' value= '".$scriptot."'>
          </td>
        </tr>";
}
echo '<tr>
        <td colspan="2">
          <input type="submit" class="btn btn-primary">
        </td>
      </tr>
      </form>
     </table>';

As you can see a form with inputs is created with a for loop. The values from the form are collected in an array equip[] and stage[]. With have a counter for the index if that makes sense. The form is then submitted to the following script.

include 'config.php';
$scriptot = ($_POST["ohyeah"]);
$y = 1;

$r= 1;

foreach($_POST['equip'] as $key => $value) {    
  foreach($_POST['stage'] as $key => $stage) {
    $query = $con->stmt_init();
    $statement = $con->prepare("INSERT INTO dgam_equip 
                                (equiplist,stage)  VALUES (?,?)");

    $statement->bind_param('ss',$value,$stage);
    $statement->execute();
  }
}

echo 'success';
//bind result variables to be printed
$statement->close();

echo '<br><br><br><br><br>';

I'm trying to then insert the arrays into a database.

ID | Equiplist |Stage
ID equip[] stage[]

I am trying to nest a foreach loop or get the arrays added in the correct row. I can get the row in but the data excecutes a huge number of times. I am guessing that is because the foreach loop is excecuting the second foreach loop What is the correct way to put this kind of data into a database. I am trying to set an array up first but am struggling.

Edit and remove the unnecessary html, the only relevant thing is the actual loop, you should also improve the indentation of your code so its easier to read. — Cyclonecode, Mar 09 '15 at 23:32
sorry but in past i got 6 down votes for not putting the code up. il take off now — Fintan Creaven, Mar 09 '15 at 23:37
Just use one loop and use the index to access both arrays. You can get the size of the arrays using ```sizeof``` and you might want to compare the size of the arrays before looping over them — TOAOGG, Mar 09 '15 at 23:38
Your tags are wrong: `...`. They are not matched correctly and `` is not [valid content](https://developer.mozilla.org/en-US/docs/Web/HTML/Element/tr) for `` — Diego, Mar 09 '15 at 23:39
You should not trust user input, so it might change, I'm just being paranoid :P — TOAOGG, Mar 09 '15 at 23:42
i get the correct data if i echo and can put db. it just goes a silly amount of times. if i am expecting 60 rows i get 60 X 60 rows. The loop is looping over the loop — Fintan Creaven, Mar 09 '15 at 23:44
As I said, use one for loop to access both arrays at the same time. — TOAOGG, Mar 09 '15 at 23:44
ca i have a quick snippet to see what yyou are getting at. I have only used the simplest of these loops — Fintan Creaven, Mar 09 '15 at 23:46
Note that you just need to call `prepare()` and `bind_param()` **once**, before you start your first loop. Just create a couple of placeholders for you parameters ie `$stage` and `$equip` before calling `bind_param()` — Cyclonecode, Mar 09 '15 at 23:57

RST · Accepted Answer · 2015-03-10T07:23:10.937

2

  $length = count($_POST['stage']);
  $stages = $_POST['stage'];
  $equips = $_POST['equip'];
  $stage = '';
  $equip = '';
  $query = $con->stmt_init();
  $statement = $con->prepare( "INSERT INTO dgam_equip (equiplist,stage)  VALUES (?,?) ");
  $statement->bind_param('ss', $equip, $stage);

  for( $i=0; $i < $length; $i++ ) {
     $stage = $stages[$i];
     $equip = $equips[$i];

     if ( ! empty($stage) ) $statement->execute();

   }

It can be optimized to take all the values in one query.

edited Mar 10 '15 at 07:23

answered Mar 09 '15 at 23:47

RST

3,899
2
20
33

Is prepare needed in every step? :) – TOAOGG Mar 09 '15 at 23:51
2

You should not call `prepare()` and `bind_param()` inside the loop, just do this once before starting the loop ie: `$stage = null; $equip = null; $statement = $con->prepare("INSERT INTO ..."); $statement->bind_param('ss', $equip, $stage); for($i = 0; $i < $length; $i++) { $stage = $stages[$i]; $equip = $equips[$i]; $statement->execute(); }` – Cyclonecode Mar 09 '15 at 23:55
This works thankyou. Had to change a couple of bits that were specific to my code but you would not have known these. Thanks very much for your help. – Fintan Creaven Mar 10 '15 at 00:16
1

I have also take @cyclone advice it does not seem a good idea to call that everytime - upvote for that if it lets me. I dont have enough points to edit anwser as it is a good point – Fintan Creaven Mar 10 '15 at 00:25
That is why I said it can be optimized. I just wanted to show how to change your current code which also has the command inside the loop. – RST Mar 10 '15 at 07:08
What I usually do is create something similar to described here (http://stackoverflow.com/questions/19680494/insert-multiple-rows-with-pdo-prepared-statements). Collect the `INSERT INTO` om an array, then use `array_chunk()` on it and loop over the chunks to prevent hitting max length limitations. It seemed too much to go into to answer the initial problem. Adjusted the code to reflect the suggested change. – RST Mar 10 '15 at 07:18

score 0 · Answer 2 · answered Mar 09 '15 at 23:55

0

$equipes = $_POST['equip'];
$stages = $_POST['stage'];

$query = $con->stmt_init();
$statement = $con->prepare( "INSERT INTO dgam_equip (equiplist,stage)  VALUES (?,?) ");

for ($i = 0, $total = count($equipes); $i < $total; $i = $i + 100) {
        $insertEquipe = array_slice($equipes, $i, 100);
        $insertStage = array_slice($stages, $i, 100);
        $conn->beginTransaction();

        foreach ($insertEquipe as $equipe) {
           foreach ($insertStage as $stage) {
               $stmt->bindValue(1, $equipe);
               $stmt->bindValue(2, $stage);
               $stmt->execute();
            }  
        }
        $conn->commit();
    }

answered Mar 09 '15 at 23:55

JC Sama

2,214
1
13
13

This still contains the error that there will be n x n inserts instead of n – TOAOGG Mar 10 '15 at 00:12
He used `foreach` twice because he thought that was the way to address two arrays. However, the arrays have paired values with the same array-index in each array. They can be addressed in one `foreach`, creating n rows to send to database. – RST Mar 10 '15 at 07:28

Inserting arrays into SQL with for loop

2 Answers2