[SOLVED] XML Database Import Error

[patsfans]

I'm trying to import an XML file into my database, but am running into a problem as it tries to parse it.  The XML file has multiple values, but not every SKU contains a value in that field so I'm getting a "Column count doesn't match value count" error each time it comes to a value that isn't there.  What code do I need to add to get it to ignore it and insert a blank field and then continue on?"

 

Here's the code in question:

 

<?php

// initialize some variables

$currentTag = "";

 

// this array will hold the values for the SQL statement

$values = array();

 

// this array will hold allowed fields/elements

$allowedFields = array("sku", "value1", "value2");

 

// XML file to parse

$xml_file="file.xml";

 

// database parameters

$host = "localhost";

$user = "name";

$pass = "pass";

$db = "db";

$table = "table";

 

// called when parser finds start tag

function startElementHandler($parser, $name, $attributes)

{

global $currentTag;

$currentTag = $name;

}

 

// called when parser finds end tag

function endElementHandler($parser, $name)

{

global $values, $currentTag;

// import database link and table name

global $connection, $table;

 

// if ending <item> tag

// implies end of record

if (strtolower($name) == "xmlfield")

{

// generate the query string

$query = "INSERT INTO table ";

$query .= "(sku,value1,value2) ";

$query .= "VALUES(\"" . join("\", \"", $values) . "\");";

 

// uncomment for debug

// print $query;

 

// execute query

$result = mysql_query($query);

 

// reset all internal counters and arrays

$values = array();

$currentTag = "";

}

 

}

 

// called when parser finds cdata

function characterDataHandler($parser, $data)

{

 

global $currentTag, $values, $allowedFields;

// lowercase tag name

$currentTag = strtolower($currentTag);

 

// look for tag in $allowedFields[] array

// to see if it is to be included in query

if (in_array($currentTag, $allowedFields) && trim($data) != "")

{

// add field=>value pairs to $values array

$values[$currentTag] = mysql_escape_string($data);

}

}

 

// initialize parser

$xml_parser = xml_parser_create();

 

// turn off whitespace processing

xml_parser_set_option($xml_parser,XML_OPTION_SKIP_WHITE, TRUE);

// 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

if (!($fp = fopen($xml_file, "r")))

{

die("File I/O error: $xml_file");

}

 

// parse XML

while ($data = fread($fp, 4096))

{

// error handler

if (!xml_parse($xml_parser, $data, feof($fp)))

{

$error_code = xml_get_error_code($xml_parser);

die("XML parser error (error code " . $error_code . "): " . xml_error_string($error_code) . "<br>Error occurred at line " . xml_get_current_line_number($xml_parser));

}

}

 

// all done, clean up!

xml_parser_free($xml_parser);

mysql_close($connection);

 

?>

 

Thank you in advance for your assistance!

 

[patsfans]

Just bumping this.  If anyone might know the answer I'd be forever in your debt

[flyhoney]

Okay this might work, but as I said in my code comment, I dont know what the indices of $values are, so you may need to do a print_r($values) to get the indices.

 

<?php
// initialize some variables
$currentTag = "";

// this array will hold the values for the SQL statement
$values = array();

// this array will hold allowed fields/elements
$allowedFields = array("sku", "value1", "value2");

// XML file to parse
$xml_file="file.xml";

// database parameters
$host = "localhost";
$user = "name";
$pass = "pass";
$db = "db";
$table = "table";

// called when parser finds start tag
function startElementHandler($parser, $name, $attributes)
{
global $currentTag;
$currentTag = $name;
}

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

// if ending <item> tag
// implies end of record
if (strtolower($name) == "xmlfield")
{
	// NOTE: I dont know what the indices of $values are,
	// so you might need to do a debug print_r($values)
	// to find out what they are.
	$sku = $values['sku'];
	$value1 = $values['value1'] ? $values['value1'] : '';
	$value2 = $values['value2'] ? $values['value2'] : '';

	// generate the query string
	$query = "INSERT INTO table ";
	$query .= "(sku,value1,value2) ";
	$query .= "VALUES('$sku', '$value1', '$value2');";

	// uncomment for debug
	// print $query;

	// execute query
	$result = mysql_query($query);

	// reset all internal counters and arrays
	$values = array();
	$currentTag = "";
}

}

// called when parser finds cdata
function characterDataHandler($parser, $data)
{

global $currentTag, $values, $allowedFields;
// lowercase tag name
$currentTag = strtolower($currentTag);

// look for tag in $allowedFields[] array
// to see if it is to be included in query
if (in_array($currentTag, $allowedFields) && trim($data) != "")
{
	// add field=>value pairs to $values array
	$values[$currentTag] = mysql_escape_string($data);
}
}

// initialize parser
$xml_parser = xml_parser_create();

// turn off whitespace processing
xml_parser_set_option($xml_parser,XML_OPTION_SKIP_WHITE, TRUE);
// 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
if (!($fp = fopen($xml_file, "r")))
{
die("File I/O error: $xml_file");
}

// parse XML
while ($data = fread($fp, 4096))
{
// error handler
if (!xml_parse($xml_parser, $data, feof($fp)))
{
	$error_code = xml_get_error_code($xml_parser);
	die("XML parser error (error code " . $error_code . "): " . xml_error_string($error_code) . "<br>Error occurred at line " . xml_get_current_line_number($xml_parser));
}
}

// all done, clean up!
xml_parser_free($xml_parser);
mysql_close($connection);

?>

 

* EDIT *

Nevermind my comment above, Im pretty sure this works.

[patsfans]

It worked.  Thank you so much for your assistance.  I really appreciate it!

 

Ian