XML to MySQL - I've hit a wall :(

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
totallyJ
Forum Newbie
Posts: 3
Joined: Tue Apr 12, 2011 3:15 am

XML to MySQL - I've hit a wall :(

Post by totallyJ »

Hello everyone :)

I've hit a wall with a project my bosses have asked me to do and I'm hoping that someone here may be able to help and guide me in the right direction.

Here is the basics of what needs to be achieved:

Vehicle Tracking

We receive (by way of a remote server into a php input form) XML data about current locations, speed, heading etc of vehicles. The locations are then shown on a page where our customers can check on the progress of their (hopefully imminent) delivery.

An exampe of the XML data is given below. The XML data is received regularly (every five minutes or so) and the amount received is very small (3Kb for instance).

At the other end of the application, I have created a solution using the Google Maps API. This part I can do so no worries there. My idea is that everytime the XML data is received, the data is INSERTED into a MySQL table. The Google Maps API part then does a SELECT on the table and therefore shows the location (and previous locations) of the vehicle(s) in question.

So, I can parse the XML data and I can echo the data on screen (for testing I am simply loading from an XML file - for live, the receiving script will need to receive the data and immediately parse then INSERT into the MySQL database). I can print_r on the contents of the array created also. All good so far I guess?

Before I explain the actual issue, perhaps I should show you the code that I have written so far?

Here is an example of the XML data:
<?xml version="1.0" encoding="UTF-8"?>
<devices xmlns="http://www.maptex.com/tracking/fleet/push/1/0" version="1.0" start="2011-04-11T09:05:00.360Z" end="2011-04-11T09:10:01.227Z">
<device uid="110754" id="4256">
<fixes>
<fix dt="2011-04-11T09:05:49Z" lat="50.5092392" lon="-4.9776478" hd="288" alt="120" spd="63.00" ok="true"/>
<fix dt="2011-04-11T09:06:29Z" lat="50.5096016" lon="-4.9851909" hd="4" alt="140" spd="42.00" ok="true"/>
<fix dt="2011-04-11T09:07:54Z" lat="50.5200462" lon="-4.9873991" hd="316" alt="120" spd="58.00" ok="true"/>
<fix dt="2011-04-11T09:09:11Z" lat="50.5257301" lon="-4.9911861" hd="354" alt="100" spd="0.00" ok="true"/>
</fixes>
</device>
<device uid="11160" id="4288">
<fixes>
<fix dt="2011-04-11T09:06:48Z" lat="50.8222084" lon="-0.9295940" hd="40" alt="20" spd="0.00" ok="true"/>
</fixes>
</device>
<device uid="13230" id="4952">
<fixes>
<fix dt="2011-04-11T09:06:26Z" lat="50.3461227" lon="-5.0281572" hd="26" alt="180" spd="37.00" ok="true"/>
<fix dt="2011-04-11T09:07:13Z" lat="50.3504791" lon="-5.0161052" hd="74" alt="180" spd="95.00" ok="true"/>
</fixes>
</device>
<device uid="14740" id="1801">
<fixes>
<fix dt="2011-04-11T09:08:42Z" lat="51.4687462" lon="-2.7404380" hd="224" alt="80" spd="85.00" ok="true"/>
</fixes>
</device>
<device uid="15530" id="4258">
<fixes>
<fix dt="2011-04-11T09:08:59Z" lat="50.7153282" lon="-2.5257559" hd="256" alt="160" spd="70.00" ok="true"/>
<fix dt="2011-04-11T09:10:04Z" lat="50.7120781" lon="-2.5387640" hd="302" alt="140" spd="50.00" ok="true"/>
</fixes>
</device>
<device uid="15770" id="4257">
<fixes>
<fix dt="2011-04-11T09:08:30Z" lat="50.7071228" lon="-3.3646569" hd="12" alt="160" spd="0.00" ok="true"/>
</fixes>
</device>
<device uid="200498" id="4287">
<fixes>
<fix dt="2011-04-11T09:07:12Z" lat="51.2957916" lon="-2.1984501" hd="264" alt="80" spd="25.00" ok="true"/>
<fix dt="2011-04-11T09:09:32Z" lat="51.2919197" lon="-2.2270401" hd="340" alt="80" spd="32.00" ok="true"/>
</fixes>
</device>
</devices>
And here is the PHP script (such as it is):

Code: Select all

