Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
using_weatherduino_pro2_upload_data_website_database_sql [2018/05/20 22:35] – [Step 4: Configuring the PHP Scripts] werk_agusing_weatherduino_pro2_upload_data_website_database_sql [2021/04/05 05:37] (current) – werk_ag
Line 1: Line 1:
-====== Using WeatherDuino Pro2 PLUS to upload data to a website database with SQL ======+====== Upload and record Air Quality data on an SQL database ====== 
 //By [[http://palmyweather.co.nz|PalmyWeather]]// //By [[http://palmyweather.co.nz|PalmyWeather]]//
  
- +==== Upload and record Air Quality data on an SQL database (Pro2 PLUS & Pro2 Compact) ====
-==== Using WeatherDuino Pro2 PLUS to upload data to a website database with SQL ==== +
- +
-**(Available in Receiver Software Release v6.x onwards)**+
  
 A Brief “How To” Guide. Please note that this is not a “one for all” guide, settings may be different depending on your server. A Brief “How To” Guide. Please note that this is not a “one for all” guide, settings may be different depending on your server.
- 
  
 ==== Step 1: Set up the database on the web server ==== ==== Step 1: Set up the database on the web server ====
Line 17: Line 14:
   * Navigate to “MySQL® Users”, usually found as part of the “MySQL® Databases” settings.   * Navigate to “MySQL® Users”, usually found as part of the “MySQL® Databases” settings.
   * Create a new user if you do not already have one set up.   * Create a new user if you do not already have one set up.
-    * Create a user name +      * Create a user name 
-    * Create a password +      * Create a password 
-    * Select the database you would like that new user to be associated with.+      * Select the database you would like that new user to be associated with.
  
-{{:wiki:database.jpg?nolink&600|}}\\+{{:wiki:database.jpg?nolink&600|database.jpg}}
  
-{{:wiki:database2.jpg?nolink&600|}}\\ +{{:wiki:database2.jpg?nolink&600|database2.jpg}}
-\\ +
  
 ==== Step 2: Enable remote control (This step may not be required) ==== ==== Step 2: Enable remote control (This step may not be required) ====
Line 33: Line 29:
   * From your server’s control panel, navigate to “Remote MySQL®”.   * From your server’s control panel, navigate to “Remote MySQL®”.
   * Enter your IP address into the “Add Access Host” and save it to the list.   * Enter your IP address into the “Add Access Host” and save it to the list.
- 
  
 ==== Step 3: Setting up the database structure / creating a new table ==== ==== Step 3: Setting up the database structure / creating a new table ====
Line 47: Line 42:
   * Here is an example of the Air Quality table with the columns named and parameters set.   * Here is an example of the Air Quality table with the columns named and parameters set.
  
-Additional columns can be created later on and parameters changed if the settings are not right first time around.\\ +Additional columns can be created later on and parameters changed if the settings are not right first time around. 
 + 
 +{{:wiki:database3.jpg?nolink&600|database3.jpg}}
  
-{{:wiki:database3.jpg?nolink&600|}}\\  
-\\  
 ==== Step 4: Configuring the PHP Scripts ==== ==== Step 4: Configuring the PHP Scripts ====
  
Line 56: Line 51:
  
   * Open “db_rw_details.php” and set the credentials. Remove the X’s and replace with your personal details between the quote marks.   * Open “db_rw_details.php” and set the credentials. Remove the X’s and replace with your personal details between the quote marks.
