MySQL，XML布尔值和整数：不正确的整数值：

Im working on grabbing xml data from ebay and putting the results into MySQL, Grabbing the data works fine, however inputting to database fails due to an incorrect integer value for a couple of the xml tags values.

我正在努力从ebay抓取xml数据并将结果放入MySQL,抓取数据工作正常,但由于几个xml标签值的整数值不正确,输入到数据库失败。

The xml tag value is the word "true" (without the quotes), and this is the db sql:

xml标记值是单词“true”(不带引号),这是db sql:

CREATE TABLE ebay_categories (
CategoryID int(10) NOT NULL default '0',
CategoryLevel int(5) NOT NULL default '0',
CategoryName varchar(120) NOT NULL default '',
CategoryParentID int(10) NOT NULL default '0',
LeafCategory int(1) NOT NULL default '0',
AutoPayEnabled int(1) NOT NULL default '0',
Expired int(1) NOT NULL default '0',
IntlAutosFixedCat int(1) NOT NULL default '0',
Virtual int(1) NOT NULL default '0',
LSD int(1) NOT NULL default '0',
ORPA int(1) NOT NULL default '0',
PRIMARY KEY  (CategoryID),
KEY catlevel (CategoryLevel),
KEY parent (CategoryParentID),
KEY ape (AutoPayEnabled),
KEY expired (Expired),
KEY IAFC (IntlAutosFixedCat),
KEY virtual (Virtual),
KEY lsd (LSD),
KEY orpa (ORPA),
KEY leaf (LeafCategory)
) TYPE=MyISAM;

i have tried int, tinyint, Boolean (resorts to tinyint) to no avail and still get this issue. Theres nothing wrong with the db connection as i ran a test using varchar as the int type for the LeafCategory and others and everything worked ok.

我试过int,tinyint,布尔(度假到tinyint)无济于事,仍然遇到这个问题。数据库连接没有错,因为我使用varchar作为LeafCategory的int类型运行测试,其他一切正常。

is theres something i can do without resorting to searching and replacing via regex before db insertion?

在数据库插入之前,我可以做些什么而不需要通过正则表达式搜索和替换?

edit:

    $query = "INSERT INTO `ebay_categories` (`CategoryID`, `LeafCategory`)VALUES           ('$xmlCategoryID', '$xmlLeafCategory')";

    if (mysqli_query($link, $query)) {
    echo "Successfully inserted " . mysqli_affected_rows($link) . " row";
} else {
    echo "Error occurred: " . mysqli_error($link);
}

The SQL statement unwrapped from client code is:

从客户端代码中解包的SQL语句是:

    INSERT INTO `ebay_categories` 
                (`CategoryID`, `LeafCategory`)
         VALUES 
                ('$xmlCategoryID', '$xmlLeafCategory')";

1 个解决方案

#1

The error you have is telling you at least one record in your XML has something other than a valid integer for either $xmlCategoryID or $xmlLeafCategory.

您遇到的错误是告诉您XML中至少有一条记录不是$ xmlCategoryID或$ xmlLeafCategory的有效整数。

What to do?

该怎么办?

A. You could have your error message display the offending data, something like this:

答:您可能会在错误消息中显示有问题的数据,如下所示:

  echo "Error occurred: " . mysqli_error($link);
  echo "Bad data was either ##$xmlCategoryID## or ##$xmlLeafCategory##.";

Notice that I used ##$value## so you can detect empty strings in your error messages. You probably should do this.

请注意,我使用了## $ value ##,因此您可以在错误消息中检测空字符串。你可能应该这样做。

B. You could try changing your column definitions for those columns to remove the NOT NULL declaration. If in fact one of those values is empty, this may fix your problem.

B.您可以尝试更改这些列的列定义以删除NOT NULL声明。如果实际上其中一个值为空,这可能会解决您的问题。

C. It's possible that you need bigint values for this information. That is, they could be very large numbers.

C.您可能需要bigint值来获取此信息。也就是说,它们可能是非常大的数字。

D. If you don't care enough about null or bad values to bother to detect them you could try this.

D.如果你不关心空值或坏值而无法检测它们,你可以试试这个。

INSERT INTO `ebay_categories` 
             (`CategoryID`, `LeafCategory`)
      VALUES 
             (CAST(IFNULL('$xmlCategoryID',-1) AS INT)
            (CAST(IFNULL('$xmlLeafCategory',-1) AS INT)

This will turn null values into -1 and non-integer values into 0.

这会将空值转换为-1,将非整数值转换为0。

Edit: Oh, I understand now. Your leafCategory item isn't a number in XML, it's a true/false/empty value. Presumably false and empty mean the same thing.

编辑:哦,我现在明白了。您的leafCategory项不是XML中的数字,它是true / false / empty值。据推测,虚假和空虚意味着同样的事情。

Try this, using a SQL case statement to translate true/other to 1/0.

试试这个,使用SQL case语句将true / other转换为1/0。

INSERT INTO `ebay_categories` 
             (`CategoryID`, `LeafCategory`)
      VALUES (
              '$xmlCategoryID',
              CASE '$xmlLeafCategory' WHEN 'true' THEN 1 ELSE 0 END
              )

Finally, danger! You need to use prepared statements, and you need to sanitize these values you're extracting from your XML file. If you don't somebody will be able to trick your software into destroying your database by feeding it a poisoned XML file. Please read up on SQL injection.

最后,危险!您需要使用预准备语句,并且需要清理从XML文件中提取的这些值。如果你不这样做,那么有人可以通过提供有毒的XML文件来欺骗你的软件来破坏你的数据库。请阅读SQL注入。

#1