<?php 

	// LOAD THE XML FILE FOR THIS TEST
    if( ! $xml = simplexml_load_file("myxmlfile3.xml") ) { 
    echo "Unable to load XML file"; 
    } 
		else { 
			foreach($xml as $device) { 
			
				echo "<p>Fixes for the device: ";
				$deviceid = $device['id'];
								
				echo "<strong>" . $deviceid . "</strong><br />";
				
					foreach($device->fixes->fix as $fixdt)
						echo "Date/Time: " . ($fixdt['dt']) . "<br />";
						$fixdt = $fixdt['dt'];
					
					foreach($device->fixes->fix as $fixlat)
						echo "Latitude: " . ($fixlat['lat']) . "<br />";
						$fixlat = $fixlat['lat'];
					
					foreach($device->fixes->fix as $fixlon)
						echo "Longtitude: " . ($fixlon['lon']) . "<br />";
						$fixlon = $fixlon['lon'];
					
					foreach($device->fixes->fix as $fixhd)
						echo "Heading: " . ($fixhd['hd']) . "<br />";
						$fixhd = $fixhd['hd'];
						
					foreach($device->fixes->fix as $fixspd)
						echo "Speed: " . ($fixspd['spd']) . "<br />";
						$fixspd = $fixspd['spd'];
						
					foreach($device->fixes->fix as $fixalt)
						echo "Altitide: " . ($fixalt['alt']) . "<br />";
						$fixalt = $fixalt['alt'];
						
					foreach($device->fixes->fix as $fixok)
						echo "Status: " . ($fixok['ok']) . "<br />";
						$fixok = $fixok['ok'];
						
					echo "<strong>End</strong> of device fixes</p>";

					 $sql = "INSERT INTO fixesV4 
					 VALUES('',
						 '$deviceid',
						 '$fixdt',
						 '$fixlat',
						 '$fixlon',
						 '$fixhd',
						 '$fixalt',
						 '$fixspd',
						 '$fixok'
					 )";  
					mysql_query($sql);
					
				}

		}
		
	//SQL - 'fixtable' = THE TABLE HOLDING ALL THE FIXES
		// 'fixtable' STRUCTURE SOMETHING LIKE THIS:
		// 'id' COLUMN IS ESSENTIALLY THE FIX ID
		// -------------------------------------------------------------------------------------------
		// |  id | deviceid |        fixdt        |  fixlat      |  fixlon      |  fixhd   |  fixalt  |  spd  | ok   |
		// -------------------------------------------------------------------------------------------
		// |  1  |   4256   |   2011-04-11T09:05:49Z  |    50.56789   |   -1.2345667   |   145   |    100   |  35   | true |
		// -------------------------------------------------------------------------------------------
		// | etc ...
		
		echo "<pre>"; 
			print_r($xml); 
		echo "</pre>"; 
?>
Here is the issue:

The problem I have is that the INSERT statement is only INSERTING the last of the 'fixes' for devices that have multiple fixes. Each 'fix' is relevant as it contains information regarding the location, speed etc of the vehicle. I've tried the INSERT statement in different places but that either produces an error or doesn't work.

Image

I'm sure I'm missing something stupid here but as I'm a relative beginner that's not surprising!

If there is a way of tweaking what I already have I would be very grateful for any input you might be able to give. I would also love to hear why you suggest a certain way of doing things as I'm keen to learn.

Many thanks for your anticipated help :-)

Kind regards,

Jonathan
totallyJ
Forum Newbie
Posts: 3
Joined: Tue Apr 12, 2011 3:15 am

Re: XML to MySQL - I've hit a wall :(

Post by totallyJ »

Hi all

Was just wondering if anyone had any ideas?

Thanks!

Jonathan
strafingmoose
Forum Newbie
Posts: 15
Joined: Mon Apr 18, 2011 2:56 pm

Re: XML to MySQL - I've hit a wall :(

Post by strafingmoose »

Just to clarify, your for each loop only loops once, and for the last device node ?
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: XML to MySQL - I've hit a wall :(

Post by McInfo »

totallyJ, thank you for explaining your problem in proper English and for using syntax tags.

I want you to gain some knowledge from this and not just a solution, so I have crippled it in some minor ways that I hope will force you to figure out what is happening.

Code: Select all

<?php
if ($xml = simplexml_load_file('sample.xml')) {
    $sql = "INSERT INTO fixes VALUES \n";
    $count = 0;
    foreach ($xml->device as $device) {
        list ($uid) = $device->attributes();
        foreach ($device->fixes->fix as $fix) {
            list ($dt, $lat, $lon, $hd, $alt, $spd, $ok) = $fix->attributes();
            $sql .= sprintf("(NULL, %u, '%s', %f, %f, %d, %d, %f, '%s'),\n", $uid, $dt, $lat, $lon, $hd, $alt, $spd, $ok);
            ++$count;
        }
    }
    $sql = substr($sql, 0, -2); // Removes comma and newline
    echo "<pre>$sql</pre>";
    if ($count > 0) {
        // Run query
    }
}
totallyJ
Forum Newbie
Posts: 3
Joined: Tue Apr 12, 2011 3:15 am

Re: XML to MySQL - I've hit a wall :(

Post by totallyJ »

Thank you both for your answers! I'm going to study the code provided now. And yes you are right I need to learn rather than have handed to me some ready made code :-)

"Feed a man a fish and you feed him for a day, teach a man to fish and you feed him for life"

Thanks again. I will come back later if I need clarification on any points.

Kind regards,

Jonathan
Gopesh
Forum Contributor
Posts: 143
Joined: Fri Dec 24, 2010 12:48 am
Location: India

Re: XML to MySQL - I've hit a wall :(

Post by Gopesh »

hi, Refer this link http://www.w3schools.com/PHP/php_xml_simplexml.asp to get about xml in php.....
Post Reply