Arduino UNO + Ethernet Shield + Temperature Sensor + Apache Server + SQL + PHP

?Hi everyone,

I am trying to upload temperature from temperature sensor to a sql database.
I am using temperature sensor, arduino uno, arduino ethernet shield and RJ 45 cable.
I am using Xampp(XAMPP Installers and Downloads for Apache Friends) database running on my PC. I have created a table(temp) in this database(temp_database). I have saved php files in my computer at C:xampp/htdocs/temp_database. I have hooked up Ethernet Shield to one of internet ports in my home. After all this, i don’t find table getting updated by temperature values. Is there any mistake? Please guide me.
Here is the source code for Arduino:

#include "SPI.h"
#include "Ethernet.h"

const int temperaturePin = A0;
byte mac[] = { 0x90, 0xA2, 0xDB, 0x0F, 0x3A, 0xDF };
byte ip[] = { 131, 247, 19, 140 };   //this is the ip address for Ethernet Shield
byte gateway[] = { 131, 225, 3, 254 };
byte subnet[] = { 255, 255, 252, 0 };
EthernetClient client ;
char server[] = "www.localhost.com";

void setup() {
// Open serial communications and wait for port to open:
Serial.begin(9600);
while (!Serial) {
; // wait for serial port to connect. Needed for Leonardo only
}
// start the Ethernet connection:
if (Ethernet.begin(mac) == 0) {
Serial.println("Failed to configure Ethernet using DHCP");
// no point in carrying on, so do nothing forevermore:
// try to congifure using IP address instead of DHCP:
Ethernet.begin(mac, ip);
}
// give the Ethernet shield a second to initialize:
delay(1000);
Serial.println("connecting...");
}

void loop()
{
if( client.connect(server, 80))
{
float tem = getTemp();
Serial.println( tem );
client.print( "GET /add.php?");
client.print("tempt1=");
client.print( tem );
client.println( " HTTP/1.1");
client.println( "Host: localhost" );
client.println( "Content-Type: application/x-www-form-urlencoded" );
client.println( "Connection: close" );
client.println();
client.println();
client.stop();
}
delay( 5000 );
}

float getTemp() {
float voltage, V,t ;
voltage = analogRead(temperaturePin) ;
V = voltage * 0.004882814 ;
t = (V - 0.5) * 100.0;
return t;
}

Here is the conec.php file

<?php
function Conection(){
if (!($link=mysql_connect("localhost","root","password"))) {
exit();
}
if (!mysql_select_db("temp_database",$link)){
exit();
}
return $link;
}
?>

Here is the add.php file:

<?php
include("conec.php");
$link=Conection();
$Sql="INSERT INTO 'temp'('temp1') VALUE ('".$_GET["temp1"]."')";
mysql_query($Sql,$link);
header("Location: insertareg.php");
?>

Here is the insertareg.php file:

<html>
<head>
<title>Data of Sensor</title>
</head>
<body>
<h1>Data from the temperature sensor</h1>
<form action="add.php" method="get">
<TABLE>
<tr>
<td>Temperature </td>
<td><input type="text" name="temp1" size="20" maxlength="30"></td>
</tr>
</TABLE>
<input type="submit" name="accion" value="Grabar">
</FORM>
<hr>
<?php
include("conec.php");
$link=Conection();
$result=mysql_query("select * from temp order by id desc",$link);
?>
<table border="1" cellspacing="1" cellpadding="1">
<tr>
<td>&nbsp;Temperature &nbsp;</td>
</tr>
<?php
while($row = mysql_fetch_array($result)) {
printf("<tr><td> &nbsp;%s </td></tr>", $row["temp1"]);
}
mysql_free_result($result);
?>
</table>
</body>
</html>

Your network settings are not correct. The gateway is unreachable.

Also, you can't use www.localhost.com for the server. Use the IP of the server instead. Insure you have modified the server firewall to allow port 80 access from the localnet.

You should read the response from the server. That might help you debug your sketch.

Hello Surfer ,
I have modified the network settings. Here is the modified code:

#include "SPI.h"
#include "Ethernet.h"

