Saving to mySQL and XML in PHP

[Tissy]

Hi,

I have a small piece of code which updates a mySQL database using PHP. The values the database are updated with come from a microcontroller.

Not being particularly familiar with PHP, is there a way that when the values are updated, it also stores these values in a xml document with a structure similar to below.

Then when the new set of values are updated, the xml is overwritten with the new values.



<?xml version="1.0"?>
-<data>
-<item>
<time>01/10:14am</time>
<temperature>10.5</temperature>
<humidity>86</humidity>
</item>
</data>




<?php
// connect to MySQL
mysql_connect('xxxxx','xxxxx','xxxxx') or die("Can't connect that way!");
@mysql_select_db('arduino') or die("Unable to select a database called 'Arduino'");
if(ISSET($_GET['t']) && (is_numeric($_GET['t'])) && $_SERVER['REMOTE_ADDR']=='192.168.1.xxx'){
// message from the Arduino
$temp = $_GET['t'];
$humidity = $_GET['h'];

// now - 60sec x 60min x 24hrs = 1 day ago
$one_day_ago = time() + 60*60*24;

$qry = "INSERT INTO temp(timing, temp, humidity) VALUES(".time().",'$temp','$humidity')";
$qry_del = "DELETE FROM temp WHERE timing < UNIX_TIMESTAMP(NOW() - INTERVAL 3 DAY)";
mysql_query($qry);
mysql_query($qry_del);
// OPTIMIZE TABLE temp
mysql_close();
exit('200');
}
mysql_close();
?>


Any help and code examples appriciated.

Steve

[djr33]

Yes. After the two mysql_query() lines you can do whatever you need, since those two lines are what add the information to the database (and delete old records). So you can also save the information in another format there.

You will need to look at how to create a file. Look into fopen(), fwrite() and fclose() on php.net in the function reference pages (use the search box to find them). Create a file with a unique name, maybe based on the current date/time.

In fwrite() you can add any content you'd like. There are specific XML functions available in PHP, but since you will be using the same format every time I don't think that's necessary. Just use the following:

$xmldata = '<?xml version="1.0"?>
-<data>
-<item>
<time>'.$timing.'</time>
<temperature>'.$temp.'</temperature>
<humidity>'.$humidity.'</humidity>
</item>
</data>';
(If those variables might ever contain symbols like < or > that could interfere with the XML structure, then you'd want to escape those.)


Finally, if you need to delete the old XML files, you can use the function unlink(). But I'm not sure how you'd locate them. As I suggested above, if you use the current date/time as the filename, that will make them easier to deal with. You'd probably need to loop through the directory: use opendir() and readdir() to go through each file) and check if the filename is a date/time 3 days old; if so, delete it using unlink().