Arduino: Save data to database

While broadcasting data over a dedicated web page on your home network is useful for a home project, fundamentals of an advanced project are to be able to store data you read from your connected sensors. This way you can monitor live data, but are also able to get historic information. It also allows you to capture data from multiple data input devices and display them when and how you want. Even though this could also be done with a dedicated web page by adding a little more code to your Arduino, it is easier to store it to a database and create a web page (or user interface) that reads data from the database.

In this article I will cover how to push data over the network to your database, and display it over the webpage. Since the real goal is to have an online database, which isn’t free, I will also describe how you can do this on your computer for practice.

 

Wikipedia: A database is an organized collection of data. The data is typically organized to model aspects of reality in a way that supports processes requiring information. For example, modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies.

 

This project will use the same connection and is really similar to the project I wrote earlier Display data over local network so I will skip the connecting part. The only difference is in the destination of the sent data. If you haven’t already, take a look there to see how I connected Arduino to the network.

 

For this project you will need:

– Everything used in the previous article: Display data over local network (Arduino, Ethernet shield, Light sensor, jumper wires, network cable)

– Online website hosting: I will not put links because I don’t advertise anyone here, google which web hosting suits your needs. Your web hosting will need to be able to run php and 1 mysql database

OR

– Local server environment: I will use XAMPP for demonstration purposes, but there are a lot of alternatives

 

The code for Arduino is the same in both cases, except in the IP address of the destination server – I will mark it with an IMPORTANT tag.

 

Step 1

Connect Arduino to your local network

Same as in previous article: Display data over local network – use it as a reference to make the connection

 

Step 2

Prepare the database

If you have a web database service running or XAMPP installed just log into your phpMyAdmin interface to create a new database.

arduino save data to database

 

1. Under the tab “Databases” create a new database. Pick a name and collation (depending on your keyboard localization, or you can leave it as is) and press “Create”. I chose the name “test” for this article.

arduino save data to database

 

2. When you create the database select the tab “Privileges”, from the tab menu.

arduino save data to database

  Next we need to create a user that will have access to the database. Press “Add user” and in the new menu enter a username and a password for the user that we are going to use for database access. You can leave other fields blank, but be careful that the checkbox Grant all privileges on database “test” stays ticked. I chose the name “arduino” and password “arduinotest” for this article.

arduino save data to database

 

3. We need a new table that we are going to write data to. Choose “SQL” from the tab menu:

arduino save data to database

  Put in this code and press “Go”.

CREATE TABLE 'test'.'sensor' (

'id' INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
'time' TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
'value' VARCHAR( 10 ) NOT NULL
)

 

  This will create a new table in our database that we can put our data in. We have an id that distinguishes each reading with its unique id, time that automatically puts current time when we write data, and value that we will read from our light sensor.

 

Step 3

Create files that will capture data sent from Arduino and write it to database

The easiest way to get data from Arduino to your database is to use php and HTTP GET request method.

Wikipedia: GET – Requests a representation of the specified resource. Requests using GET should only retrieve data and should have no other effect.

Basically, GET is used for sending limited amount of data to a webpage, ie. a GET request looks like this:

http://www.yourwebpage.com/write_data.php?data1=value1;data2=value2;data3=value3...

 

Still this doesn’t write data directly into the database, so we need to make a php file that we will send data to (from example, the “write_data.php” file) which will write data to database.

 

1. Create the “write_data.php” file. Open up notepad and paste in the below code.

<?php

    // Prepare variables for database connection
   
    $dbusername = "arduino";  // enter database username, I used "arduino" in step 2.2
    $dbpassword = "arduinotest";  // enter database password, I used "arduinotest" in step 2.2
    $server = "localhost"; // IMPORTANT: if you are using XAMPP enter "localhost", but if you have an online website enter its address, ie."www.yourwebsite.com"

    // Connect to your database

    $dbconnect = mysql_pconnect($server, $dbusername, $dbpassword);
    $dbselect = mysql_select_db("test",$dbconnect);

    // Prepare the SQL statement

    $sql = "INSERT INTO test.sensor (value) VALUES ('".$_GET["value"]."')";    

    // Execute SQL statement

    mysql_query($sql);

?>

 

Afterwards click File -> Save As. In the “File name:” enter “write_data.php” and from the “Save as type:” drop-down menu select “All Files” (see image below) and click “Save”.

arduino save data to database

 

2. Put the “write_data.php” file in the right folder

If you are using XAMPP you will need to place the file in the “C:\xampp\htdocs” directory (or if your XAMPP install directory is different use that directory\htdocs).

OR

If you are using an online web page, you will need to place the file in the “public_html/www” directory.

 

WARNING: If you are using a live web page, you should be aware of the SQL Injection attacks that can damage or completely destroy your web applications and web pages. Before using this code be sure to read a little more about this type of attack.

Wikipedia: SQL injection is a code injection technique, used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution (e.g. to dump the database contents to the attacker).[1] SQL injection must exploit a security vulnerability in an application’s software, for example, when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and unexpectedly executed. SQL injection is mostly known as an attack vector for websites but can be used to attack any type of SQL database.

 

When we have a database and a php file that will write sent data into the database we can test it to see it works. All you need to do is enter a GET request into your browser:

If you are using XAMPP – in your web browser insert this address:

