Logging data to Database using Arduino Ethernet shield

    911 views
    0 comments
    0 likes
    details

    Arduino-Ethernet-Database

    Description

    In this project, we will be logging data to database using Arduino Ethernet shield. We will store the data of the DHT22 temperature and humidity sensor in the database and we will then show this on the webpage. For creating the database, we will use the Xampp server and we will then send the data from the Arduino using the Ethernet shield through the internet router. This data will get stored in the webpage and we can access this data anytime we want. We will also create a webpage that will show this data.

     

    Hardware Requirements

    The components required for logging data to database using Arduino Ethernet shield are as follows

    • Arduino Uno
    • Arduino Ethernet Shield
    • DHT22 Temperature and Humidity sensor
    • Connecting Wires
    • Breadboard

     

    Skill Required

    The skills you require logging data to database using Arduino Ethernet shield are as follows

    • Arduino IDE
    • HTML
    • PHP
    • MYSQL

     

    Getting Started

    Step 1          Installing and Setting the Xampp

    1. The first step you need to do is to install Xampp. Go to the below link and install it from there.

    Xampp

    After installing it, run the Xampp as administrator by right clicking on it and selecting run as administrator as shown in below picture.

    Run xampp as admin.png

    2. After running it, click on config button next to apache and select “Apache (httpd.config)” as shown below.

    Xampp port selection.png

    3. A notepad file will open, change the port written next to word “listen” from 80 to 8095. The reason of why we need to change the port is that the port 80 is sometimes used by other applications so it will cause error. Therefore, we have selected a separate port to avoid any error.

    change port.PNG

    4. After that, click on config button in the top right corner as highlighted in the below figure

    press config button.PNG

    5. While pressing the config button, a window will open; check the mysql and apache boxes as shown below

    check boxes.PNG

    6. After doing all the above steps, restart you PC and run again the Xampp as administrator. You will see the apache and mysql service started.

    apache and mysql started.PNG

    Step 2

    Creating the Database

    Open your browser and type “localhost:8095/phpmyadmin/”. It will ask for a username and password, the default username is “root” and leave the password as blank. After giving the username and password, it will open the phpmyadmin.

    Then go to databases tab and create the new database. I have created the database with the name “ethernet” as shown below

    create database.PNG

    Then go to SQL tab next to databases tab and paste the below code there. It will create a table where we will enter the data of the sensors from the Arduino

    CREATE TABLE `ethernet`.`data` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`event` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
`temperature` VARCHAR( 10 ) NOT NULL,
`humidity` VARCHAR( 10 ) NOT NULL,
`heat_index` VARCHAR( 10 ) NOT NULL
)

     

    sql.PNG

    Step 3

    Creating the PHP pages

    Now we are going to create the php pages which will help in making connection between the Arduino and the Database.

    1. First go to C:\xampp\htdocs and create a new folder named ‘ethernet’. Create a new text file in this folder and save the name as ‘connection.php’. Paste the below code there and save the file. This PHP file will make a connection between the Arduino and the database.

    <?php
$username = "root";
$pass = "";
$host = "localhost";
$db_name = "ethernet";
$con = mysqli_connect ($host, $username, $pass);
$db = mysqli_select_db ( $con, $db_name );
?>

     

    2. Then create a new text file and save the name as ‘data.php’. Paste the below code there and save it. This PHP file will log the data into the database from the Arduino.

    <?php
include ('connection.php');
$sql_insert = "INSERT INTO data (temperature, humidity, heat_index) VALUES ('".$_GET["temperature"]."', '".$_GET["humidity"]."', '".$_GET["heat_index"]."')";
if(mysqli_query($con,$sql_insert))
{
echo "Done";
mysqli_close($con);
}
else
{
echo "error is ".mysqli_error($con );
}
?>

     

    3. You can manually check that if the database is receiving the data from these pages or not or you can say that if the connection between the database and these pages is made or not. To check that, place the below line in your browser

    http://localhost:8095/ethernet/data.php?temperature=15&humidity=14&heat_index=13

     

    After entering the above line in your browser, if “Done” will be shown, then it means that the values are stored in the database. You can see these by going into the data base.

    Step 4

    Sending Data from Arduino

    Now, we are going to send the data from Arduino to the database.

    Make the connections of the DHT22 with the Arduino as shown in the figure below

    To read more about interfacing DHT22 with Arduino, read this Tutorial | Temperature and Humidity Sensor DHT22 Interfacing with Arduino

    DHT22 with arduino_bb.png

    Arduino Code

    Download the DHT22 library from the below link. No need to download the Ethernet and SPI library because it comes with the Arduino IDE installation.

    DHT22 Library

    Now paste the below code in the Arduino IDE

    #include <SPI.h>