-    * $key ="xxxxxxxxxxx";  - This is a “handshaking” code which will be checked when data is passed from the outside source to ensure it is legitimate. +      * $key ="xxxxxxxxxxx"; - This is a “handshaking” code which will be checked when data is passed from the outside source to ensure it is legitimate. 
-    * $dbhost = "127.0.0.1"; - Usually does not need to be changed. The IP address could be replaced with “localhost”. +      * $dbhost = "127.0.0.1"; - Usually does not need to be changed. The IP address could be replaced with “localhost”. 
-    * $dbuser ="xxxxxxxxxxxx"; - This is the username which was setup in Step 1 of this guide. +      * $dbuser ="xxxxxxxxxxxx"; - This is the username which was setup in Step 1 of this guide. 
-    * $dbpassword="xxxxxxxxxxxx"; - This is the password which was setup in Step 1 of this guide. +      * $dbpassword="xxxxxxxxxxxx"; - This is the password which was setup in Step 1 of this guide. 
-    * $database ="xxxxxxxxxxxx"; - This is the name of the database which was setup in Step 1 of this guide. \\ +      * $database ="xxxxxxxxxxxx"; - This is the name of the database which was setup in Step 1 of this guide.
  
   * Open “AirQuality_LogToSQL.php” for configuring this file.   * Open “AirQuality_LogToSQL.php” for configuring this file.
-    * Locate and update the “include” line in this file. This is used to point the path to read the credentials set in the “db_rw_details.php”. It is best if these two file are not located in the same folder for security reasons.\\ An example of this is: include ('/home/weather/data/scripts/db_rw_details.php'); +      * Locate and update the “include” line in this file. This is used to point the path to read the credentials set in the “db_rw_details.php”. It is best if these two file are not located in the same folder for security reasons. \\ An example of this is: include ('/home/weather/data/scripts/db_rw_details.php'); 
-    * Locate the timezone setting and update this to your location. The following website can help choose the best timezone name to use for your area: http://php.net/manual/en/timezones.php .\\  Example of this is: date_default_timezone_set('Europe/Lisbon'); +      * Locate the timezone setting and update this to your location. The following website can help choose the best timezone name to use for your area: [[http://php.net/manual/en/timezones.php|http://php.net/manual/en/timezones.php]] . \\  Example of this is:date_default_timezone_set('Europe/Lisbon'); 
-    * Choose the path of where the text log should be stored. This is a one line text file which get updated each time the data is uploaded to the website. For example:$logfile = './logs/AirQuality_Log.txt';+      * Choose the path of where the text log should be stored. This is a one line text file which get updated each time the data is uploaded to the website. For example:$logfile = './logs/AirQuality_Log.txt';
  
   * Save and upload these two files to your chosen directories on your website.   * Save and upload these two files to your chosen directories on your website.
- 
  
 ==== Step 5: Configuring the Software of RX Unit ==== ==== Step 5: Configuring the Software of RX Unit ====
Line 77: Line 70:
   * Navigate to the “Credentials.h” file.   * Navigate to the “Credentials.h” file.
   * Update the “My Server Credentials”.   * Update the “My Server Credentials”.
-    * Under “My_Server”, enter your website address. NOTE: Do not use a leading http:%%//%% as this will not work. +      * Under “My_Server”, enter your website address. NOTE: Do not use a leading http:<nowiki>//</nowiki> as this will not work. 
-    * Under “Path_To_LogToSQL_PHPfile”, enter the path to the “AirQuality_LogToSQL.php” file on your website. +      * Under “Path_To_LogToSQL_PHPfile”, enter the path to the “AirQuality_LogToSQL.php” file on your website. 
-    * The “MYSERVER_KEY” is where your “key” set under Step 4 is entered. This is the “handshaking” code which will be checked when data is passed from the outside source to ensure it is legitimate. +      * The “MYSERVER_KEY” is where your “key” set under Step 4 is entered. This is the “handshaking” code which will be checked when data is passed from the outside source to ensure it is legitimate. 
-  *  Within the “Config_Options.h” file, the “ENABLE_AQM_TO_MYSERVER” will need to be enabled by changing the “0” to a “1”. +  * Within the “Config_Options.h” file, the “ENABLE_AQM_TO_MYSERVER” will need to be enabled by changing the “0” to a “1”. 
-  *  Assumptions have been made that WiFi has been enabled and configured already within the RX Software settings. This is also a requirement if it has not been enabled.+  * Assumptions have been made that WiFi has been enabled and configured already within the RX Software settings. This is also a requirement if it has not been enabled.
  
 Once the RX software is compiled and uploaded, it should now start to upload data to the database. This can be checked by logging into the database admin where the data can be viewed. Once the RX software is compiled and uploaded, it should now start to upload data to the database. This can be checked by logging into the database admin where the data can be viewed.
  
