By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,173 Members | 603 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,173 IT Pros & Developers. It's quick & easy.

PHP to read XML file and export data to MySQL

P: 3
Hello !
I`m kinda new to it so I found this `job` very hard therefor I ask for Your help.

So, the thing is...
I have this XML file ( http://www.izishop.net/export.php )
which I need to open with php and read it, and insert some fields those fields on my MySQL database.

Well... i found some usefull informations which might help me here:
(http://www.thescripts.com/forum/thread2005.html)
Take that data

<?php
$data = "<?xml version=\"1.0\"?>
<sqldata>
<record><name>Pedro</name><eyecolor>brown</eyecolor></record>
<record><name>Muppy</name><eyecolor>blue</eyecolor></record>
</sqldata>";
?>

and get all the records into an array

<?php
preg_match_all('#<record>(.*)</record>#Us', $data, $records);
?>

then, for each record, get the respective values, and insert into sql
I am treating all values as strings (quoting them with ')
you might want to test the column names and do it differently

<?php
foreach ($records[1] as $record) {
preg_match_all('#<(.*)>(.*)</\1>#Us', $record, $values);
// $values[1] has the column names
// $values[2] has the values to insert
$sql = "insert into table ("
. implode(', ', $values[1])
. ") values ('"
. implode("', '", $values[2])
. "')";
echo $sql, '<br />'; ### or mysql_query($sql)
}
?>

This might go, but how do i set this data to be read from http://www.izishop.net/export.php ?

Afterward I need to update table if item already exist in my database.

I really don`t know how to do it, so any help will be appriciated.
Oct 24 '06 #1
Share this Question
Share on Google+
5 Replies

ronverdonk
Expert 2.5K+
P: 4,258
The code you posted does NOT work on the file you want to use. It was a very simple sample for just non-nested XML. You did not read the entry in that same forum thread that warned:
You can't parse XML fully with just regexps. Use an XML parser:
And I agree with Andy, there are plenty XML parsers around, if you have PHP5 you can use SimpleXML, see SimpleXML
As for reading the file, you can do something like [php]$xmlstring = file_get_contents("filename");[/php] that reads the entire file into the variable $xmlstring.

Ronald :cool:
Oct 24 '06 #2

P: 3
The code you posted does NOT work on the file you want to use. It was a very simple sample for just non-nested XML. You did not read the entry in that same forum thread that warned:And I agree with Andy, there are plenty XML parsers around, if you have PHP5 you can use SimpleXML, see SimpleXML
As for reading the file, you can do something like [php]$xmlstring = file_get_contents("filename");[/php] that reads the entire file into the variable $xmlstring.

Ronald :cool:
Thank you. That is indeed the way to read the content :)
I did what you said... I now used SAX parter and came to this:

$xfile = "http://www.izishop.net/export.php";
$xparser=xml_parser_create();
xml_set_element_handler($xparser, "startingHandler", "endingHandler");
xml_set_character_data_handler($xparser, "cdataHandler");

if(!($fp=fopen($xfile,"r")))
{
die ("File does not exist");
}

while($data=fread($fp, 4096))
{
if(!xml_parse($xparser,$data,feof($fp)))
{
die("XML parse error: xml_error_string(xml_get_error_code($xparser))");
}
}

xml_parser_free($xml_parser);

function startingHandler($xparser, $element_name, $attributes)
{
echo "Opening Tag:<b>$element_name</b><br>";
while (list($key,$value)=each($attributes))
{
echo "Attribute:<b><i>$key=$value</i></b><br>";
}
}

function endingHandler($xparser, $element_name)
{
echo "Closing Tag:<b>$element_name</b><br>";
}

function cdataHandler($xparser, $cdata)
{
echo "CDATA: <i><u>$cdata</u></i><br>";
}
?>

This replays this:
http://krebs.si/xml/test2.php

So what I want to do now is to insert some of those atributes ( id, link, name, brand... ) into mysql database.

Thank you.
Oct 24 '06 #3

ronverdonk
Expert 2.5K+
P: 4,258
Next time you post any sort of code in this forum enclose it within php or code tags. See the Posting Guidelines at the top of this forum or the Reply Guidelines at the right hand side of the reply message screen.
Code, the way you posted it, is almost unreadable on the screen and does not encourage members to read it. I certainly will not give it more then a glance.

Ronald :cool:
Oct 24 '06 #4

P: 3
Of course

Expand|Select|Wrap|Line Numbers
  1. <?php
  2. $xfile = "http://www.izishop.net/export.php";
  3. $xparser=xml_parser_create();
  4. xml_set_element_handler($xparser, "startingHandler", "endingHandler");
  5. xml_set_character_data_handler($xparser, "cdataHandler");
  6.  
  7. if(!($fp=fopen($xfile,"r")))
  8. {
  9. die ("File does not exist");
  10. }
  11.  
  12. while($data=fread($fp, 4096))
  13. {
  14. if(!xml_parse($xparser,$data,feof($fp)))
  15. {
  16. die("XML parse error: xml_error_string(xml_get_error_code($xparser))");
  17. }
  18. }
  19.  
  20. xml_parser_free($xml_parser);
  21.  
  22. function startingHandler($xparser, $element_name, $attributes)
  23. {
  24. echo "Opening Tag:<b>$element_name</b><br>";
  25. while (list($key,$value)=each($attributes))
  26. {
  27. echo "Attribute:<b><i>$key=$value</i></b><br>";
  28. }
  29. }
  30.  
  31. function endingHandler($xparser, $element_name)
  32. {
  33. echo "Closing Tag:<b>$element_name</b><br>";
  34. }
  35.  
  36. function cdataHandler($xparser, $cdata)
  37. {
  38. echo "CDATA: <i><u>$cdata</u></i><br>";
  39. }
  40. ?>
  41.  
So this replays this:
http://www.krebs.si/xml/test2.php

This tells me i`m going into right direction :)
Next thing to do is to insert some of those atributes into MySQL dabase fields.
So for each <item> < xml`s opening tag i must pick out propher atributes values and insert them into mysql.
But If item is already in my database I only need to update the record in database ( for let`s say if the price of the item changes ).

Thank you, I appreciate your help.
Oct 24 '06 #5

ronverdonk
Expert 2.5K+
P: 4,258
Any time.

Ronald :cool:
Oct 24 '06 #6

Post your reply

Sign in to post your reply or Sign up for a free account.