将XML文件中的数据插入表中

I am trying to get data from an XML file on the web into my datbase so that I can use it.

我试图将Web上的XML文件中的数据导入到我的数据库中,以便我可以使用它。

I have produced the following code, but as its been a long time since I have done coding, imlost with the error message that I am getting.

我已经生成了以下代码,但由于我已经完成编码已经很长时间了,因此我会收到错误信息。

The error is "Unknown column '10074' in 'field list'".

错误是“字段列表”中的“未知列'10074'”。

10074 is the produce ID of the first item in the XML file.

10074是XML文件中第一项的产品ID。

Any pointers would be really useful as it is doing my head in!

任何指针都非常有用,因为它正在努力!

The code I have is as follows:

我的代码如下:

    <?php
   $Products = simplexml_load_file('http://atsdistribution.co.uk/feeds/xml_all_products.aspx');


$con = mysql_connect(Details);
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("catflaps_products", $con);


foreach($Products->Product as $Product)
{
$ProductID = $Product->ProductID;
$Name = $Product->Name;
$DropshipPrice = $Product->DropshipPrice;
$SRP = $Product->SRP;
$Brand = $Product->Brand;
$Xline = $Product->Xline;
$InStock = $Product->InStock;
$Stock = $Product->Stock;
$Barcode = $Product->Barcode;
$Weight = $Product->Weight;
$CategoryID = $Product->CategoryID;
$Category = $Product->Category;
$SmallImage = $Product->SmallImage;
$LargeImage = $Product->LargeImage;
$Description = $Product->Description;

mysql_query("INSERT INTO test(ProductID, Name, DropshipPrice, SRP, Brand, Xline, InStock, Stock, Barcode, Weight, CategoryID, Category, SmallImage, LargeImage, Description)
VALUES(`$ProductID`, `$Name` , `$DropshipPrice`, `$SRP`, `$Brand`, `$Xline`, `$InStock`, `$Stock`, `$Barcode`, `$Weight`, `$CategoryID`, `$Category`, `$SmallImage`, `$LargeImage`, `$Description`)")
        or die(mysql_error());

}

mysql_close($con);


?>

1 个解决方案

#1

You should not use backquotes inside VALUES part. It's only to quote mysql identifiers (like table, column names). I think if you remove it your problem will be solved

你不应该在VALUES部分中使用反引号。它只引用mysql标识符(如表,列名)。我想如果你删除它,你的问题将得到解决

You should use quotes (regular ones either ' or ") when you quote string value in VALUES part (but see below, there is a better approach)

当您在VALUES部分中引用字符串值时,您应该使用引号(常规的'或')(但请参见下文,有更好的方法)

If you choose #2, then you need to properly escape your values from XML by using mysql_real_escape_string in your case. In fact, this is a breach in security (see SQL injections) if you don't do this. But even if you say this is a temporary script one-time usage etc, you'll probably end up with another error when there is single- or double-quote in your xml data

如果选择#2,则需要在您的情况下使用mysql_real_escape_string从XML中正确地转义值。实际上,如果您不这样做,这是安全漏洞(请参阅SQL注入)。但即使你说这是一次性使用的临时脚本等,当你的xml数据中有单引号或双引号时,你可能会遇到另一个错误

The best approach is using PDO prepare statements, then you don't bother quoting certain datatypes with quotes or not doing this - you bind certain param with its datatype. And remember that mysql_* functions are deprecated today.

最好的方法是使用PDO准备语句,然后你不用引号引用某些数据类型或者不这样做 - 你将某个param与其数据类型绑定在一起。请记住,今天不推荐使用mysql_ *函数。

So this code works like a charm:

所以这段代码就像一个魅力:

<?php
$Products = simplexml_load_file('xml_all_products.xml');
$config = array('db' => array(
    'dbname' => 'test',
    'host'   => 'localhost:4040',
    'username' => 'xx',
    'password' => 'xxx'
));
$db = new PDO('mysql:dbname='.$config['db']['dbname'].';host='.$config['db']['host'],$config['db']['username'],$config['db']['password']);

foreach($Products->Product as $Product)
{
    $ProductID = $Product->ProductID;
    $Name = $Product->Name;
    $DropshipPrice = $Product->DropshipPrice;
    $SRP = $Product->SRP;
    $Brand = $Product->Brand;
    $Xline = $Product->Xline;
    $InStock = $Product->InStock;
    $Stock = $Product->Stock;
    $Barcode = $Product->Barcode;
    $Weight = $Product->Weight;
    $CategoryID = $Product->CategoryID;
    $Category = $Product->Category;
    $SmallImage = $Product->SmallImage;
    $LargeImage = $Product->LargeImage;
    $Description = $Product->Description;

    $ProductsRS = $db->prepare("INSERT INTO test(ProductID, Name, DropshipPrice, SRP, Brand, Xline, InStock, Stock, Barcode, Weight, CategoryID, Category, SmallImage, LargeImage, Description)
                        VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

    $ProductsRS->execute(array($ProductID, $Name, $DropshipPrice, $SRP, $Brand, $Xline, $InStock, $Stock, $Barcode, $Weight, $CategoryID, $Category, $SmallImage, $LargeImage, $Description));
}

#1

You should not use backquotes inside VALUES part. It's only to quote mysql identifiers (like table, column names). I think if you remove it your problem will be solved