?
Solved

XML to MySQL using PHP

Posted on 2009-05-19
11
Medium Priority
?
391 Views
Last Modified: 2013-12-12
Hello, I need to import XML data to a MySql database. The XML follows this pattern:

<hotel>
  <hotel_ref>12345</hotel_ref>
  <hotel_name>The Budget Hotel</hotel_name>
  <hotel_star>5</hotel_star>
  <hotel_address>50-60 Main Road</hotel_address>
  <hotel_city>London</hotel_city>
  <hotel_county>Greater London</hotel_county>
  <hotel_pcode>NW8 1RF</hotel_pcode>
  <hotel_description>Lorem ipsum dolor sit amet, consectetur adipiscing elit. Quisque quis eros sapien, id lobortis nunc. Lorem ipsum dolor sit amet, consectetur adipiscing elit.</hotel_description>
  <hotel_directions>Sed a dolor in orci tincidunt luctus. Aliquam erat volutpat. Praesent nec magna ac mauris euismod rutrum non eget mauris. </hotel_directions>
  <hotel_link>http://www.example.com</hotel_link>
  <hotel_rooms>201</hotel_rooms>
  <images>http://www.example.com/test.jpg</images>
- <geo_code>
  <long>-0.12173</long>
  <lat>51.51998</lat>
  </geo_code>
  <hotel_distance>0.8718</hotel_distance>
  <rating>5</customer_rating>
  <prices_from>100.00</prices_from>
  <star>Lorem Ipsem</star>
  <accomodation_type>Hotel</accomodation_type>
  <rack_rate>300.00</rack_rate>
</hotel>

 Using some recycled code (from a solution elsewhere on this site) I can get most of the data into my database, but NOT the data in the <geo_code></geo_code> nodes - which i need to go in corresponding fields in MySQL latitude and longitude.

Could somebody please help me with a pointer on how to extract that data?

Here's the code i'm using....

Thanks!


<?php
 
ini_set('display_errors', 1);   # enable PHP error reporting
error_reporting(E_ALL);
 
 
 mysql_connect("localhost", "root", "") or die(mysql_error());
 
mysql_select_db("xmltest") or die(mysql_error());
 
$xmlfile = "hotels.xml";
 
$dom = new DOMDocument;
$dom->load($xmlfile);
 
$items = $dom->getElementsByTagName('hotel');  # find all <hotel> elements
foreach($items as $item) {
  $record = array();
  foreach($item->childNodes as $child) {
    if($child->nodeType == XML_ELEMENT_NODE)  # elements within each <hotel>
      $record[] = $child->nodeName." = '".
        mysql_real_escape_string(trim($child->nodeValue))."'";
  }
  echo '<pre>insert into hotels set '.implode(",\n  ",$record).'</pre>';
}
 
?>

Open in new window

0
Comment
Question by:ogdini
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 10

Expert Comment

by:iamanindian
ID: 24421868
Do you want to use script or tools? You may use Navicat to do the xml import - which is a very efficient way of doing. Script is definitely a good option - but then we need to spend time in checking the code. :(
0
 

Author Comment

by:ogdini
ID: 24422639

Hello iamanindian,

I need to use a PHP script as this is part of a bigger application.

Thanks for the Navicat suggestion I have looked at that before.

Howard
0
 

Author Comment

by:ogdini
ID: 24422683
This is currently the output from the code above:

hotel_ref = 12345
hotel_name = The Budget Hotel
hotel_star = 5
hotel_address = 50-60 Main Road
hotel_city = London
hotel_county = Greater London
hotel_pcode = NW8 1RF
hotel_description = Lorem ipsum dolor sit amet, consectetur adipiscing elit. Quisque quis eros sapien, id lobortis nunc. Lorem ipsum dolor sit amet, consectetur adipiscing elit.
hotel_directions = USed a dolor in orci tincidunt luctus. Aliquam erat volutpat. Praesent nec magna ac mauris euismod rutrum non eget mauris.
hotel_link = http://www.example.com 
hotel_rooms = 201
images = http://www.example.com/test.jpg
geo_code =
hotel_distance = 0.8718
rating = 5
prices_from = 100.00
star = Lorem Ipsem
accomodation_type = Hotel
rack_rate = 300.00
0
Are You Using the Best Web Development Editor?

The worlds of web hosting and web development are constantly evolving. Every year we see design trends change, coding standards adapt and new frameworks/CMS created. With such a quick pace of change it’s easy to get lost trying to keep up.

See if your editor made the list.

 
LVL 111

Expert Comment

by:Ray Paseur
ID: 24423237
I'll take a look at this, but before I do, let me make a comment about the geocode.  A geocode is a two field, comma-separated string with ALWAYS latitude first, and longitude last.  So while the XML will isolate it correctly, there is a small risk that future coding would not work as expected.

OK
<geo_code>
  <long>-0.12173</long>
  <lat>51.51998</lat>
  </geo_code>

WRONG
$geocode = "-0.12173,51.51998";
0
 

Author Comment

by:ogdini
ID: 24423308
Hello Ray,
Thanks for looking into this for me :)
In response to your point about geocode formatting for me it is not an issue on this occasion because, instead of extracting them as a comma separated string, i would prefer to have them as 2 separate entities so that I can put them into a database field each (Latitude and Longitude)
Kind regards,
Howard
0
 

