473,372 Members | 854 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

PHP to read XML file and export data to MySQL

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
5 17252
ronverdonk
4,258 Expert 4TB
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
seddy
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
4,258 Expert 4TB
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
seddy
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
4,258 Expert 4TB
Any time.

Ronald :cool:
Oct 24 '06 #6

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

Similar topics

2
by: Joe Gazda | last post by:
I'm a relative newbie to PHP, but have been able to put together some PHP code to generate a CSV/XLS file from a Query result. Now, I would like to include custom column names instead of the MySQL...
1
by: Andy | last post by:
I have a mysql db and running on RH linux , how can I export the data from the mysql db on the shell ? thx.
4
by: news | last post by:
Our production database in an exported textfil runs about 60 MB. Compressed that's about 9 MB. I'm trying to import the export into another machine running FC3 and mySQL 11.18, and it appears as...
1
by: Ward B | last post by:
Greetings. I'm somewhat new to this whole MySQL/PHP thing and need a little help. My web hosting service uses phpMyAdmin and at the bottom of the screen iis an area where I can upload a text...
7
by: phillip.s.powell | last post by:
We're looking at a GUI interface for our MySQL DB and I am interested in MySQL Administrator, however, one of our requirements is to be able to import/export databases. Is this possible or do I...
8
by: Declan Barry | last post by:
Hi all.. Does anyone have a php script that would allow me to encrypt the contents of a txt file? I have an excel file which has a list of usernames and generated passwords. What I would...
7
by: eselk | last post by:
I'm doing some speed tests. I created a brand-new table, with just one "Long Integer" field. I'm testing adding 1000 records. If I use the "Export" feature in Access, it takes only a few seconds...
0
by: lanesbalik | last post by:
hi all, right now i'm trying to migrate from db2 running under linux to mysql v5.1. i manage to export out the db2 structure & data into a del (ascii) file. but when i try to load the data...
1
by: milas57 | last post by:
hello everyone im new to php and mysql i been trying to export mysql data in an xml file my code below i have problem picking data in the database to display in the xml file, the xml file only...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.