PHP Code - import xml file from web server (/publi

Question

I have xml files been imported onto an FTP server. This is stored in location '/public_html/ctrackxml/' with a random file name and in the following format:

<message type="POSITIONDATA">
  <messageid>-1</messageid>
  <mobile>SNK261GP</mobile>
  <time>2012/01/20 08:34:45 AM</time>
  <latitude>-29.8477</latitude>
  <longitude>30.9554</longitude>
  <status>Driving</status>
  <speed>82</speed>
  <address> near Outer Ring Road (N2); Umkumbaan; in Durban</address>
  <direction>
  </direction>
  <runningodo>1587000</runningodo>
</message>

I need to loop through all the files in the folder and import each file into the MySQL database table xmldata which has the following structure:

I need each tag in the xml file to be imported into a seperate field in the table. so each xml file represent one table entry.

From the research I have done it looks like I need to use the 'LOAD XML LOCAL INFILE' mysql syntax however I cant seem to get this to work correctly directly within mysql.

If you could point me in the right direction I would greatly appreciate it.

Update

below is the code that I hav emanaged to scrape together with the assistance of another website. http://www.phpfreaks.com/forums/index.php?topic=244744.0

I tested the script from phpfreaks and it works 100% however the xml structure is quite different. I have tried to modify the code to suite my xml file but am having some issues to get this working.

my code is as follows but currently fails ont he foreach statement:

<?php

echo "starting <br><br>";
//mysql connection
$con2 = mysql_connect("localhost","dbuser","dbpassword");
if (!$con2)  {  die('Could not connect: ' . mysql_error());  }
$selectdb = mysql_select_db("dbname", $con2);
if (!$selectdb)  { die('Database not used: ; ' . mysql_error());  }

echo "connected to DB<br/><br/>";

//simplexml load xml file   
$library =  simplexml_load_file('http://www.website/ctrackxml/CTO_20120119140006_0000.xml');

echo "xml loaded<br/><br/>";

//loop through parsed xmlfeed and print output      

foreach ($message->message as $message) {                  
printf("messageid: %s\n", $messageid->messageid);                  
printf("mobile: %s\n", $mobile->mobile);
printf("time: %s\n", $time->time);
printf("latitude: %s\n", $latitude->latitude);
printf("longitude: %s\n", $longitude->longitude);
printf("status: %s\n", $status->status);
printf("speed: %s\n", $speed->speed);
printf("address: %s\n", $address->address);
printf("direction: %s\n", $direction->direction);
printf("runningodo: %s\n", $runningodo->runningodo);

echo "xml parsed<br/><br/>";

//insert into databse                     
mysql_query("INSERT INTO xml (messageid, mobile, time,latitude,longitude,status,speed,address,direction,odometer)
VALUES (\"$messageid->messageid\", \"$mobile->mobile\", \"$time->time\", \"$latitude->latitude\", \"$longitude->longitude\", \"$status->status\", \"$speed->speed\", \"$address->address\", \"$direction->direction\", \"$runningodo->runningodo\")")
or die(mysql_error());

echo "inserted into mysql<br/><br/>";

//show updated records            
printf ("Records inserted: %d\n", mysql_affected_rows());  
}


//close connection 
mysql_close($con2);

?>

Thanks as always to everyone for the help.

Answer 1

Use mysql_real_escape_string() on your input. http://se2.php.net/manual/en/function.mysql-real-escape-string.php

It willl not solve your current problem but future ones where escape chars are needed.

Turn on display_errors, enable warnings.

Try debugging with a counter in the foreach loop $i++, or echo every cycle. find out how far it goes before failing..

Answer 2

foreach ($library->message as $mess) {                  
  printf("messageid: %s\n", $mess->messageid);                  
  printf("mobile: %s\n", $mess->mobile);
  printf("time: %s\n", $mess->time);
  printf("latitude: %s\n", $mess->latitude);
  printf("longitude: %s\n", $mess->longitude);
  printf("status: %s\n", $mess->status);
  printf("speed: %s\n", $mess->speed);
  printf("address: %s\n", $mess->address);
  printf("direction: %s\n", $mess->direction);
  printf("runningodo: %s\n", $mess->runningodo);
}

Answer 3

<?php

ini_set('display_errors','On');

echo "starting";

//mysql connection
$con2 = mysql_connect("localhost","dbuser","dbpass");
if (!$con2)  {  
    die('Could not connect: ' . mysql_error());  
}

$selectdb = mysql_select_db("dbname", $con2);
if (!$selectdb)  { 
    die('Database not used: ; ' . mysql_error());  
}

echo "connected to DB<br /><br />";

// Read filenames in current directory looking for XML files
$file_arr = array();
if ($handle = opendir('.')) {
    while (false !== ($file = readdir($handle))) {
        if (($file != ".") && ($file != "..")) {
            if(substr($file, -4) == ".xml")
            {
                array_push($file_arr, $file);
            }
        }
    }
    closedir($handle);
}

// Loop through each XML file in the current directory
foreach($file_arr as $filename)
{
    //simplexml load xml file   
    $mess = simplexml_load_file($filename);
    echo "xml loaded<br /><br />";

    $messageid = mysql_real_escape_string($mess->messageid);
    $mobile = mysql_real_escape_string($mess->mobile);
    $time = mysql_real_escape_string($mess->time);
    $latitude = mysql_real_escape_string($mess->latitude);
    $longitude = mysql_real_escape_string($mess->longitude);
    $status = mysql_real_escape_string($mess->status);
    $speed = mysql_real_escape_string($mess->speed);
    $address = mysql_real_escape_string($mess->address);
    $direction = mysql_real_escape_string($mess->direction);
    $runningodo = mysql_real_escape_string($mess->runningodo);

    echo "xml parsed<br /><br />";

    //insert into databse                     
    mysql_query("INSERT INTO xml (messageid, mobile, time, latitude, longitude, status, speed, address, direction, odometer)
    VALUES ('$messageid', '$mobile', '$time', '$latitude', '$longitude', '$status', '$speed', '$address', '$direction', '$runningodo')")
    or die(mysql_error());

    echo "inserted into mysql<br /><br />";

    //show updated records            
    printf ("Records inserted: %d\n", mysql_affected_rows());  
}
//close connection 
mysql_close($con2);

?>

The above code should achieve what you are looking for based on the spec. As stated there is one XML record per one XML file.

This code is designed to run in the same directory as the XML files, you can easily change this by editing opendir command in the code. It will read all the XML files in the current working directory and place the data into the Database.

Answer 4

Browsing your code it looks like the following:

foreach ($message->message as $message) {

should actually be:

foreach ($library->message as $message) {

$library contains your XML object so it should be the one you are accessing and not the, as yet, unitialised $message variable.

Were $message initialised your current code would actually wipe itself out with each iteration of the for loop because it assigns its child element to itself!

Answer 5

I recently worked on a project that has some similarities to what you require. As Charles suggested, I used simplexml for the parsing. My task also required connecting via ftp to a server, getting files, unzipping them in a directory etc.

My approach was to write a script that processes one file, uses simplexml to read the document, do some simple transforms and write the data to mysql.

I then created a simple bash script wrapper that reads all the files in a directory and for each one, passes it as the parameter to my processing script.

If you found that your structure is a close enough match to allow for the use of LOAD XML you won't need PHP at all ... just the bash script and repeated calls to the mysql command line client.

The php script accepts the filename as the command line parameter. Of course this refers to the php CLI. It's actually a class, but its as simple as this to load the file:

if (file_exists($filename)) {
      $this->xml = simplexml_load_file($filename);

From there you can refer to the elements very easily. This bash script is typical of the what I'm using, where you pass it the directory where all your xml files are, and it just processes them all one by one.

#!/bin/bash
if [ $# -ne 1 ]
then
  echo "No directory specified."
  exit 1
fi
DIR="$( cd "$( dirname "$0" )" && pwd )"
FILES="$1"/*.xml
for f in $FILES
  do
    /usr/bin/php -f /path/to/yourscript.php $f
done
exit 0

If the documents are exactly as you describe then, once you do the load I described above (which assumes you have a simple class to facilitate your xml variable, then you just need to write an insert statement and call it using the mysql_query function. I'd actually recommend you tune the schema so that the column types accurately match the type of data your getting for each column rather than having varchars for numbers. This could be as simple as something like this:

$msg = $this->xml->message;

$query = "INSERT INTO YOUR TABLE (messageid, mobile...) VALUES ({$msg->message}, '{$msg->mobile}', ...)";
// assumes you already made db connection previously
$result = mysql_query($query);
if ($result) {
// inserted ok.
} else {
// something wrong, check mysql_error() for specifics
}