使用循环的PHP插入

Question

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

下面是我创建的原始函数：

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

Answer 2

对于循环，执行此操作(PDO或其他支持准备语句的数据库客户端库)：

1. 准备SQL INSERT查询。
2. 绑定变量。
3. 根据绑定变量循环数组，每次迭代执行一次。

利润。

对于基于PDO的示例来说，数组中的数据要插入到某个需要一个名为option的列的表中。

首先，要将一些数据插入数据库：

$options = [
    ['option' => "Insert Option A " . uniqid()],
    ['option' => "Insert Option B " . uniqid()],
    ['option' => "Insert Option C " . uniqid()],
];

在其他地方，让我们假设有那个$options数组，并关心数据库交互。这需要一个连接：

$conn = new PDO('mysql:dbname=test;host=localhost', 'testuser', 'test');

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);

现在，让我们准备插入查询。在这里使用命名参数(如问题中所示)，请确保这也适用于编号参数：

$stmt = $conn->prepare('INSERT INTO config (`OPTION`) VALUES (:OPTION);');

现在，让我们将命名的参数绑定到这里的一个变量。注意，变量是前缀的(这里是insert)。这实际上是对输入数组中的option键的别名：

$stmt->bindParam(':OPTION', $insert_option);

所以现在从上面的编号列表，点1)准备SQL INSERT查询。和2.)绑定变量。已经完成了。

只有左边才是$options数组上插入值的循环：

foreach ($options as $insert) {
    extract($insert, EXTR_PREFIX_ALL, 'insert');
    $stmt->execute();
}

通过使用extract，可以一次根据输入数组设置多个变量，并且不需要太多的操作。

完整的例子：

$options = [
    ['option' => "Insert Option A " . uniqid()],
    ['option' => "Insert Option B " . uniqid()],
    ['option' => "Insert Option C " . uniqid()],
];

$conn = new PDO('mysql:dbname=test;host=localhost', 'testuser', 'test');

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);

# 1. Prepare    
$stmt = $conn->prepare('INSERT INTO config (`OPTION`) VALUES (:OPTION);');

# 2. Bind
$stmt->bindParam(':OPTION', $insert_option);

# 3. Loop & Execute
foreach ($options as $insert) {
    extract($insert, EXTR_PREFIX_ALL, 'insert');
    $stmt->execute();
}