const int temperaturePin = A0;
byte mac[] = { 0x90, 0xA2, 0xDA, 0x0F, 0x3A, 0xDC };
byte ip[]  = { 131, 247,   19,  140 };   //this is ip address assugned for ethernet   
byte gateway[] = { 10, 226,   3,  254 };        
byte subnet[]  = { 255, 255, 252,   0 };
EthernetClient client ;
char server[] = "www.localhost.com";

void setup() {
// Open serial communications and wait for port to open:
Serial.begin(9600);
while (!Serial) {
; // wait for serial port to connect. Needed for Leonardo only
}
// start the Ethernet connection:
if (Ethernet.begin(mac) == 0) {
Serial.println("Failed to configure Ethernet using DHCP");
// no point in carrying on, so do nothing forevermore:
// try to congifure using IP address instead of DHCP:
Ethernet.begin(mac, ip);
}
// give the Ethernet shield a second to initialize:
delay(1000);
Serial.println("connecting...");
}

void loop()
{
if( client.connect(server, 80))
{
float tem = getTemp();
Serial.println( tem );
client.print( "GET /add.php?");
client.print("tempt1=");
client.print( tem );
client.println( " HTTP/1.1");
client.println( "Host: localhost" );
client.println( "Content-Type: application/x-www-form-urlencoded" );
client.println( "Connection: close" );
client.println();
client.println();
client.stop();
}
delay( 5000 );
}

float getTemp() {
float voltage, V,t ;
voltage = analogRead(temperaturePin) ;
V = voltage * 0.004882814 ;
t = (V - 0.5) * 100.0;
return t;
}

Also, you can't use www.localhost.com for the server. Use the IP of the server instead.

I did not understand regarding IP. When i opened www.localhost.com, i could see a server address in php admin tool (127.0.0.1). Is this is the server address i need to keep instead of URL?

Insure you have modified the server firewall to allow port 80 access from the localnet.

Is this a PC option or on localhost? Can u please explain me this clearly ?

You should read the response from the server. That might help you debug your sketch.

Sure I will do this.

Thanks
Omkar

Is the arduino connected straight to the internet, or your local network ?

If it is a local network, I would have expected something like

byte ip = { 192, 168, 0, 10 }; // Static IP in range of local networked DHCP release
byte gateway = { 192, 168, 0, 1 }; // IP of the router
byte subnet = { 255, 255, 255, 0 }; //Default subnet
EthernetClient client ;
char server = "192.168.0.3"; //The IP of the computer running Xamp

This assuming your router is 198.168.0.1 which is likely dealing out DHCP ips

I am not familair with the arduino ethernet codings, but looking at the codes, the above looks logical.

Hello Lennaert,

I have connected Ethernet Shield to local network and have run below code in Arduino. This gives you the ip address of your connected Arduino + Ethernet System. Here is the code:

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

// Enter a MAC address for your controller below.
// Newer Ethernet shields have a MAC address printed on a sticker on the shield
byte mac[] = { 0x90, 0xA2, 0xDA, 0x0F, 0x3A, 0xDC };

// Initialize the Ethernet client library
// with the IP address and port of the server 
// that you want to connect to (port 80 is default for HTTP):
EthernetClient client;

void setup() {
  // start the serial library:
  Serial.begin(9600);
  // start the Ethernet connection:
  if (Ethernet.begin(mac) == 0) {
    Serial.println("Failed to configure Ethernet using DHCP");
    // no point in carrying on, so do nothing forevermore:
    for(;;)
      ;
  }
  // print your local IP address:
  Serial.println(Ethernet.localIP());

}

void loop() {

}

char server = “192.168.0.3”; //The IP of the computer running Xamp

Should the server address be my computer’s address or one on the localhost database? localhost database has (127.0.0.1)

This assuming your router is 198.168.0.1 which is likely dealing out DHCP ips

My system is connected through WIFI. Does this make any change??

Thanks

If you were to check in your router on the LAN section, you will likely see the IP of the arduino (and your computer too) , just compare mac adresses.

Yes, you should add the IP from the network, the 192.168.0.3 variant, this so the Arduino knows to which computer to connect.

The 127.0.0.1 and variant addresses are for the local computers to refer to themselves locally.
Such as php my admin (a web app) referring to a local runnign msql server, either localhost, or 127.0.0.1

:slight_smile:

So the arduino needs to know the networked IP adres of the computer running the webserver and database.

And Wifi doesnt matter, its also LAN, WLAN that is :slight_smile:

Thanks a lot. Now, it makes me sense. I will try to keep do what you have advised.
I have another question:

Where should these php files be kept?? As of now i have kept these files in my PC at C/Xampp/htdocs. Is this fine??

Please advise !!

Thanks

The PHP scripts should be in the www folder.

Depanding on your installation this is likely /var/www or something like /home/userName/www (Assuming Xamp is the linux version)

Simply put, the root of your web server.

I was wondering why you have a form, do you sometimes manually add data ?

for sake of simplicity: remove the 2 php files, and use this script in a single file and call it add.php

/** new add.php **/

<?php error_reporting(E_ALL^E_NOTICE); $user = "root"; //User name to connect to sql $pass = "password"; //Password for sql user $host = "localhost"; // Mysql server is on same host as web server $database = "temp_database"; //Database name defined in phpmyadmin $table = "temp"; //Name of the table in database $columname = "temp1"; // name of column to hold readings, defined in the table, in the database mysql_connect($host,$user,$pass); mysql_select_db($database); mysql_query("insert into $table ($columname) values ('".$_GET['temp1']."') "); ?>

I haven’t tested it, but looks solid enough.
Make sure to look at what you want to write where, database, table, column.
If you have enabled error logging in Xamp, the log files in /var/log/apache (or httpd) and /var/log/mysql could help you along.

Here is a pointer: make sure your webserver ir running.
On a computer in the network, try and connect by IP to your web server.
You can also try it from your own computer (the one runnign xamp, but in linux security settings could allow a local user but deny network users)

Just open a browser and see if you get Xamp pages when you navigate to 192.168.0.3

Xampp is a windows application as i am using a windows PC.

Depanding on your installation this is likely /var/www or something like /home/userName/www (Assuming Xamp is the linux version)

I dont find these files in my Xampp.

for sake of simplicity: remove the 2 php files, and use this script in a single file and call it add.php

Thats a good idea.

They are not files, they are folders.

In windows: c:\wamp\www

On linux try form command line:

grep -i 'DocumentRoot' httpd.conf

and hit enter

There is no such folder in C:/Xampp !

googled

Instead of www, it looks like your looking for a 'htdocs' folder

