php 循环插入,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
';

$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]."
";

$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
';

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

}

}