Author Comment

by:ogdini
ID: 24423332

So, ideally my output from the script would be like this:


hotel_ref = 12345
hotel_name = The Budget Hotel
hotel_star = 5
hotel_address = 50-60 Main Road
hotel_city = London
hotel_county = Greater London
hotel_pcode = NW8 1RF
hotel_description = Lorem ipsum dolor sit amet, consectetur adipiscing elit. Quisque quis eros sapien, id lobortis nunc. Lorem ipsum dolor sit amet, consectetur adipiscing elit.
hotel_directions = USed a dolor in orci tincidunt luctus. Aliquam erat volutpat. Praesent nec magna ac mauris euismod rutrum non eget mauris.
hotel_link = http://www.example.com 
hotel_rooms = 201
images = http://www.example.com/test.jpg
latitude = 51.51998
longitude = -0.12173
hotel_distance = 0.8718
rating = 5
prices_from = 100.00
star = Lorem Ipsem
accomodation_type = Hotel
rack_rate = 300.00
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 24423337
10-4.  Just be sure if you present these values to a mapping service as a geocode that you have them in the right order.

RIGHT
$geocode = "51.51998,-0.12173";

Example:
http://maps.google.com/maps?f=q&source=s_q&hl=en&geocode=&q=51.51998,-0.12173&sll=38.93877,-77.096736&sspn=0.107618,0.2314&ie=UTF8&z=16&iwloc=A
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 24423718

<?php // RAY_xml_to_mysql.php
error_reporting(E_ALL);
echo "<pre>";
 
// TEST DATA FROM THE OP
$xml = "
<hotel>
  <hotel_ref>12345</hotel_ref>
  <hotel_name>The Budget Hotel</hotel_name>
  <hotel_star>5</hotel_star>
  <hotel_address>50-60 Main Road</hotel_address>
  <hotel_city>London</hotel_city>
  <hotel_county>Greater London</hotel_county>
  <hotel_pcode>NW8 1RF</hotel_pcode>
  <hotel_description>Lorem ipsum dolor sit amet, consectetur adipiscing elit. Quisque quis eros sapien, id lobortis nunc. Lorem ipsum dolor sit amet, consectetur adipiscing elit.</hotel_description>
  <hotel_directions>Sed a dolor in orci tincidunt luctus. Aliquam erat volutpat. Praesent nec magna ac mauris euismod rutrum non eget mauris. </hotel_directions>
  <hotel_link>http://www.example.com</hotel_link>
  <hotel_rooms>201</hotel_rooms>
  <images>http://www.example.com/test.jpg</images>
 <geo_code>
  <long>-0.12173</long>
  <lat>51.51998</lat>
 </geo_code>
  <hotel_distance>0.8718</hotel_distance>
  <customer_rating>5</customer_rating>
  <prices_from>100.00</prices_from>
  <star>Lorem Ipsem</star>
  <accomodation_type>Hotel</accomodation_type>
  <rack_rate>300.00</rack_rate>
</hotel>
";
 
// CREATE AN OBJECT
$obj = SimpleXML_Load_String($xml);
 
// VISUALIZE THE ENTIRE OBJECT
// var_dump($obj);
 
 
 
// BUILD THE QUERY ARRAY HERE
$query = array();
 
// PLUCK OUT THE GEOCODE DATA
$geocode_data = $obj->geo_code;
$query["latitude"]  = (string) $geocode_data->lat;
$query["longitude"] = (string) $geocode_data->long;
unset($obj->geo_code);
 
foreach ($obj as $k => $v)
{
   $query["$k"] = (string) $v; // ADD mysql_real_escape_string() FUNCTION HERE
}
 
// VISUALIZE THE QUERY ARRAY
// var_dump($query);
 
// CONSTRUCT THE SQL
$sql = 'INSERT INTO hotels SET ';
foreach ($query as $k => $v)
{
    $sql .= "`$k` = \"$v\", ";
}
$sql = rtrim($sql, ', ');
 
var_dump($sql);

Open in new window

0
 

Author Comment

by:ogdini
ID: 24427031
Thanks Ray :)
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 24427045
Thanks for the points!  As you can see, I like SimpleXML much better than DOMDocument.  FWIW, I like RESTful services better than SOAP, too.  But that's a separate issue...

I had to change this from the OP:
<customer_rating>5</customer_rating>

Other than that, it's pretty faithful to the example.  Best of luck with it, ~Ray
0
 

Author Comment

by:ogdini
ID: 24427077

Sorry about the error in the OP :(

I like simpleXML too.. i haven;t got my head around DOM at all yet.

Thanks again and best wishes.

Howard
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question