Jump to content

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


patsfans

Recommended Posts

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!

Link to comment
Share on other sites

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 :)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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. :)

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.