CODEWITHSUNDEEP
phphtmlmysqlarraysforeach
fcreav
fcreav

Reputation: 360

Inserting arrays into SQL with for loop

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.

Upvotes: 1

Views: 1436

Answers (2)

RST
RST

Reputation: 3925

  $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.

Upvotes: 2

JC Sama
JC Sama

Reputation: 2204

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

Upvotes: 0

Related Questions