#include <Ethernet.h>
#include "DHT.h"
#define DHTPIN 9
#define DHTTYPE DHT22
DHT dht(DHTPIN, DHTTYPE);
byte mac[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
byte ip[] = {192, 168, 1, 18 }; //Read the code explanation below
byte serv[] = {192, 168, 1, 5} ; //Read the code explanation below
EthernetClient cliente;
void setup() {
Serial.begin(9600); //setting the baud rate at 9600
Ethernet.begin(mac, ip);
dht.begin();
}
void loop() {
float hum = dht.readHumidity(); //Reading the humidity and storing in hum
float temp = dht.readTemperature(); //Reading the temperature as Celsius and storing in temp
float fah = dht.readTemperature(true); //reading the temperature in Fahrenheit
float heat_index = dht.computeHeatIndex(fah, hum); //Reading the heat index in Fahrenheit
float heat_indexC = dht.convertFtoC(heat_index); //Converting the heat index in Celsius
if (cliente.connect(serv, 8095)) { //Connecting at the IP address and port we saved before
Serial.println("connected");
cliente.print("GET /ethernet/data.php?"); //Connecting and Sending values to database
cliente.print("temperature=");
cliente.print(temp);
cliente.print("&humidity=");
cliente.print(hum);
cliente.print("&heat_index=");
cliente.println(heat_indexC);
//Printing the values on the serial monitor
Serial.print("Temperature= ");
Serial.println(temp);
Serial.print("Humidity= ");
Serial.println(hum);
Serial.print("Heat Index= ");
Serial.println(heat_indexC);
cliente.stop(); //Closing the connection
}
else {
// if you didn't get a connection to the server:
Serial.println("connection failed");
}
delay(5000);
}

     

    Code Explanation

    You need to make changes in the following two lines. Make sure that the Ethernet wire is connected to the Ethernet shield.

    byte ip[] = {192, 168, 1, 18 };
byte serv[] = {192, 168, 1, 5} ;

    In the Arduino IDE, go to files-Examples-Ethernet-DHCPAdressPrinter. A new window will open up, upload the code and in the serial printer, it will show you the IP Address. Enter it in “byte ip[] = {192, 168, 1, 18 };”

    DHCP IP Adress.PNG

    Then open the command prompt (cmd) and type in “ipconfig” and it will show you the IPv4 address. Enter it in “byte serv[] = {192, 168, 1, 5} ; ”

    IPv4 Adress.PNG

    The remaining code is explained with the comments.

    Step 5

    Displaying the Data

    To display the data, you will need to create another PHP file that will read the data from the database and will show it on the webpage.

    Create another PHP file with the name “display.php” and place it in the same folder where you have placed the other PHP files (C:\xampp\htdocs\ethernet\). Place the following code in the display.php and save the file.

    <?php
$url=$_SERVER['REQUEST_URI'];
header("Refresh: 5; URL=$url"); // Refresh the webpage every 5 seconds
?>
<html>
<head>
<title>Arduino Ethernet Database</title>
<style type="text/css">
.table_titles {
padding-right: 20px;
padding-left: 20px;
color: #000;
}
.table_titles {
color: #FFF;
background-color: #0000FF;
}
table {
border: 2px solid #333;
}
body { font-family: "Trebuchet MS", Courier; }
</style>
</head>
<body>
<h1>Arduino Data Logging to Database</h1>
<table border="0" cellspacing="0" cellpadding="4">
<tr>
<td class="table_titles">ID</td>
<td class="table_titles">Date and Time</td>
<td class="table_titles">Temperature</td>
<td class="table_titles">Humidity</td>
<td class="table_titles">Heat_index</td>
</tr>
<?php
include('connection.php');
$result = mysqli_query($con,'SELECT * FROM data ORDER BY id DESC');
// Process every record
$oddrow = true;
while($row = mysqli_fetch_array($result))
{
if ($oddrow)
{
$css_class=' class="table_cells_odd"';
}
else
{
$css_class=' class="table_cells_even"';
}
$oddrow = !$oddrow;
echo "<tr>";
echo "<td '.$css_class.'>" . $row['id'] . "</td>";
echo "<td '.$css_class.'>" . $row['event'] . "</td>";
echo "<td '.$css_class.'>" . $row['temperature'] . "</td>";
echo "<td '.$css_class.'>" . $row['humidity'] . "</td>";
echo "<td '.$css_class.'>" . $row['heat_index'] . "</td>";
echo "</tr>";
}
// Close the connection
mysqli_close($con);
?>
</table>
</body>
</html>

     

    Now type the following line in the browser, webpage with data will be shown like shown in below figure

    http://localhost:8095/ethernet/display.php

     

    display.PNG

     

    Tags: 201802, W5100, Ethernet, Arduino, Database, MySQL

     

    Arduino-Ethernet-Database

    Description

    In this project, we will be logging data to database using Arduino Ethernet shield. We will store the data of the DHT22 temperature and humidity sensor in the database and we will then show this on the webpage. For creating the database, we will use the Xampp server and we will then send the data from the Arduino using the Ethernet shield through the internet router. This data will get stored in the webpage and we can access this data anytime we want. We will also create a webpage that will show this data.

     

    Hardware Requirements

    The components required for logging data to database using Arduino Ethernet shield are as follows

    • Arduino Uno
    • Arduino Ethernet Shield
    • DHT22 Temperature and Humidity sensor
    • Connecting Wires
    • Breadboard

     

    Skill Required

    The skills you require logging data to database using Arduino Ethernet shield are as follows

    • Arduino IDE
    • HTML
    • PHP
    • MYSQL

     

    Getting Started

    Step 1          Installing and Setting the Xampp

    1. The first step you need to do is to install Xampp. Go to the below link and install it from there.

    Xampp

    After installing it, run the Xampp as administrator by right clicking on it and selecting run as administrator as shown in below picture.

    Run xampp as admin.png

    2. After running it, click on config button next to apache and select “Apache (httpd.config)” as shown below.

    Xampp port selection.png

    3. A notepad file will open, change the port written next to word “listen” from 80 to 8095. The reason of why we need to change the port is that the port 80 is sometimes used by other applications so it will cause error. Therefore, we have selected a separate port to avoid any error.

    change port.PNG

    4. After that, click on config button in the top right corner as highlighted in the below figure

    press config button.PNG

    5. While pressing the config button, a window will open; check the mysql and apache boxes as shown below

    check boxes.PNG

    6. After doing all the above steps, restart you PC and run again the Xampp as administrator. You will see the apache and mysql service started.

    apache and mysql started.PNG

    Step 2

    Creating the Database

    Open your browser and type “localhost:8095/phpmyadmin/”. It will ask for a username and password, the default username is “root” and leave the password as blank. After giving the username and password, it will open the phpmyadmin.

    Then go to databases tab and create the new database. I have created the database with the name “ethernet” as shown below

    create database.PNG

    Then go to SQL tab next to databases tab and paste the below code there. It will create a table where we will enter the data of the sensors from the Arduino

    CREATE TABLE `ethernet`.`data` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`event` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
`temperature` VARCHAR( 10 ) NOT NULL,
`humidity` VARCHAR( 10 ) NOT NULL,
`heat_index` VARCHAR( 10 ) NOT NULL
)

     

    sql.PNG

    Step 3

    Creating the PHP pages

    Now we are going to create the php pages which will help in making connection between the Arduino and the Database.

    1. First go to C:\xampp\htdocs and create a new folder named ‘ethernet’. Create a new text file in this folder and save the name as ‘connection.php’. Paste the below code there and save the file. This PHP file will make a connection between the Arduino and the database.

    <?php
