<?php
/*
* Meteohub to SQL script by Fergal Coulter - www.wexfordharbour.info - (2011)
*
* Requires Meteohub upload the file all_sensors.csv to a specified location
* A CRON job should be set up on your server to run this script as often as you require
*
* SQL Database structure should be an AUTO_INCRAMENT(ed) INT index in field 0, DATETIME field 1, then VARCHAR(11) for all others
*/
////////////////////////////////////////////////////////////////
// YOUR DATABASE DETAILS
////////////////////////////////////////////////////////////////
$databasehost = "DATABASE.INTERNAL.HOSTNAME.com";
$databasename = "YOUR_DATABASE_NAME";
$databasetable = "YOUR_DATABASE_TABLE";
$databaseusername ="DATABASE_LOGIN_NAME";
$databasepassword = "DATABASE_PASSWORD";
// The absolute location of where meteohub is uploading all_sensors.csv etc :
// (error.txt is created manually, and only used for debugging)
// the /usr/local/ etc will vary per your server
$csvfile = "../html/all_sensors.csv";
$FileName = "../html/error.txt";
////////////////////////////////////////////////////////////////
$fieldseparator = " ";
$lineseparator = "\n";
$FileHandle = fopen($FileName, 'w') or die("can't open file");
fwrite($FileHandle, "Run");
fclose($FileHandle);
// add an : ,'NA' for every extra field you want to put into your SQL database - in this case 10 fields plus an empty first field for index
$array = array('','NA','NA','NA','NA','NA','NA','NA','NA','NA','NA');
//these are the specific fields of interest within the all_sensors.csv - add / remove or change as per your requirements
$var1 = 'actual_localdate2';
$var2 = 'day1_wind0_gustspeedmax_kn';
$var3 = 'day1_wind0_speed_kn';
$var4 = 'day1_wind0_speedmin_kn';
$var5 = 'day1_th0_tempmin_c';
$var6 = 'day1_th0_tempmax_c';
$var7 = 'day1_rain0_total_mm';
$var8 = 'day1_rain0_ratemax_mm';
$var9 = 'day1_thb0_hummax_rel';
$var10 = 'day1_thb0_hummin_rel';
// The following scans through all the CSV rows available
$row = 1;
if (($handle = fopen($csvfile, "r")) !== FALSE) {
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$num = count($data);
$row++;
for ($c=0; $c < $num; $c++) {
$pos_seq = strpos($data[$c], 'seq');
// The following little section converts the formatting of "actual_localdate2" into an
// SQL DateTime format and adds inserts it into the array[1] element
$pos = strpos($data[$c], $var1);
if (($pos !== false) && ($pos_seq === false)) {
$datetime=strpbrk($data[$c], ' ');
$pieces = explode(" ", $datetime);
$year_bits = explode(".", $pieces[1]);
$amend_year=array($year_bits[2],$year_bits[1],$year_bits[0]); //reverse the year
$pieces[1]=implode(".", $amend_year);
$array[1]=implode(" ", $pieces);
echo $data[$c] . "<br />\n";
}
// Each of the following sections strip out white space, and inserts the data into corresponding array[XX]
// This method could be done far more efficently of course, but it works!
$pos = strpos($data[$c], $var2);
if (($pos !== false) && ($pos_seq === false)){
echo $data[$c] . "<br />\n";
$array[2]=strpbrk($data[$c], ' ');
}
$pos = strpos($data[$c], $var3);
if (($pos !== false) && ($pos_seq === false)) {
echo $data[$c] . "<br />\n";
$array[3]=strpbrk($data[$c], ' ');
}
$pos = strpos($data[$c], $var4);
if (($pos !== false) && ($pos_seq === false)){
echo $data[$c] . "<br />\n";
$array[4]=strpbrk($data[$c], ' ');
}
$pos = strpos($data[$c], $var5);
if (($pos !== false) && ($pos_seq === false)) {
echo $data[$c] . "<br />\n";
$array[5]=strpbrk($data[$c], ' ');
}
$pos = strpos($data[$c], $var6);
if (($pos !== false) && ($pos_seq === false)) {
echo $data[$c] . "<br />\n";
$array[6]=strpbrk($data[$c], ' ');
}
$pos = strpos($data[$c], $var7);
if (($pos !== false) && ($pos_seq === false)){
echo $data[$c] . "<br />\n";
$array[7]=strpbrk($data[$c], ' ');
}
$pos = strpos($data[$c], $var8);
if (($pos !== false) && ($pos_seq === false)) {
echo $data[$c] . "<br />\n";
$array[8]=strpbrk($data[$c], ' ');
}
$pos = strpos($data[$c], $var9);
if (($pos !== false) && ($pos_seq === false)) {
echo $data[$c] . "<br />\n";
$array[9]=strpbrk($data[$c], ' ');
}
$pos = strpos($data[$c], $var10);
if (($pos !== false) && ($pos_seq === false)) {
echo $data[$c] . "<br />\n";
$array[10]=strpbrk($data[$c], ' ');
}
//////////////////////////////////////////////////////////////////
/*
* for every new field added, you should include one of the following 'functions'
* !!! make sure to change $varXX on line 1, and $array[XX] on line 4
$pos = strpos($data[$c], $var10);
if (($pos !== false) && ($pos_seq === false)) {
echo $data[$c] . "<br />\n";
$array[10]=strpbrk($data[$c], ' ');
}
*/
//////////////////////////////////////////////////////////////////
}
}
fclose($handle);
}
//////////////////////////////////////////////////////////////////
// This inserts the array into the SQL database
// Make sure to add an extra ,'$array[XX]' for every extra field you add
//////////////////////////////////////////////////////////////////
$con = mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
mysql_select_db($databasename) or die(mysql_error());
mysql_query("INSERT INTO $databasetable VALUES
('$array[0]','$array[1]','$array[2]','$array[3]','$array[4]','$array[5]','$array[6]','$array[7]','$array[8]','$array[9]','$array[10]') ")
or die(mysql_error());
echo "Data Inserted!";
mysql_close($con);
?>
