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
– Cyclonecode, Commented Mar 9, 2015 at 23:32
sorry but in past i got 6 down votes for not putting the code up. il take off now — fcreav
– fcreav, Commented Mar 9, 2015 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
– TOAOGG, Commented Mar 9, 2015 at 23:38
Your tags are wrong: <table ...><tr><form ..>...</tr>. They are not matched correctly and <form> is not valid content for <tr> — Diego
– Diego, Commented Mar 9, 2015 at 23:39

RST · Accepted Answer · 2015-03-10 07:23:10Z

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, 2015 at 7:23

answered Mar 9, 2015 at 23:47

RST

3,9252 gold badges24 silver badges33 bronze badges

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

6 Comments

TOAOGG Over a year ago

Is prepare needed in every step? :)

Cyclonecode Over a year ago

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(); }

fcreav Over a year ago

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.

fcreav Over a year ago

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

RST Over a year ago

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.

|

JC Sama · Accepted Answer · 2015-03-09 23:55:44Z

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 9, 2015 at 23:55

JC Sama

2,2041 gold badge14 silver badges13 bronze badges

2 Comments

TOAOGG Over a year ago

This still contains the error that there will be n x n inserts instead of n

RST Over a year ago

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.

Collectives™ on Stack Overflow

Inserting arrays into SQL with for loop

2 Answers 2

6 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related