$username = "root";
$pass = "";
$host = "localhost";
$db_name = "ethernet";
$con = mysqli_connect ($host, $username, $pass);
$db = mysqli_select_db ( $con, $db_name );
?>

     

    2. Then create a new text file and save the name as ‘data.php’. Paste the below code there and save it. This PHP file will log the data into the database from the Arduino.

    <?php
include ('connection.php');
$sql_insert = "INSERT INTO data (temperature, humidity, heat_index) VALUES ('".$_GET["temperature"]."', '".$_GET["humidity"]."', '".$_GET["heat_index"]."')";
if(mysqli_query($con,$sql_insert))
{
echo "Done";
mysqli_close($con);
}
else
{
echo "error is ".mysqli_error($con );
}
?>

     

    3. You can manually check that if the database is receiving the data from these pages or not or you can say that if the connection between the database and these pages is made or not. To check that, place the below line in your browser

    http://localhost:8095/ethernet/data.php?temperature=15&humidity=14&heat_index=13

     

    After entering the above line in your browser, if “Done” will be shown, then it means that the values are stored in the database. You can see these by going into the data base.

    Step 4

    Sending Data from Arduino

    Now, we are going to send the data from Arduino to the database.

    Make the connections of the DHT22 with the Arduino as shown in the figure below

    To read more about interfacing DHT22 with Arduino, read this Tutorial | Temperature and Humidity Sensor DHT22 Interfacing with Arduino

    DHT22 with arduino_bb.png

    Arduino Code

    Download the DHT22 library from the below link. No need to download the Ethernet and SPI library because it comes with the Arduino IDE installation.

    DHT22 Library

    Now paste the below code in the Arduino IDE

    #include <SPI.h>