-The data can now be pulled from this database for such things like producing graphs on the website and for many other purposes.  +The data can now be pulled from this database for such things like producing graphs on the website and for many other purposes.
  
 There is a lot of potential going forward for this to not only upload data for the Air Quality monitor (which at time of writing is the only publicly supported option). In the future, this concept could be used to upload all the data gathered by the weather station and have the weather station save the data directly into the database making it a free standing product. There is a lot of potential going forward for this to not only upload data for the Air Quality monitor (which at time of writing is the only publicly supported option). In the future, this concept could be used to upload all the data gathered by the weather station and have the weather station save the data directly into the database making it a free standing product.
Line 91: Line 84:
 ==== Using SQL Language ==== ==== Using SQL Language ====
  
-Step 3 in this guide could be done by using SQL code if that is of personal preference. “[[http://www.meteocercal.info/forum/User-uncle-bob|Uncle_Bob]]” has done some work in this area. This is an example of the SQL code used to create a table within a database, once again using the Air Quality parameters.  +Step 3 in this guide could be done by using SQL code if that is of personal preference. “[[http://www.meteocercal.info/forum/User-uncle-bob|Uncle_Bob]]” has done some work in this area. This is an example of the SQL code used to create a table within a database, once again using the Air Quality parameters.
 <code> <code>
 +
 DROP SCHEMA IF EXISTS `AQM`; DROP SCHEMA IF EXISTS `AQM`;
 CREATE SCHEMA AQM; CREATE SCHEMA AQM;
Line 99: Line 92:
 DROP TABLE IF EXISTS `AirQuality`; DROP TABLE IF EXISTS `AirQuality`;
 CREATE TABLE `AirQuality` ( `LogDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `PM25` decimal(4,1) NOT NULL, `PM100` decimal(4,1) NOT NULL, `AQI` decimal(3,0) NOT NULL, `CO2` decimal(5,0) NOT NULL, PRIMARY KEY (LogDateTime) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `AirQuality` ( `LogDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `PM25` decimal(4,1) NOT NULL, `PM100` decimal(4,1) NOT NULL, `AQI` decimal(3,0) NOT NULL, `CO2` decimal(5,0) NOT NULL, PRIMARY KEY (LogDateTime) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 +
 </code> </code>
  
-{{:wiki:mysql.png?nolink&600|}} +{{:wiki:mysql.png?nolink&600}}
  
 ==== Testing PHP scripts to ensure it writes ==== ==== Testing PHP scripts to ensure it writes ====
  
-A web browser can be used to test the PHP scripts to ensure they write.\\  +A web browser can be used to test the PHP scripts to ensure they write. \\ As an example, this URL if entered into the web browser, should write data into the Air Quality database if it has been set up correctly (change the URL to find the "AirQuality_LogToSQL.php" file): \\ www.YOURSERVER.XXX/AirQuality_LogToSQL.php?privatekey=YOURKEY&PM25=99&PM100=98.7&AQI=97&CO2=96 \\ Once the web browser loads the page, nothing will be displayed. But if the database is viewed, the data listed in the URL should have been entered. 
-As an example, this URL if entered into the web browser, should write data into the Air Quality database if it has been set up correctly (change the URL to find the "AirQuality_LogToSQL.php" file):\\  +
-www.YOURSERVER.XXX/AirQuality_LogToSQL.php?privatekey=YOURKEY&PM25=99&PM100=98.7&AQI=97&CO2=96 \\  +
-Once the web browser loads the page, nothing will be displayed. But if the database is viewed, the data listed in the URL should have been entered.+
  

Trace:

Print/export
QR Code
QR Code using_weatherduino_pro2_upload_data_website_database_sql (generated for current page)