Importing Large XML into PHP db, looking to create progress bar

[patsfans]

I have a site where I have an admin panel set up to import an XML data feed into a database, but it's a large import and it takes a lot of time to update.  I'm looking to upgrade the admin panel and offer the ability to be able to see the number of records as they're being imported instead of it hanging there until the query is completed.  The tough part about a progress bar is obviously you have to know the # of items being imported beforehand, and that varies each time the feed is downloaded.  So I'm just trying to get some suggestions from someone who may have written something similar.

 

Thanks in advance!

[patsfans]

Sorry - I realized it would probably be a little more helpful to show you an example of the import script I'm working with:

 

<?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("val1", "val2", "val3");

// XML file to parse
$xml_file="http://www.mysite.com/xml/Product-Data.xml";

// database parameters
$host = "localhost";
$user = "username";
$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) == "tablecorefields")
{
	// 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.

	$val1 = $values['val1'] ? $values['val1'] : '';
	$val2 = $values['val2'] ? $values['val2'] : '';
	$val3 = $values['val3'] ? $values['val3'] : '';

	// generate the query string
	$query = "INSERT INTO TABLE ";
	$query .= "(val1, val2, val3) ";
	$query .= "VALUES('$val1', '$val2', '$val3');";

	// uncomment for debug
	// print $query;

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

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

?>

 

This works fine to import the .xml data, I'm just trying to make the interface a little cleaner and wanted to get a suggestion on how to show the progress as the import takes place.

 

Thank you in advance

[xyph]

You'd have to call the script using AJAX, and have some sort of counter (via SQL, flat file, session, etc) that tracks the total rows to be inserted, and the rows already inserted.

 

You'd have a second script, called by AJAX every second, half second, however fast you want the progress bar to update. This second script would read the counter set by the other script, and return the output back. You'd parse these results, and insert them into the DOM via JavaScript.

[patsfans]

You'd have to call the script using AJAX, and have some sort of counter (via SQL, flat file, session, etc) that tracks the total rows to be inserted, and the rows already inserted.

 

You'd have a second script, called by AJAX every second, half second, however fast you want the progress bar to update. This second script would read the counter set by the other script, and return the output back. You'd parse these results, and insert them into the DOM via JavaScript.

 

Since I'm not overly familiar with AJAX I did some digging since you posted the reply last night and didn't spot anything looked like it would work.  As a result I thought I'd check to see if you had any good suggestions looking at the supplied script to potentially point me in the right direction.  I appreciate your assistance, and thanks for taking the time to help me out.

[harristweed]

I did this some time ago and it seemed to work!

 

set $i=0 outside of the 'loop'

 

then have this code inside the 'loop'

$i++;    
$nl=$i % 100;
if($nl==0)echo"<br />$i Records processed<br />";
echo"|";
    ob_flush(); flush();

[xyph]

There is lots of odd behaviour involved with trying to flush the output while the script is executing. You should check out the user comments here:

 

http://www.php.net/manual/en/function.ob-flush.php#100462

[harristweed]

Well, as I said, it worked for me.