PHP PDO使用循环插入

我在使用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",: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();
            }
    }

你不需要绑定你的变量.我以前用类似的代码完成了这个.虽然很难说什么出错了.你有例外 – 如果是的话是什么？

我唯一看错的是你在循环中做好准备……应该更像是：

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

        $sql = "INSERT INTO studentData (originallyAddedOn,: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();
}

PHP PDO使用循环插入

猜你在找的PHP相关文章