Storing certain XML children in SQL

Question

We have a network connected thermometer on our network that outputs an XML stream when you connect to its web address (http://xxx.xxx.xxx.xxx/netfeellife.xml):

<request>
    <rf_status>0</rf_status>
    <getvalue>0</getvalue>
    <scan_time>Fri May 11 09:10:54 2018</scan_time>
    <itemnum>3</itemnum>
    <temperature>28.4</temperature>
    <humidity>41</humidity>
    <dection>0</dection>
    <water>0</water>
    <gas>0</gas>
    <smoke>0</smoke>
    <dw1>0</dw1>
    <dw2>0</dw2>
    <dw3>0</dw3>
    <dw4>0</dw4>
    <dw5>0</dw5>
    <dw6>0</dw6>
    <dw7>0</dw7>
    <dw8>0</dw8>
    <dw9>0</dw9>
</request>

However, as we haven't connected the water/gas/smoke detectors, those parts of the output will always be '0', so it doesn't make sense to store them, only the <scan_time>, <temperature> and <humidity>, which have numbers. We also can't adjust the formatting of the XML, as it what the box creates.

My original code was like this:

 <?php
    $xml=simplexml_load_file("http://xxx.xxx.xxx.xxx/netfeellife.xml") or die("Error: Cannot create object");

    $db = new PDO('mysql:host=localhost;dbname=temperature;charset=utf8', 
    'root', 'password');
     $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
     $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
     $stmt = $db->prepare("INSERT INTO thermometer (`scan_time`, `itemnum`, 
     `temperature`, `humidity`) VALUES(?, ?, ?, ?)");

     $limit = new LimitIterator(new IteratorIterator($xml), 2, 4);

     foreach ($limit as $row)
     {

      $scan_time = (string)$row;
      //$limit->next();
      $itemnum = (string)$row;
      //$limit->next();
      $temperature = (string)$row;
      //$limit->next();
      $humidity = (string)
      $limit->current();

      $stmt->execute(array($scan_time, $itemnum, $temperature, $humidity));
     }

    ?>

But that saved the data like this:

scan_time itemnum temperature humidity

41 41 41 41

29.7 30 30 30

3 3 3 3

Fri May 11 19:53:15 2018 0 0 0

I've also tried adapting code from other queries on here - Parse xml-file and insert into mysql database - without much success.

How could I just extract the three elements I'd want and store them? Thanks

Are we still using the security risky mysql_* functions, deprecated in [PHP 5.5](http://php.net/manual/en/migration55.deprecated.php) since 2013 and entirely removed in [PHP 7](http://php.net/manual/en/mysql.php) in 2015? Before anything fix this to current DB-APIs: mysqli or PDO. — Parfait, May 11 '18 at 11:26
Thanks. My original PDO code wasn't working, which is why I thought I'd try using code from Stack Overflow to see if that would work (albeit an example that was posted back in 2012, before the functions were depreciated). — Phil, May 11 '18 at 20:10

score 0 · Answer 1 · answered May 11 '18 at 15:56

As your document only includes one set of readings, then the foreach() is not needed, in fact it's causing the problem...

//foreach ($xml as $syn)
// {
$scan_time = $syn->scan_time;
$temperature = $syn->temperature;
$humidity = $syn->humidity;

$sql="INSERT INTO thermometer (scan_time, temperature, humidity) VALUES ('$scan_time','$temperature','$humidity')";
$query  = mysql_query($sql);

if (!mysql_query($sql,$con))
 {
     die('Error: ' . mysql_error());
 }
else
 {
     echo "Records added";
 }
 //}

(I've shown the lines to comment out).

Storing certain XML children in SQL

1 Answers1