title: PHP PDO Insert Using Loop
body: <p>I am having trouble using PDO to insert multiple records into a database.  I can successfully add a single record, but as soon as I add the <code>foreach</code> loop, it fails.  After reading a number of other SO questions regarding this, I believe I need to "bind" my variables, although I am completely confused on the proper syntax.</p>
  
  <p>Here is the original function I created:</p>
  
  <pre><code>&lt;? function addToDatabase () {
      //Get All Variables
      $timestamp = date("Y-m-d H:i:s");
      $schoolName = $_SESSION['schoolName'];
      $schoolStreet = $_SESSION['schoolStreet'];
      $schoolCity = $_SESSION['schoolCity'];
      $schoolState = $_SESSION['schoolState'];
      $schoolZip = $_SESSION['schoolZip'];
      $schoolContactName = $_SESSION['schoolContactName'];
      $schoolContactTitle = $_SESSION['schoolContactTitle'];
      $schoolContactPhone = $_SESSION['schoolContactPhone'];
      $schoolCsontactEmail = $_SESSION['schoolContactEmail'];
      $inputMethod = $_SESSION['inputMethod'];
  
      $studentDataArray = $_SESSION['studentDataArray'];
  
      $studentFirstNameField = $_SESSION['studentFirstNameField'];
      $studentLastNameField = $_SESSION['studentLastNameField'];
      $studentStreetField = $_SESSION['studentStreetField'];
      $studentCityField = $_SESSION['studentCityField'];
      $studentStateField = $_SESSION['studentStateField'];
      $studentZipcodeField = $_SESSION['studentZipcodeField'];
      $studentDOBField = $_SESSION['studentDOBField'];
      $studentGenderField = $_SESSION['studentGenderField'];
      $studentGradeField = $_SESSION['studentGradeField'];
  
      //Connnect to Database
      $host = 'myHost';
      $un = 'myUsername';
      $pw = 'myPassword';
      $db_name = 'myTable';
  
      try {
          $conn = new PDO("mysql:host=$host;dbname=$dbName", $un, $pw);
          echo 'Connected to database&lt;br&gt;';
  
          $sql = "INSERT INTO studentData (originallyAddedOn, inputMethod, studentFirst, studentLast, studentStreet, studentCity, studentState, studentZip, studentDOB, studentGender, studentGrade, schoolName, schoolStreet, schoolCity, schoolState, schoolZip, schoolContactName, schoolContactTitle, schoolContactEmail, schoolContactPhone) VALUES (:originallyAddedOn, :inputMethod, :studentFirst, :studentLast, :studentStreet, :studentCity, :studentState, :studentZip, :studentDOB, :studentGender, :studentGrade, :schoolName, :schoolStreet, :schoolCity, :schoolState, :schoolZip, :schoolContactName, :schoolContactTitle, :schoolContactEmail, :schoolContactPhone)";
  
          foreach ($studentDataArray as $student){
              $q = $conn-&gt;prepare($sql);
              echo $student[$studentFirstNameField]."&lt;br&gt;";
              $q-&gt;execute(array(  ':originallyAddedOn'=&gt;$timestamp,
                              ':inputMethod'=&gt;$inputMethod,
                              ':studentFirst'=&gt;$student[$studentFirstNameField],
                              ':studentLast'=&gt;$student[$studentLastNameField],
                              ':studentStreet'=&gt;$student[$studentStreetField],
                              ':studentCity'=&gt;$student[$studentCityField],
                              ':studentState'=&gt;$student[$studentStateField],
                              ':studentZip'=&gt;$student[$studentZipField],
                              ':studentDOB'=&gt;$student[$studentDOBField],
                              ':studentGender'=&gt;$student[$studentGenderField],
                              ':studentGrade'=&gt;$student[$studentGradeField],
                              ':schoolName'=&gt;$schoolName,
                              ':schoolStreet'=&gt;$schoolStreet,
                              ':schoolCity'=&gt;$schoolCity,
                              ':schoolState'=&gt;$schoolState,
                              ':schoolZip'=&gt;$schoolZip,
                              ':schoolContactName'=&gt;$schoolContactName,
                              ':schoolContactTitle'=&gt;$schoolContactTitle,
                              ':schoolContactEmail'=&gt;$schoolContactEmail,
                              ':schoolContactPhone'=&gt;$schoolContactPhone));           
              }
              // close the database connection
              $dbh = null;
          }
          catch(PDOException $e) {
              echo $e-&gt;getMessage();
              }
      }
  </code></pre>
  
  <p>The <code>$studentDataArray</code> looks similar to this:</p>
  
  <pre><code> 0 =&gt; //student 1
      array
        [0] =&gt; 'Joe' //First
        [1] =&gt; 'Smith' //Last
        [2] =&gt; '101 Main St' //Street
        [3] =&gt; 'Boston' //City
        [4] =&gt; 'MA' //State
        [5] =&gt; '01234' //Zip
        [6] =&gt; '2000-01-01' //Date of Birth
        [7] =&gt; 'Male' //Gender
        [8] =&gt; '12'  //Grade
  
   1 =&gt; //Student 2
      array
        [0] =&gt; 'Jane'
        [1] =&gt; 'Smith'
        [2] =&gt; '99 Main St'
        [3] =&gt; 'Boston'
        [4] =&gt; 'MA'
        [5] =&gt; '01234'
        [6] =&gt; '2000-02-02'
        [7] =&gt; 'Female'
        [8] =&gt; '10'
  </code></pre>
  
  <p><hr>
  <strong>UPDATE:</strong>  For those that are interested, here is my final function after I fixed the errors:</p>
  
  <pre><code>&lt;? function addToDatabase ($dataArray) {
  
      //Connnect to Database
      $host = 'myHost';
      $un = 'myUsername';
      $pw = 'myPassword';
      $db_name = 'myTable';    
  
      try {
          $conn = new PDO("mysql:host=$host;dbname=$dbName", $un, $pw);
          echo 'Connected to database&lt;br&gt;';
  
          $sql = "INSERT INTO studentData (originallyAddedOn, inputMethod, studentFirst, studentLast, studentStreet, studentCity, studentState, studentZip, studentDOB, studentGender, studentGrade, schoolName, schoolStreet, schoolCity, schoolState, schoolZip, schoolContactName, schoolContactTitle, schoolContactEmail, schoolContactPhone) VALUES (:originallyAddedOn, :inputMethod, :studentFirst, :studentLast, :studentStreet, :studentCity, :studentState, :studentZip, :studentDOB, :studentGender, :studentGrade, :schoolName, :schoolStreet, :schoolCity, :schoolState, :schoolZip, :schoolContactName, :schoolContactTitle, :schoolContactEmail, :schoolContactPhone)";
          $q = $conn-&gt;prepare($sql);
  
          foreach ($dataArray as $student){
              $a = array (':originallyAddedOn'=&gt;$student['timestamp'],
                          ':inputMethod'=&gt;$student['inputMethod'],
                          ':studentFirst'=&gt;$student['studentFirst'],
                          ':studentLast'=&gt;$student['studentLast'],
                          ':studentStreet'=&gt;$student['studentStreet'],
                          ':studentCity'=&gt;$student['studentCity'],
                          ':studentState'=&gt;$student['studentState'],
                          ':studentZip'=&gt;$student['studentZip'],
                          ':studentDOB'=&gt;$student['studentDOB'],
                          ':studentGender'=&gt;$student['studentGender'],
                          ':studentGrade'=&gt;$student['studentGrade'],
                          ':schoolName'=&gt;$student['schoolName'],
                          ':schoolStreet'=&gt;$student['schoolStreet'],
                          ':schoolCity'=&gt;$student['schoolCity'],
                          ':schoolState'=&gt;$student['schoolState'],
                          ':schoolZip'=&gt;$student['schoolZip'],
                          ':schoolContactName'=&gt;$student['schoolContactName'],
                          ':schoolContactTitle'=&gt;$student['schoolContactTitle'],
                          ':schoolContactEmail'=&gt;$student['schoolContactEmail'],
                          ':schoolContactPhone'=&gt;$student['schoolContactPhone']);
  
              if ($q-&gt;execute($a)) {          
                  // Query succeeded.
                  } else {
                      // Query failed.
                      echo $q-&gt;errorCode();
                      }
              // close the database connection
              $dbh = null;
              echo "Insert Complete!";
          }
          }
          catch(PDOException $e) {
              echo $e-&gt;getMessage();
              }
      }
  </code></pre>