parsing xml and insert data in mysql table

Question

I am parsing an xml file using php simplexml_load_file() and then inserting the desired data in a mysql ISAM table. The problem is that the code works "most" of the times, with 500 internal server errors here and there. The XML file that I am trying to process is big (around 50 MB), and it yields around 25000 rows in the mysql table when it works. When I get the error, the script inserts anything from a few rows to a few thousand rows.

Anyway, here is the code, I would appreciate it if someone has any insight into this, or has an alternative way, I don't know, maybe batch processing or something like that.

<?php
include ("myconnection.php");

mysql_query("DELETE FROM mytable") or die(mysql_error());   

echo "data deleted, now insert: <br /><br />";

//the url for the feed:
$feed = 'cachy/copy.xml';


echo "myfeed: ".$feed;
echo "<br />";

// Load the feed

$xml = simplexml_load_file($feed);
if ($xml != null)
{
echo "<br />Success! feed available!<br /><br />";
}
else
{ 
echo "<br />Couldn't fetch the content<br /><br />";
die;
}

//die;
//ini_set("memory_limit","256M");
//set_time_limit(120);


function clean($input)
{
   $input = trim($input);  
   $input = htmlentities($input, ENT_QUOTES);
   $input = mysql_escape_string($input);
   $input = EscapeShellCmd($input);
   return $input;  
}


//insert data from feed
foreach($xml->xpath('//product') as $products)

{

$product_name = clean($products->product_name);

mysql_unbuffered_query("INSERT INTO mytable (onsaledate, onsaletime, eventdate, eventtime, buyat_short_deeplink_url, product_name, level1, level2, VenueName, VenueDMAID)VALUES (\"$products->OnsaleDate\",\"$products->OnsaleTime\",\"$products->EventDate\",\"$products->EventTime\",\"$products->buyat_short_deeplink_url\",\"$product_name\",\"$products->level1\",\"$products->level2\",\"$products->VenueName\",\"$products->VenueDMAID\")") or die(mysql_error());


}

mysql_close($myConnection);  

echo "records inserted my man!";  

die;

Thanks in advance for your help!

Greg · Accepted Answer · 2009-08-12 08:06:45Z

2

What error do you get?

Also, it will run much faster if you:

Replace DELETE FROM table with TRUNCATE table (assuming you can re-use IDs)
Insert multiple rows at once, like this

INSERT INTO table (col1, col2) VALUES (val1, val2), (val1a, val1b), ...

answered Aug 12, 2009 at 8:06

Greg

323k55 gold badges378 silver badges338 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gene Over a year ago

I have changed the delete to truncate, it looks like it might be a tad faster. How would I build your suggested insert in my loop? The Error that I get is this: Internal Server Error The server encountered an internal error or misconfiguration and was unable to complete your request. Not very descriptive

Gene Over a year ago

Step 2 was the crucial one. Thanks!

agvstin · Accepted Answer · 2009-08-12 13:06:08Z

1

Looks like your php is not showing all errors. Try adding this:

error_reporting(E_ALL);

This should print all errors. Xslt (@Greg) is a good option.

answered Aug 12, 2009 at 13:06

agvstin

2262 silver badges5 bronze badges

Comments

Josh · Accepted Answer · 2009-08-12 08:09:26Z

0

i'm not sure if it is more efficient to set the xpath to a variable before starting you loop.

eg

$productsxml = $xml->xpath('//product')
foreach($productsxml as $products)

you could also use xslt to create a statement like @Greg has suggested and execute it at once.

answered Aug 12, 2009 at 8:09

Josh

6,2872 gold badges39 silver badges56 bronze badges

Collectives™ on Stack Overflow

parsing xml and insert data in mysql table

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related