If you are using Wamp then you should have in systray a W simbol and if you right click on it you sould see Open http folder or something like that.This will show you your root folder where your index.php file should be.
My advice for now is forget the arduino programming, just setup the php and make sure you are inserting values when you run your php files locally using (http://localhost/yourPhpFile.php/$yourVariable=14)
This should insert the values in mysql if all is ok.Then try to do the same thing in another computer in the same network has your pc where you have Xamp running.
Make sure now you use the ip address like 192.168.XXX.XXX not 127.0.0.1 since 127.0.0.1 refers to itself and you want access your Xamp server that is in another computer.This is an important concept you must understand!
Anyway try in the test computer open browser and visit http://192.168.XXX.XXX/yourPhpFile.php/$yourVariable=15
If you also can insert values in mysql then you can do the same in arduino now.Until you can't do this you will never be able to put it working.
Disable the firewall or open an exception to www service and make sure your apache is listen for external connections.THis is configured in apache.conf

Its getting complicated =( But, I will give a try :cold_sweat: . Thanks a lot to u all for your support.

Finally I achieved it :stuck_out_tongue: Uffff :smiley: Thanks everyone :slight_smile:

I'm glad to hear that.Perhaps you want describe what you have done to fix it to help another members when they search for a similar problem.
Happy coding...

I would like to create a blog and post the link here.

I will let you soon about that !

Thanks and regards,
Omkar

HugoPT:
I'm glad to hear that.Perhaps you want describe what you have done to fix it to help another members when they search for a similar problem.

I'll take a guess. He quit using localhost and 127.0.0.1 on the Arduino for the server and used the localnet IP instead as you and I both suggested. :wink:

Here is my blog on how to implement it:

Hi Omkardokur,

thank you for posting the blog! I am working on a similar project trying to get analog radiation data into a database. I am using 1X arduino UNO, 1X arduino ethernet shield, 1x analog sensor and a router.
I followed your instructions and slightly adapted your code to fit to my server settings (ip etc.). My problem: I didn’t get the sensor data into the database. please note that I am impelmenting the method of pushing data to the wamp server . here I share the code and I hope somebody could check it and tell if something is missing.
here the arduino code:

#include "SPI.h"
#include "Ethernet.h"

byte mac[] = { 0x90, 0xA2, 0xDA, 0x0F, 0x3A, 0xDC };
//byte ip[]  = { 192, 168,   1, 3 };   //this is ip address assugned for ethernet   
int  interval = 5000; 
EthernetClient client ;
char server[] = "192.168.1.77";
int rad =0;
void setup() {
// Open serial communications and wait for port to open:
Serial.begin(9600);
Ethernet.begin(mac);

  Serial.println("Tweaking4All.com - Radiation Drone - v2.0");
  Serial.println("-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-\n");
  Serial.print("IP Address        : ");
  Serial.println(Ethernet.localIP());
  Serial.print("Subnet Mask       : ");
  Serial.println(Ethernet.subnetMask());
  Serial.print("Default Gateway IP: ");
  Serial.println(Ethernet.gatewayIP());
  Serial.print("DNS Server IP     : ");
  Serial.println(Ethernet.dnsServerIP());
}  

/*if (Ethernet.begin(mac) == 0) {
Serial.println("Failed to configure Ethernet using DHCP");

Ethernet.begin(mac, ip);
}

delay(1000);
Serial.println("connecting...");
}}*/
void loop()
{
if( client.connect(server, 80)){
    Serial.println("-> Connected");
    // Make a HTTP request:
    client.print( "GET /testserver/rad/add_data.php?");
    client.print("serial=");
    client.print( "288884820500006X" );
    client.print("&&");
    client.print("radiation=");
    client.print( "12.3" );
    client.println( " HTTP/1.1");
    client.print( "Host: " );
    client.println(server);
    client.println( "Connection: close" );
    client.println();
    client.println();
    client.stop();
  }
  else {
    // you didn't get a connection to the server:
    Serial.println("--> connection failed/n");
  }

  delay(interval);
}

and here my php codes:
the dbconnect.php

<?php
$MyUsername = "root";  // enter your username for mysql
$MyPassword = "";  // enter your password for mysql
$MyHostname = "localhost";    // this is usually "localhost" unless your database resides on a different server

$dbh = mysql_pconnect($MyHostname , $MyUsername, $MyPassword);
$selected = mysql_select_db("cap_radiation",$dbh);
?>

the review_data.php

<?php 
    // Start MySQL Connection
    include('dbconnect.php'); 
?>

<html>
<head>
    <title>Arduino Radiation Log</title>
    <style type="text/css">
        .table_titles, .table_cells_odd, .table_cells_even {
                padding-right: 20px;
                padding-left: 20px;
                color: #000;
        }
        .table_titles {
            color: #FFF;
            background-color: #666;
        }
        .table_cells_odd {
            background-color: #CCC;
        }
        .table_cells_even {
            background-color: #FAFAFA;
        }
        table {
            border: 2px solid #333;
        }
        body { font-family: "Trebuchet MS", Arial; }
    </style>
</head>

    <body>
        <h1>Arduino Radiation Log</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">Sensor Serial</td>
            <td class="table_titles">Radiation in Celsius</td>
          </tr>
<?php
    // Retrieve all records and display them
    $result = mysql_query("SELECT * FROM radiation ORDER BY id ASC");

    // Used for row color toggle
    $oddrow = true;

    // process every record
    while( $row = mysql_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["sensor"].'</td>';
        echo '   <td'.$css_class.'>'.$row["celsius"].'</td>';
        echo '</tr>';
    }
?>
    </table>
    </body>
</html>

and here the add_data.php

<?php
    // Connect to MySQL
    include("dbconnect.php");

    // Prepare the SQL statement
    $SQL = "INSERT INTO rad_database.radiation(sensor ,celsius) VALUES ('".$_GET["serial"]."', '".$_GET["rad"]."')";     

    // Execute SQL statement
    mysql_query($SQL);

    // Go to the review_data.php (optional)
    header("Location: review_data.php");
?>