Jump to content

SimpleXML to MySQL


mojomystery

Recommended Posts

First, thanks in advance for any help.

Here's my issue.

I've read through tons of similar problems here but I don't think I'm understanding this enough to make this work.

 

I have an XML file such as:

 

 

 


<unitlist>
<unit>
<iterator>0</iterator>
<route>2</route>
<rt_name>'Riverfront Streetcar'</rt_name>
<name>457</name>
<lat>2956.21014</lat>
<lng>-9004.62839</lng>
<bearing>2.14501</bearing>
<car_type>'RTA Street Cars'</car_type>
<speed>16.57122</speed>
<GMT>043432</GMT>[/indent]
</unit>


<unit>
<iterator>1</iterator>
<route>10</route>
<rt_name>'Tchoupitoulas'</rt_name>
<name>250</name>
<lat>2958.0927</lat>
<lng>-9005.28724</lng>
<bearing>1.532399</bearing>
<car_type>'RTA Buses'</car_type>
<speed>0.6904677</speed>
<GMT>104620</GMT>[/indent]
</unit>
</unitlist>


 

What I want to do is, for each <unit>, import all attributes of that unit (iterator,route,rt_name, etc, etc, etc) into my mySQL database. My problem is that, all it's doing is importing the same attributes of the first <unit> the number of times that <unit> exists. In this example, it's importing the same information from the first <unit> twice, as two records/rows. If I had 90 <unit>s, it would import 90 identical records.

 

Here's my code:

 

foreach($unitlist->unit as $unit) {
//insert into database
mysql_query("INSERT INTO businfo (iterator,route,rt_name,name,lat,lng,bearing,car_type,speed,GMT)VALUES (\"$iterator\",\"$route\", \"$rt_name\", \"$name\", \"$lat\", \"$lng\", \"$bearing\", \"$car_type\", \"$speed\", \"$GMT\")")
or die(mysql_error());

}

 

Can anyone explain what I'm doing wrong?

 

Thanks again for the help!

 

- dan -

Edited by mojomystery
Link to comment
Share on other sites

try

<?php

$xml = simplexml_load_file('units.xml');
$data = $fields = array();
foreach ($xml->xpath('unit') as $unit) {
   $fields = array_keys((array)($unit));
   $data[] = '(' . join(', ', (array)$unit) . ')';
}

$sql = "INSERT INTO businfo (" . join(', ', $fields) . ") VALUES\n" ;
$sql .= join (",\n", $data);

echo "<pre>$sql</pre>";
?>


RESULT

INSERT INTO businfo (iterator, route, rt_name, name, lat, lng, bearing, car_type, speed, GMT) VALUES
(0, 2, 'Riverfront Streetcar', 457, 2956.21014, -9004.62839, 2.14501, 'RTA Street Cars', 16.57122, 043432),
(1, 10, 'Tchoupitoulas', 250, 2958.0927, -9005.28724, 1.532399, 'RTA Buses', 0.6904677, 104620)

Link to comment
Share on other sites

@Barand,

 

Thank you very much. I think this produces exactly what I need.

In my first test, however, I noticed that this company's XML file is prone to bad formatting which breaks the query. For example, their latest version contains this:

 

 

<unit>
<iterator>35</iterator>
<route>80</route>
<rt_name>'Louisa'</rt_name>
<name>173</name>
<lat>2900</lat>
<lng>-9000</lng>
<bearing>-1</bearing>
<car_type>'RTA Buses'</car_type>
<speed>-1</speed>
<GMT/>[/size][/font][/color]
</unit>

 

Notice how they are missing a <GMT>? This breaks the query and it won't execute when it hits that line. Is there any way to account for mistakes like this so it doesn't break?

 

Thanks again for your help and sorry for the delay in responding. Today is the first day I've been able to get back to working on this.

 

- dan -

Edited by mojomystery
Link to comment
Share on other sites

The formatting is correct where there is no value for an element

 

<GMT /> is equivalent to <GMT></GMT>

 

You could do something like this

 

<?php

$xml = simplexml_load_file('units.xml');
$data = $fields = array();
foreach ($xml->xpath('unit') as $unit) {
 $fields = array_keys((array)($unit));
 if ($unit->GMT == '') $unit->GMT = 0;			 // check for no value
 $data[] = '(' . join(', ', (array)$unit) . ')';
}

$sql = "INSERT INTO businfo (" . join(', ', $fields) . ") VALUES\n" ;
$sql .= join (",\n", $data);

echo "<pre>$sql</pre>";
?>

Edited by Barand
Link to comment
Share on other sites

  • 11 months later...
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.