SimpleXML to MySQL

[mojomystery]

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 January 18, 2013 by mojomystery

[requinix]

You have to get the values of $iterator and such with $unit->iterator, not $unitlist->unit->iterator.

[Barand]

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)

[mojomystery]

@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 January 21, 2013 by mojomystery

[Barand]

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 January 21, 2013 by Barand

[mojomystery]

Thanks again!

 

- dan -

[beartooth]

On a related note, what about a scenario where you have sub-elements that you want to capture and add into the data array as well?

 

Thanks!