PHP PDO使用循环插入

Question

我在使用PDO将多个记录插入数据库时​​遇到麻烦。我可以成功添加一条记录，但是一旦添加foreach循环，它就会失败。在阅读了与此相关的许多其他SO问题之后，我相信我需要“绑定”我的变量，尽管我对正确的语法完全感到困惑。

这是我创建的原始函数：

<? 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<br>';

        $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->prepare($sql);
            echo $student[$studentFirstNameField]."<br>";
            $q->execute(array(  ':originallyAddedOn'=>$timestamp,
                            ':inputMethod'=>$inputMethod,
                            ':studentFirst'=>$student[$studentFirstNameField],
                            ':studentLast'=>$student[$studentLastNameField],
                            ':studentStreet'=>$student[$studentStreetField],
                            ':studentCity'=>$student[$studentCityField],
                            ':studentState'=>$student[$studentStateField],
                            ':studentZip'=>$student[$studentZipField],
                            ':studentDOB'=>$student[$studentDOBField],
                            ':studentGender'=>$student[$studentGenderField],
                            ':studentGrade'=>$student[$studentGradeField],
                            ':schoolName'=>$schoolName,
                            ':schoolStreet'=>$schoolStreet,
                            ':schoolCity'=>$schoolCity,
                            ':schoolState'=>$schoolState,
                            ':schoolZip'=>$schoolZip,
                            ':schoolContactName'=>$schoolContactName,
                            ':schoolContactTitle'=>$schoolContactTitle,
                            ':schoolContactEmail'=>$schoolContactEmail,
                            ':schoolContactPhone'=>$schoolContactPhone));           
            }
            // close the database connection
            $dbh = null;
        }
        catch(PDOException $e) {
            echo $e->getMessage();
            }
    }

在$studentDataArray类似于此：

 0 => //student 1
    array
      [0] => 'Joe' //First
      [1] => 'Smith' //Last
      [2] => '101 Main St' //Street
      [3] => 'Boston' //City
      [4] => 'MA' //State
      [5] => '01234' //Zip
      [6] => '2000-01-01' //Date of Birth
      [7] => 'Male' //Gender
      [8] => '12'  //Grade

 1 => //Student 2
    array
      [0] => 'Jane'
      [1] => 'Smith'
      [2] => '99 Main St'
      [3] => 'Boston'
      [4] => 'MA'
      [5] => '01234'
      [6] => '2000-02-02'
      [7] => 'Female'
      [8] => '10'

更新： 对于那些感兴趣的，这是我修复错误后的最终功能：

<? 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<br>';

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

        foreach ($dataArray as $student){
            $a = array (':originallyAddedOn'=>$student['timestamp'],
                        ':inputMethod'=>$student['inputMethod'],
                        ':studentFirst'=>$student['studentFirst'],
                        ':studentLast'=>$student['studentLast'],
                        ':studentStreet'=>$student['studentStreet'],
                        ':studentCity'=>$student['studentCity'],
                        ':studentState'=>$student['studentState'],
                        ':studentZip'=>$student['studentZip'],
                        ':studentDOB'=>$student['studentDOB'],
                        ':studentGender'=>$student['studentGender'],
                        ':studentGrade'=>$student['studentGrade'],
                        ':schoolName'=>$student['schoolName'],
                        ':schoolStreet'=>$student['schoolStreet'],
                        ':schoolCity'=>$student['schoolCity'],
                        ':schoolState'=>$student['schoolState'],
                        ':schoolZip'=>$student['schoolZip'],
                        ':schoolContactName'=>$student['schoolContactName'],
                        ':schoolContactTitle'=>$student['schoolContactTitle'],
                        ':schoolContactEmail'=>$student['schoolContactEmail'],
                        ':schoolContactPhone'=>$student['schoolContactPhone']);

            if ($q->execute($a)) {          
                // Query succeeded.
                } else {
                    // Query failed.
                    echo $q->errorCode();
                    }
            // close the database connection
            $dbh = null;
            echo "Insert Complete!";
        }
        }
        catch(PDOException $e) {
            echo $e->getMessage();
            }
    }

Answer 1

您不需要绑定变量。香港专业教育学院以前用类似的代码做到这一点。很难说出什么问题了。您是否有例外-如果是这样？

我唯一看到的错误是您在循环中进行了准备…应该更像是：

try {
         $conn = new PDO("mysql:host=$host;dbname=$dbName", $un, $pw);
         echo 'Connected to database<br>';

        $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)";

        // prepare once... exceute many :-)
        $q = $conn->prepare($sql);

        foreach($studentDataArray as $student) {
            $q->execute($yourDataArray);
            // do other stuff if needed

        }

} catch(PDOException $e) {
  echo $e->getMessage();
}