title: PHP Code - import xml file from web server (/public_html/ctrackxml) into mysql database
body: <p>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:</p>
  
  <pre><code>&lt;message type="POSITIONDATA"&gt;
    &lt;messageid&gt;-1&lt;/messageid&gt;
    &lt;mobile&gt;SNK261GP&lt;/mobile&gt;
    &lt;time&gt;2012/01/20 08:34:45 AM&lt;/time&gt;
    &lt;latitude&gt;-29.8477&lt;/latitude&gt;
    &lt;longitude&gt;30.9554&lt;/longitude&gt;
    &lt;status&gt;Driving&lt;/status&gt;
    &lt;speed&gt;82&lt;/speed&gt;
    &lt;address&gt; near Outer Ring Road (N2); Umkumbaan; in Durban&lt;/address&gt;
    &lt;direction&gt;
    &lt;/direction&gt;
    &lt;runningodo&gt;1587000&lt;/runningodo&gt;
  &lt;/message&gt;
  </code></pre>
  
  <p>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:</p>
  
  <p><img src="https://i.stack.imgur.com/Vi5Pr.jpg" alt="MySQL table"></p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>If you could point me in the right direction I would greatly appreciate it.</p>
  
  <p><strong>Update</strong> </p>
  
  <p>below is the code that I hav emanaged to scrape together with the assistance of another website.
  <a href="http://www.phpfreaks.com/forums/index.php?topic=244744.0" rel="nofollow noreferrer">http://www.phpfreaks.com/forums/index.php?topic=244744.0</a></p>
  
  <p>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.</p>
  
  <p>my code is as follows but currently fails ont he foreach statement:</p>
  
  <pre><code>&lt;?php
  
  echo "starting &lt;br&gt;&lt;br&gt;";
  //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&lt;br/&gt;&lt;br/&gt;";
  
  //simplexml load xml file   
  $library =  simplexml_load_file('http://www.website/ctrackxml/CTO_20120119140006_0000.xml');
  
  echo "xml loaded&lt;br/&gt;&lt;br/&gt;";
  
  //loop through parsed xmlfeed and print output      
  
  foreach ($message-&gt;message as $message) {                  
  printf("messageid: %s\n", $messageid-&gt;messageid);                  
  printf("mobile: %s\n", $mobile-&gt;mobile);
  printf("time: %s\n", $time-&gt;time);
  printf("latitude: %s\n", $latitude-&gt;latitude);
  printf("longitude: %s\n", $longitude-&gt;longitude);
  printf("status: %s\n", $status-&gt;status);
  printf("speed: %s\n", $speed-&gt;speed);
  printf("address: %s\n", $address-&gt;address);
  printf("direction: %s\n", $direction-&gt;direction);
  printf("runningodo: %s\n", $runningodo-&gt;runningodo);
  
  echo "xml parsed&lt;br/&gt;&lt;br/&gt;";
  
  //insert into databse                     
  mysql_query("INSERT INTO xml (messageid, mobile, time,latitude,longitude,status,speed,address,direction,odometer)
  VALUES (\"$messageid-&gt;messageid\", \"$mobile-&gt;mobile\", \"$time-&gt;time\", \"$latitude-&gt;latitude\", \"$longitude-&gt;longitude\", \"$status-&gt;status\", \"$speed-&gt;speed\", \"$address-&gt;address\", \"$direction-&gt;direction\", \"$runningodo-&gt;runningodo\")")
  or die(mysql_error());
  
  echo "inserted into mysql&lt;br/&gt;&lt;br/&gt;";
  
  //show updated records            
  printf ("Records inserted: %d\n", mysql_affected_rows());  
  }
  
  
  //close connection 
  mysql_close($con2);
  
  ?&gt;
  </code></pre>
  
  <p>Thanks as always to everyone for the help.</p>