I have several data sources that I'm wanting to parse and import into the same SQL table, so I modified the default files. But now I'm getting a blank row at the end of each file. I suspect it's because I commented out the TRUNCATE, but before I did that, it would delete everything already entered. Where do I need to move it to?
I'm also getting a count one higher than the number of entries, which sounds related to the blank row.
Here's my code:
<?php
set_time_limit(0);
require("test_database.inc.php");
require("MagicParser.php");
$sql = "
DROP TABLE IF EXISTS `table`
";
database_queryModify($sql,$result);
$sql = "
CREATE TABLE `table`
(
id INT(15) UNSIGNED NOT NULL default 0,
field1 VARCHAR(100) NOT NULL default '',
field2 VARCHAR(100) NOT NULL default '',
PRIMARY KEY (id)
)
ENGINE=MyISAM;
";
database_queryModify($sql,$result);
print "Done. Table created.";
$arr = array("http://[url]",
"http://[url2]",
"http://[url3]",
"http://[url4]",
"http://[url5]",
"localfile.xml");
function myRecordHandler($record)
{
global $count;
$sql = "
INSERT INTO `table` SET
id = '".database_safe($record["ID"])."',
field1 = '".database_safe($record["FIELD1"])."',
field2 = '".database_safe($record["FIELD2"])."'
"; // no comma after last ------------------------------------------^
database_queryModify($sql,$result);
$count++;
}
function myRecordHandler2($record)
{
global $count;
$sql = "
INSERT INTO `table` SET
id = '".database_safe($record["OTHERID"])."',
field1 = '".database_safe($record["OTHERFIELD1"])."',
field2 = '".database_safe($record["OTHERFIELD2"])."'
"; // no comma after last ------------------------------------------^
database_queryModify($sql,$result);
$count++;
}
foreach ($arr as $filename) {
$count = 0;
if ($filename != "localfile.xml") {
myRecordHandler($record);
}
else {
myRecordHandler2($record);
}
// $sql = "TRUNCATE `table`";
// database_queryModify($sql,$result);
if ($filename != "localfile.xml") {
if (!MagicParser_parse($filename,"myRecordHandler","xml|LIST/ITEM/")) {
die("Could not open ".$filename);
}
}
else {
if (!MagicParser_parse($filename,"myRecordHandler2","xml|ROOT/ROW/")) {
die("Could not open ".$filename);
}
}
print "Done. ".$count." records imported.";
}
?>
Hello Lera,
With CSV parsing, it's possible to get a "null" line if there is a carriage return on the end of the last line, however this should not be the case with XML if generated cleanly, but there is always the possibility of course that the process creating the XML has inadvertently created a "null" entry itself.
One thing to do would be to check $record for a valid ID field in your record handler functions before going on to construct the SQL / insert into the database. I've added this in the following example:
<?phpset_time_limit(0);
require("test_database.inc.php");
require("MagicParser.php");
$sql = "
DROP TABLE IF EXISTS `table`
";
database_queryModify($sql,$result);
$sql = "
CREATE TABLE `table`
(
id INT(15) UNSIGNED NOT NULL default 0,
field1 VARCHAR(100) NOT NULL default '',
field2 VARCHAR(100) NOT NULL default '',
PRIMARY KEY (id)
)
ENGINE=MyISAM;
";
database_queryModify($sql,$result);
print "Done. Table created.";
$arr = array("http://[url]",
"http://[url2]",
"http://[url3]",
"http://[url4]",
"http://[url5]",
"localfile.xml");
function myRecordHandler($record)
{
global $count;
if ($record["ID"]) return;
$sql = "
INSERT INTO `table` SET
id = '".database_safe($record["ID"])."',
field1 = '".database_safe($record["FIELD1"])."',
field2 = '".database_safe($record["FIELD2"])."'
"; // no comma after last ------------------------------------------^
database_queryModify($sql,$result);
$count++;
}
function myRecordHandler2($record)
{
global $count;
if ($record["OTHERID"]) return;
$sql = "
INSERT INTO `table` SET
id = '".database_safe($record["OTHERID"])."',
field1 = '".database_safe($record["OTHERFIELD1"])."',
field2 = '".database_safe($record["OTHERFIELD2"])."'
"; // no comma after last ------------------------------------------^
database_queryModify($sql,$result);
$count++;
}
foreach ($arr as $filename) {
$count = 0;
if ($filename != "localfile.xml") {
myRecordHandler($record);
}
else {
myRecordHandler2($record);
}
// $sql = "TRUNCATE `table`";
// database_queryModify($sql,$result);
if ($filename != "localfile.xml") {
if (!MagicParser_parse($filename,"myRecordHandler","xml|LIST/ITEM/")) {
die("Could not open ".$filename);
}
}
else {
if (!MagicParser_parse($filename,"myRecordHandler2","xml|ROOT/ROW/")) {
die("Could not open ".$filename);
}
}
print "Done. ".$count." records imported.";
}
?>
...see the new lines:
if ($record["ID"]) return;and
if ($record["OTHERID"]) return;...which should do the trick...
Cheers,
David
--
MagicParser.com