mysql - MySQL INSERT IGNORE无法用于我的7DTD服务器脚本

我的表就是用这个创建的。

CREATE TABLE `Player` (
`id` int(11) NOT NULL auto_increment,
`playername` varchar(255) NOT NULL,
`SteamID` varchar(255) NOT NULL,
`position` varchar(255) NOT NULL,
`lastlogin` varchar(255) NOT NULL,
`approved` varchar(255) NOT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

我正在尝试将新字段插入列中而不覆盖其他字段。完整的脚本通过FTP下载player.xml，然后将播放器Steam64ID转换为用户SteamName，然后将其假设要插入到mysql数据库中（如果Steam64ID尚未在数据库中）。

$xml = simplexml_load_file("players.xml");
foreach($xml->children() as $player)
{
    $id = $player->attributes()->id;
    $profile = new SteamProfile($id);
    $name = $profile->getUsername();
    $lastlogin = $player->attributes()->lastlogin;
    $position = $player->lpblock->attributes()->pos;
    $sql1= "INSERT IGNORE INTO Player (playername, SteamID, position, lastlogin, approved) VALUES ('$name', '$id', '$position', '$lastlogin', '$approve')";
    if (!mysql_query($sql1,$connection))
    {
            die('Insert Error: ' . mysql_error());
    }
}

我尝试执行的所有操作都会不断返回此错误。

Insert Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'm L', '76561198108930923', '', '8/23/2014 7:47:36 PM', '')' at line 1

这是完整的代码减去连接信息，是的，我知道那是多么糟糕，所以我要寻求帮助。我也是100％新的mysql和php。

class SteamProfile
{
    const STEAMAPI_URL_MASK = 'http://steamcommunity.com/profiles/%s/?xml=1';
    const UNKONWN_NAME_MASK = 'User #%s (Username Not Found)';
    private $steamId;
    private $xml;
    public function __construct($steamId){
        $this->steamId = $steamId;
    }
    public function getUsername(){
        $xml = $this->getXml($this->steamId);
        return $xml->steamID ? (string)$xml->steamID : sprintf(self::UNKONWN_NAME_MASK, $this->steamId);
    }
    private function getXml($steamId){
        if ($this->xml){
            return $this->xml;
        }
        $url = sprintf(self::STEAMAPI_URL_MASK, $steamId);
        if (!$xml = simplexml_load_file($url)){
            throw new UnexpectedValueException(sprintf('Unable to load XML from "%s"', $url));
        }
        return $this->xml = $xml;
    }
}
if (ftp_get($conn_id, $local_file, $server_file, FTP_BINARY))
{
    echo "Successfully loaded $local_file\n<br />";
}
else
{
    echo "There was a problem\n.<br />";
}
ftp_close($conn_id);
$connection = mysql_connect("$hostname","$data_username","$data_password") or die ("Couldn't connect to server.");
$db = mysql_select_db("$db_name", $connection) or die("Couldn't select database.");
$xml = simplexml_load_file("players.xml");
foreach($xml->children() as $player)
{
    $id = $player->attributes()->id;
    $profile = new SteamProfile($id);
    $name = $profile->getUsername();
    $lastlogin = $player->attributes()->lastlogin;
    $position = $player->lpblock->attributes()->pos;
    $sql1= "INSERT IGNORE INTO Player (playername, SteamID, position, lastlogin, approved) VALUES ('$name', '$id', '$position', '$lastlogin', '$approve')";
    if (!mysql_query($sql1,$connection))
    {
            die('Insert Error: ' . mysql_error());
    }
}
sleep(2);
echo "Successfully inserted information to database<br />";
if(isset($_POST['update']))
{
    $steam = $_POST['steam'];
    $approved = $_POST['approved'];
    mysql_query ("UPDATE Player SET approved='$approved' WHERE SteamID = '$steam'");
    if(! $connection )
    {
        die('Could not connect: ' . mysql_error());
    }
    echo'<br />Updated ('.$steam.') into the database successfully.<br />';
}
else
{
}
$mysql = mysql_query("SELECT playername, SteamID, position, lastlogin, approved FROM Player WHERE position IS NOT NULL AND TRIM(position) <> '' AND approved = ''");
while($row = mysql_fetch_array($mysql))
        {
            echo '<br /><table><tr><td>
            Player Name: '.$row['playername'].'<br />
            SteamID: '.$row['SteamID'].'<br />
            Keystone Location: '.$row['position'].'<br />';
            echo 'Admin Approved: ';
            if (isset($row['position']) && ($row['approved'] != "Yes"))
            {
                echo 'No</td></tr></table>';
            }
            else
            {
                echo 'Yes</td></tr></table>';
            }
        }
mysql_close($connection);
?><br />
<form method="post" action="<?php $_PHP_SELF; ?>">
    <table width="400" border="0" cellspacing="1" cellpadding="2">
    <tr><td width="100">Type SteamID</td><td><input name="steam" type="text" id="steam"></td></tr>
    <tr><td width="100">Yes or No</td><td><input name="approved" type="text" id="approved"></td></tr>
    <tr><td width="100"></td><td></td></tr>
    <tr><td width="100"></td><td><input name="update" type="submit" id="update" value="Approve"></td></tr></table></form>

最佳答案

播放器名称很可能包含单引号，而MySQL将其视为值定界符。

在插入数据库之前，通过mysql-real-escape-string运行名称：

$name = mysql-real-escape-string($profile->getUsername());

顺便说一句，请注意，不建议使用mysql_方法，并且您确实应该使用mysqli或PDO。