#include <Ethernet.h>
#include "DHT.h"
#define DHTPIN 9
#define DHTTYPE DHT22
DHT dht(DHTPIN, DHTTYPE);
byte mac[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
byte ip[] = {192, 168, 1, 18 }; //Read the code explanation below
byte serv[] = {192, 168, 1, 5} ; //Read the code explanation below
EthernetClient cliente;
void setup() {
Serial.begin(9600); //setting the baud rate at 9600
Ethernet.begin(mac, ip);
dht.begin();
}
void loop() {
float hum = dht.readHumidity(); //Reading the humidity and storing in hum
float temp = dht.readTemperature(); //Reading the temperature as Celsius and storing in temp
float fah = dht.readTemperature(true); //reading the temperature in Fahrenheit
float heat_index = dht.computeHeatIndex(fah, hum); //Reading the heat index in Fahrenheit
float heat_indexC = dht.convertFtoC(heat_index); //Converting the heat index in Celsius
if (cliente.connect(serv, 8095)) { //Connecting at the IP address and port we saved before
Serial.println("connected");
cliente.print("GET /ethernet/data.php?"); //Connecting and Sending values to database
cliente.print("temperature=");
cliente.print(temp);
cliente.print("&humidity=");
cliente.print(hum);
cliente.print("&heat_index=");
cliente.println(heat_indexC);
//Printing the values on the serial monitor
Serial.print("Temperature= ");
Serial.println(temp);
Serial.print("Humidity= ");
Serial.println(hum);
Serial.print("Heat Index= ");
Serial.println(heat_indexC);
cliente.stop(); //Closing the connection
}
else {
// if you didn't get a connection to the server:
Serial.println("connection failed");
}
delay(5000);
}

     

    Code Explanation

    You need to make changes in the following two lines. Make sure that the Ethernet wire is connected to the Ethernet shield.

    byte ip[] = {192, 168, 1, 18 };
byte serv[] = {192, 168, 1, 5} ;

    In the Arduino IDE, go to files-Examples-Ethernet-DHCPAdressPrinter. A new window will open up, upload the code and in the serial printer, it will show you the IP Address. Enter it in “byte ip[] = {192, 168, 1, 18 };”

    DHCP IP Adress.PNG

    Then open the command prompt (cmd) and type in “ipconfig” and it will show you the IPv4 address. Enter it in “byte serv[] = {192, 168, 1, 5} ; ”

    IPv4 Adress.PNG

    The remaining code is explained with the comments.

    Step 5

    Displaying the Data

    To display the data, you will need to create another PHP file that will read the data from the database and will show it on the webpage.

    Create another PHP file with the name “display.php” and place it in the same folder where you have placed the other PHP files (C:\xampp\htdocs\ethernet\). Place the following code in the display.php and save the file.

    <?php
$url=$_SERVER['REQUEST_URI'];
header("Refresh: 5; URL=$url"); // Refresh the webpage every 5 seconds
?>
<html>
<head>
<title>Arduino Ethernet Database</title>
<style type="text/css">
.table_titles {
padding-right: 20px;
padding-left: 20px;
color: #000;
}
.table_titles {
color: #FFF;
background-color: #0000FF;
}
table {
border: 2px solid #333;
}
body { font-family: "Trebuchet MS", Courier; }
</style>
</head>
<body>
<h1>Arduino Data Logging to Database</h1>
<table border="0" cellspacing="0" cellpadding="4">
<tr>
<td class="table_titles">ID</td>
<td class="table_titles">Date and Time</td>
<td class="table_titles">Temperature</td>
<td class="table_titles">Humidity</td>
<td class="table_titles">Heat_index</td>
</tr>
<?php
include('connection.php');
$result = mysqli_query($con,'SELECT * FROM data ORDER BY id DESC');
// Process every record
$oddrow = true;
while($row = mysqli_fetch_array($result))
{
if ($oddrow)
{
$css_class=' class="table_cells_odd"';
}
else
{
$css_class=' class="table_cells_even"';
}
$oddrow = !$oddrow;
echo "<tr>";
echo "<td '.$css_class.'>" . $row['id'] . "</td>";
echo "<td '.$css_class.'>" . $row['event'] . "</td>";
echo "<td '.$css_class.'>" . $row['temperature'] . "</td>";
echo "<td '.$css_class.'>" . $row['humidity'] . "</td>";
echo "<td '.$css_class.'>" . $row['heat_index'] . "</td>";
echo "</tr>";
}
// Close the connection
mysqli_close($con);
?>
</table>
</body>
</html>

     

    Now type the following line in the browser, webpage with data will be shown like shown in below figure

    http://localhost:8095/ethernet/display.php

     

    display.PNG

     

    Tags: 201802, W5100, Ethernet, Arduino, Database, MySQL

     

    COMMENTS

    Please Login to comment
      Subscribe  
    Notify of
    POSTED BY
    Albin