PHP/MySQL问题:第1列的整数值'‘不正确

Question

对于以下sql语句(在php内部)：

// Create Table to Hold JSON

$sql = "CREATE TABLE {$value}_GAMES ( ".  // Creating a new table for each team
    "nfl_game_id INT NOT NULL, ".
    "team VARCHAR(3) NOT NULL, ".
    "opponent VARCHAR(3) NOT NULL, ".
    "totfd INT NOT NULL, ".
    "totyds INT NOT NULL, ".
    "pyds INT NOT NULL, ".
    "ryds INT NOT NULL, ".
    "pen INT NOT NULL, ".
    "penyds INT NOT NULL, ".
    "trnovr INT NOT NULL, ".
    "pt INT NOT NULL, ".
    "ptyds INT NOT NULL, ".
    "ptavg INT NOT NULL, ".
    "PRIMARY KEY ( nfl_game_id ));";

$retval = mysql_query($sql, $con); // Execute SQL Code
if(! $retval)
{
die('Could not create table: ' . mysql_error());
}
echo 'Table created successfully\n';


// Process JSON Data

$data = json_decode($result, true); // Decode JSON

foreach($data as $row)
{
$game = $row['nfl_game_id'];
$team = $row['team'];
$opponent = $row['opponent'];
$totfirstdown =$row['totfd'];
$totyds = $row['totyds'];
$pyds = $row['pyds'];
$ryds = $row['ryds'];
$pen = $row['pen'];
$penyds = $row['penyds'];
$trnovr = $row['trnovr'];
$pt = $row['pt'];
$ptyds = $row['ptyds'];
$ptavg = $row['ptavg'];

// Insert data into team-specific table 
$sql = "INSERT INTO {$value}_GAMES(nfl_game_id, team, opponent, totfd, ".
"totyds, pyds, ryds, pen, penyds, trnovr, pt, ptyds, ptavg)".
"VALUES($game, '$team', '$opponent', '$totfirstdown', '$totyds',".
"'$pyds', '$ryds', '$pen', '$penyds', '$trnovr', '$pt', '$ptyds', ".
"'$ptavg')";
$ret_val = mysql_query($sql,$con); // Execute SQL Code
if(! $ret_val)
{
    die('Could not add JSON data to table: ' . mysql_error());
}
}

mysql_close($con); // Close Connection

我收到以下错误：

无法将JSON数据添加到表:不正确的整数值:当我回显API调用时，第1行的列'nfl_game_id‘的'’数据是这样的：

[{"nfl_game_id":2009081350，“球队”：“ARI”，“对手”：“坑”，"totfd":22，"totyds":329‌​，"pyds":259，"ryds":70，“笔”：4，"penyds":27，"trnovr":2，"pt":6，"ptyds":266，"ptavg":3‌​7}，.....

知道是怎么回事吗？

Answer 1

在$game变量中也添加单引号。

$sql = "INSERT INTO {$value}_GAMES(nfl_game_id, team, opponent, totfd, totyds, pyds, ryds, pen, penyds, trnovr, pt, ptyds, ptavg) VALUES('$game', '$team', '$opponent', '$totfirstdown', '$totyds', '$pyds', '$ryds', '$pen', '$penyds', '$trnovr', '$pt', '$ptyds', '$ptavg')";

Answer 2

在这个错误中，禁用严格模式对我不起作用。我使用的是MySQL 5.7.15 (根据文档，从<= 5.7.8起同样有效的配置也适用)

我一直在尝试，直到我发现唯一的解决方案就是使用

UPDATE IGNORE ...
INSERT IGNORE INTO ... 

因为non strict在这个问题上也失败了。为了不被忽略，我不能替换所有出现的ColumnXX = '$possibleEmptyValue'。