php - PDO 查询未插入 - HY093 错误消息但绑定(bind)变量的数量正确

Question

代码

$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$stmt = $dbh->prepare($query);
print_r($fullStmt);

if(!$stmt->execute($fullStmt))
{
    print_r($stmt->errorInfo());
    $full_query = "INSERT INTO `fixtures` (competition_code,competition_id,competition_name,season_id,season_name,
    timestamp,uid,last_modified,matchday,period,matchwinner,date,team1,team1_halfscore,team1_score,team1_goals,
    team1_side,team2,team2_halfscore,team2_score,team2_goals,team2_side) VALUES (";
    foreach($fullStmt as $val){ $full_query.= "'$val', "; }
    $full_query = trim($full_query, ", ");
    $full_query.= ");";
    exit($full_query);
}

输出

Array
(
    [competition_code] => EN_PR
    [competition_id] => 8
    [competition_name] => English Barclays Premier League
    [season_id] => 2013
    [season_name] => Season 2013/2014
    [timestamp] => 2013-10-30 09-03-49
    [uid] => g695281
    [last_modified] => 2013-10-15T12:35:58+00:00
    [matchday] => 1
    [period] => FullTime
    [matchwinner] => t7
    [date] => 2013-08-17 15:00:00 BST
    [team1] => t3
    [team1_halfscore] => 1
    [team1_score] => 1
    [team1_goals] => p44346/#/Goal
    [team1_side] => Home
    [team2] => t7
    [team2_halfscore] => 1
    [team2_score] => 3
    [team2_goals] => p54861/#/Goal//p83564/#/Goal//p54861/#/Penalty
    [team2_side] => Away
)
Array
(
    [0] => HY093
    [1] => 
    [2] => 
)
INSERT INTO `fixtures` (competition_code,competition_id,competition_name,season_id,season_name,
            timestamp,uid,last_modified,matchday,period,matchwinner,date,team1,team1_halfscore,team1_score,team1_goals,
            team1_side,team2,team2_halfscore,team2_score,team2_goals,team2_side) VALUES ('EN_PR', '8', 'English Barclays Premier League', '2013', 'Season 2013/2014', '2013-10-30 09-03-49', 'g695281', '2013-10-15T12:35:58+00:00', '1', 'FullTime', 't7', '2013-08-17 15:00:00 BST', 't3', '1', '1', 'p44346/#/Goal', 'Home', 't7', '1', '3', 'p54861/#/Goal//p83564/#/Goal//p54861/#/Penalty', 'Away');

概览

$fullStmt 是一个值数组，我有一个查询如下:

$query = "INSERT INTO `fixtures` (
                competition_code,
                competition_id,
                competition_name,
                season_id,
                season_name,
                timestamp,
                uid,
                last_modified,
                matchday,
                period,
                matchwinner,
                date,
                team1,
                team1_halfscore,
                team1_score,
                team1_goals,
                team1_side,
                team2,
                team2_halfscore,
                team2_score,
                team2_goals,
                team2_side
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

但是，当尝试执行时，它返回 FALSE。我输出结果查询，当直接将其插入 phpMyAdmin 时，它插入成功。

为什么当我在 phpMyAdmin 的 SQL 字段中运行代码时插入没有问题，但在 PHP 中执行时却没有？

Answer 1

在我自己测试之前，我不确定 PDO 中的这种行为，但是由于 $fullStmt 中的值数组是一个关联数组，PDO 实际上是尝试根据其数组键绑定(bind)命名参数。您最初准备的语句使用位置 ? 占位符，因此命名参数不存在(并且它们不能与 ? 混合)。

因此您需要消除 PDO 的数组键，以正确地将数组值与其位置占位符绑定(bind)。这最容易通过调用 array_values() 来完成。在数组上传递给 execute()。

// Strip off the associative array keys...
if(!$stmt->execute(array_values($fullStmt))) {
   // etc
}

请注意，PDO 对数组顺序的正确解释取决于它的值以完全正确的顺序开始。您的 $fullStmt 数组确实以正确的顺序排列，无论您使用何种方式生成它。但是，如果该过程发生变化，剥离数组键可能会导致您的 INSERT 语句将值放入错误的列中。重构语句生成以使用 VALUES () 列表中的 :competition_code 之类的命名参数并继续使用关联数组来防止这种潜在的绊倒可能是值得的点。