MySQL, XML boolean values, and integers: Incorrect integer value:

Question

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.

The xml tag value is the word "true" (without the quotes), and this is the 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.

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:

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

It is not clear what the problem is since you offer no clue what SQL insert statement you generate from the xml data. My initial guess is that you try to insert TRUE as a literal, which equals the integer value 1. But if that were the case, it should just work, even if you stick it into a varchar column. So I'm now thinking you're trying to insert 'TRUE' (quoted string) into an integer column. For better help, post better info (INSERT statement) — Roland Bouman, Nov 15 '12 at 14:02
sorry about that, have edited original post to show query, thanks — MartinJJ, Nov 15 '12 at 14:04
is it not that default of '0' is false where the 'true' should return as 1?.. thats how i understand it from what i have read — MartinJJ, Nov 15 '12 at 14:29

O. Jones · Accepted Answer · 2012-11-15T18:51:43.563

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.

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.

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.

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

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

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.

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.

Try this, using a SQL case statement to translate true/other to 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.

Firstly thanks for your time. Point A returns this error "Bad data was either ##353## or ####", notice the empty string that refers to LeafCategory, the numerical value is the CategoryID, the xml contains only nodes that are used and can be different for each Category meaning that i also get "Bad data was either ##96766## or ##true##" where the leafcategory is included and contains the word value of 'true'. Removing NOT NULL still has the same outcome, this is why i was thinking that maybe i have to loop through with regex and change 'true' to '1' before insertion. Points C and D both fail. — MartinJJ, Nov 15 '12 at 15:06
Im aware of the injection risk and need for mysqli_real_escape_string but before i do that i want to get the code working, im pretty green when it comes to MySQL and need to have my code light and working before i start to confuse myself adding more code to a non working code, — MartinJJ, Nov 15 '12 at 15:14
Excellent Ollie, that works a treat and certainly saved me from messing around with regxp, your way seems a lot cleaner.. have marked as answered.. many thanks — MartinJJ, Nov 16 '12 at 06:29

MySQL, XML boolean values, and integers: Incorrect integer value:

1 Answers1