PHP to read XML file and export data to MySQL

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.

re: PHP to read XML file and export data to MySQL

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:

re: PHP to read XML file and export data to MySQL

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.

re: PHP to read XML file and export data to MySQL

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:

re: PHP to read XML file and export data to MySQL

Of course

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.

re: PHP to read XML file and export data to MySQL

Any time.

Ronald :cool: