mySql doesn't accept ">" or ">"

[mo_katler]

Hi Folks,

I am after some help, iam trying to convert XML tags to Mysql. Every thing is working apart of the ASCII letter (&,>,",etc..). When the parser finds these characters i will put them in a new field. which resulting error in inputting info from XML doc to MySQL. I wonder if anyone could resolve this matter and any help will be exteremly appreciated.

Code.php:

function startElementHandler($parser, $name, $attributes)
{
global $currentTag;
$currentTag = $name;

}

// called when parser finds end tag
function endElementHandler($parser, $name)
{
global $values, $currentTag;
global $connection, $table;

      if (strtolower($name) == "row")
  { $query = "INSERT INTO $table";
        $query .= "(wo, kpi, sky, site, prob, details, rprt_date, tar_date, comp_date, days, rech, csm, fm, ein) ";
        $query .= "VALUES(\"" .join("\",\"", $values). "\");";      

$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());

        $values = array();
        $currentTag = "";
  }

}

function characterDataHandler($parser, $data)
{
global $fields, $values, $currentTag, $count;
if (trim($data) != "")
{

        $fields[$count] = $currentTag;

        $values[$count] = mysql_escape_string($data);
        $count++;
  }

}

// initialize parser
$xml_parser = xml_parser_create();

// turn off whitespace processing
xml_parser_set_option($xml_parser,XML_OPTION_SKIP_WHITE, TRUE);
xml_parser_set_option($xml_parser,XML_OPTION_TARGET_ENCODING,"UTF-8");

// turn on case folding
xml_parser_set_option($xml_parser, XML_OPTION_CASE_FOLDING, FALSE);

// set callback functions
xml_set_element_handler($xml_parser, "startElementHandler", "endElementHandler");
xml_set_character_data_handler($xml_parser, "characterDataHandler");

// open connection to database
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");
mysql_select_db($db) or die ("Unable to select database!");

// read XML file

XML.XML
<data>
<row>
<value>189&gt;8838</value>
<value>10</value>
<value>EN0031A1</value>
<value>Innova Park</value>
<value>G02</value>
<value>tony glancy Electrical Supply grd floor office</value>
<value>2006-03-02T15:01:00</value>
<value>2006-10-27T17:00:00</value>
<value>2006-10-27T15:04:00</value>
<value>239</value>
<value>4</value>
<value>Lynne Bunyan</value>
<value>John Hedges</value>
<value>701387387VW</value>
</row>
<data/>

Result:
Error in query: INSERT INTO fm_csm(wo, kpi, sky, site, prob, details, rprt_date, tar_date, comp_date, days, rech, csm, fm, ein) VALUES("189",">","8838","10","EN0031A1","Innova Park","G02","tony glancy Electrical Supply grd floor office","2006-03-02T15:01:00","2006-10-27T17:00:00","2006-10-27T15:04:00","239","4","Lynne Bunyan","John Hedges","701387387VW");. Column count doesn't match value count at row 1

As you can see above it will consider this char as a seperate string to be placed in a seperate field.
Many thanks in advance
Mo

[ClarkF1]

You could use str_replace or something similar.

[MarkR]

The problem is that you're seeing multiple character data nodes when an entity is involved; this is normal and your parser should be able to handle it.

The problem is nothing to do with MySQL, which can store both > and &gt;

When you see two or more character data messages adjacent, you SHOULD treat them as one, unless they have elements in between.

Your parser is faulty.

Mark

Mark

[mo_katler]

First of all, I would like to thank everyone who participate to help in my project.

Assume the same code attached above, I've added the follwing
foreach ($values as $value)
{
$ad =ereg_replace('&', 'm', join("\",\"", $values));

			}

it replaces the character '&' with 'm' -look in example attached below- but still treats it as seperate entity. Can you advise if i can control the seperator in my sql. In orther words, control of parser to seperate entities as required.

Example:
1898838","m","10","EN0031A1","Innova Park","G02","tony glancy Electrical Supply grd floor office","2006-03-02T15:01:00","2006-10-27T17:00:00","2006-10-27T15:04:00","239","4","Lynne Bunyan","John Hedges","701387387VW"