http://localhost/write_data.php?value=100

 

If you have a live webpage – in your web browser insert this address:

http://www.yourwebpage.com/write_data.php?value=100

 

(while the “www.yourwebpage.com” – stands for your real website address)

 

If everything worked fine you can check in your phpMyAdmin interface by selecting the database “test” and then selecting the table “sensor”. The value 100 with id=1 and current time stamp should be there.

arduino save data to database

 

 

Step 4

Write Arduino code

When we got the database ready, and the php file that writes data to database, we can write in Arduino code that sends current reading from the light sensor.

IMPORTANT: In the code you will find comments that say IMPORTANT. This is the place where you will have to enter values different than mine, according to your environment. If you are using XAMPP you will have to find out your computer IP address (detailed in last article). If you are using a webpage you will then have to enter its address (ie. “www.yourwebpage.com”).

 

#include <SPI.h>
#include <Ethernet.h>

byte mac[] = {
  0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
 
// Enter the IP address for Arduino, as mentioned we will use 192.168.0.16
// Be careful to use , insetead of . when you enter the address here
IPAddress ip(192,168,0,16);

int photocellPin = 0;  // Analog input pin on Arduino we connected the SIG pin from sensor
int photocellReading;  // Here we will place our reading

char server[] = "192.168.0.11"; // IMPORTANT: If you are using XAMPP you will have to find out the IP address of your computer and put it here (it is explained in previous article). If you have a web page, enter its address (ie. "www.yourwebpage.com")

// Initialize the Ethernet server library
EthernetClient client;

void setup() {
 
  // Serial.begin starts the serial connection between computer and Arduino
  Serial.begin(9600);
 
  // start the Ethernet connection
  Ethernet.begin(mac, ip);
    
}

void loop() {
 
  photocellReading = analogRead(photocellPin); // Fill the sensorReading with the information from sensor
 
  // Connect to the server (your computer or web page)  
  if (client.connect(server, 80)) {
    client.print("GET /write_data.php?"); // This
    client.print("value="); // This
    client.print(photocellReading); // And this is what we did in the testing section above. We are making a GET request just like we would from our browser but now with live data from the sensor
    client.println(" HTTP/1.1"); // Part of the GET request
    client.println("Host: 192.168.0.11"); // IMPORTANT: If you are using XAMPP you will have to find out the IP address of your computer and put it here (it is explained in previous article). If you have a web page, enter its address (ie.Host: "www.yourwebpage.com")
    client.println("Connection: close"); // Part of the GET request telling the server that we are over transmitting the message
    client.println(); // Empty line
    client.println(); // Empty line
    client.stop();    // Closing connection to server

  }

  else {
    // If Arduino can't connect to the server (your computer or web page)
    Serial.println("--> connection failed\n");
  }
 
  // Give the server some time to recieve the data and store it. I used 10 seconds here. Be advised when delaying. If u use a short delay, the server might not capture data because of Arduino transmitting new data too soon.
  delay(10000);
}

 

 

If everything worked as planned, you should be able to see new data in your database through the phpMyAdmin. You can now do what you like with it, make an app which connects to the database and reads it, or make a php webpage that displays it.

 

Step 5

Displaying the data

To display the new data in your database it is easiest to make a php webpage that will read data from it and refresh every few seconds. For this article I will create a new php file (like in Step 3) that will display my data.

 

1. Creating the php file that will read data from database. Again open notepad and paste in the below code.

<?php
$url=$_SERVER['REQUEST_URI'];
header("Refresh: 5; URL=$url");  // Refresh the webpage every 5 seconds
?>
<html>
<head>
    <title>Light Sensor</title>
</head>
    <body>
        <h1>Light sensor readings</h1>
    <table border="0" cellspacing="0" cellpadding="4">
      <tr>
            <td>ID</td>
            <td>Timestamp</td>
            <td>Value</td>
      </tr>
      
<?php
    // Connect to database

   // IMPORTANT: If you are using XAMPP you will have to enter your computer IP address here, if you are using webpage enter webpage address (ie. "www.yourwebpage.com")
    $con=mysqli_connect("192.168.0.11","arduino","arduinotest","test");
       
    // Retrieve all records and display them   
    $result = mysqli_query($con,'SELECT * FROM sensor ORDER BY id DESC');
      
    // Process every record
    
    while($row = mysqli_fetch_array($result))
    {      
        echo "<tr>";
        echo "<td>" . $row['id'] . "</td>";
        echo "<td>" . $row['time'] . "</td>";
        echo "<td>" . $row['value'] . "</td>";
        echo "</tr>";
        
    }
        
    // Close the connection   
    mysqli_close($con);
?>
    </table>
    </body>
</html>

 

 Afterwards click File -> Save As. In the “File name:” enter “get_data.php” and from the “Save as type:” drop-down menu select “All Files” (see image below) and click “Save”.

 

2. Put the “get_data.php” file in the right folder

If you are using XAMPP you will need to place the file in the “C:\xampp\htdocs” directory (or if your XAMPP install directory is different use that directory\htdocs).

OR

If you are using an online web page, you will need to place the file in the “public_html/www” directory.

 

 If everything worked as planned, when opening the page get_data.php you should get the data that we wrote in it from the sensor.

arduino save data to database

 

Now we can make more advanced projects with databases and multiple sensors.