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.

75 thoughts on “Arduino: Save data to database

    1. Hi, sorry for the late reply. Regarding your error, it was due to bad formatting on the page, so if you copied the code directly into your IDE the quotation marks (” and ‘) would have some sort of formatting and the IDE would not regard them as quotations, but rather some characters and the code wouldn’t compile. I have repaired all the code snippets on the page so there should be no more errors.

  1. Hello, may I ask you for some advices? If I am going to replace the light sensor with a RFID module, which part of the code should I change?

    1. int photocellPin = 0;
      int photocellReading;

      photocellReading = analogRead(photocellPin);

      These are the lines of code used for light measurement, so you should change them with the desired code for RFID. Then, when you send the data don’t use
      client.print(photocellReading);
      instead, send data read from the RFID.

    1. Yes there is. What comes to mind are WiFi and GSM shields. Actually, you don’t have to use shields at all. You can use modules (module doesn’t stack on top of an Arduino). I frequently use GSM modules for these types of projects. With them I am able to send data over mobile data network from remote locations. Take a look at this:
      http://www.ebay.com/itm/SIM900-Quad-band-GSM-GPRS-Core-Mini-Wireless-data-transport-Module-for-Arduino-/271452511672

      1. Can you elaborate on the method with which I can send the data using GSM module as you did with Ethernet shield? And what will be the changes in the code if I have to use GPS data instead of the light sensor?

    1. The “connection failed” line is in the else { } part of the if statement. It means that the Arduino wasn’t able to connect to the server. What server are you using, web server or XAMPP? If you are using XAMPP you can try opening “localhost” from your browser just to be sure it’s running.

  2. Im having this error:

    Parse error: syntax error, unexpected ‘INTO’ (T_STRING) in /home/bde/public_html/write_data.php on line 15

    looks like the php file is having an error, could you please help me understand this?

    Thanks.

    1. I tested the “write_data.php” file and it works, but I was experiencing bad formatting on the page, so if you copied the code directly into your IDE/Notepad the quotation marks (” and ‘) would have some sort of formatting and the IDE/Notepad would not regard them as quotations, but rather some characters and the code wouldn’t work. I have repaired all the code snippets on the page so there should be no more errors.

  3. What is the use of 192.168.0.16 if we use xampp?

    I managed to send data to the databave via write_data.php :

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

    and display them via get_data.php :

    http://localhost/get_data.php (here I see the value 555 in ID and timestamps)

    Very well up to here.

    My problem is that I can’t send data via the Arduino sketch instead of writing in the URL: localhost/write_data.php?value=555 . (In the Serial monitor says: “failed connection”, meaning that something is going wrong in my sketch)

    My Arduino sketch simply reads the digitalPin 3. So I want to store in the db the on/off (0/1) values

    My IP4v is: 192.168.1.2 Should I use it? How should my Arduino sketch be??

    Thank u very much in advance…

    1. The 192.168.0.16 was the address that Arduino would take. The lines

      byte mac[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
      IPAddress ip(192,168,0,16);
      Ethernet.begin(mac, ip);

      are instructions for Arduino on how to connect to your network. It is not needed in all cases, for some shields and libraries support DHCP (automatic address assigning) but I have had problems using this approach so I always use the direct assigning of IP address to Arduino. You can take a look at this page:
      https://www.arduino.cc/en/Reference/EthernetBegin

  4. hi.Thank you for everything! but i have a problem. (STEP 3) when i run (localhost/write_data.php?value=100) value does not register the database. can you help me ?

    1. Hi, no problem, before tracing the error could you perhaps remake all the .php files? I had formatting issues within the code so if you copied it directly it would mess up all the quotation marks (” and ‘) and the code wouldn’t work. If you still have this issue when you update the files, let me know.

    2. hi.Thank you for everything! but i have a problem. (STEP 3) when i run not register the database.i edit all the quotation marks ( ” and ‘ ) all file . can you help me ?

      1. No problem. If I understood you correctly, you have a problem saving the actual data to database. Are you using a live web page hosting or XAMPP? Did you create the database through phpMyAdmin?

      2. hello
        My team is working on a project which deals with arduino , current sensor and sending data to database(wamp).
        Even faced the same problem as you have stated “data is not received in database” even though software and hardware components are working fine.

        Finally we found out that some small changes are to be made in the wamp- settings
        here is the link which helped us solve the problem :
        Refer the three steps(under the heading “4.Giving Access to Arduino to talk to server (Setup Access)”)

        http://arduinotemperatureserver.blogspot.in/2014/08/arduino-ethernet-shield-temperature.html

        I hope this will solve your problem.

  5. your post is awesome!
    but i have a question. if i want to use more than one sensor, where is the place for add command?can u give me an example?
    thanks

    1. Hi, if you would want to use more than one sensor, you must:
      1. prepare the database to accept 2 values (add 1 more column to the table)
      2. change the “write_data.php” file in the line where you write data into the database to something like this:
      $sql = “INSERT INTO test.sensor (value1, value2) VALUES (‘”.$_GET[“value1″].”, “.$_GET[“value2″].”‘)”;
      3. change the Arduino sketch so you write 2 values in the GET request like this:
      int value1 = analogRead(pin1);
      int value2 = analogRead(pin2);
      if (client.connect(server, 80)) {
      client.print(“GET /write_data.php?”);
      client.print(“value1=”);
      client.print(value1);
      client.print(“&value2=”);
      client.print(value2);
      client.println(” HTTP/1.1″);
      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”);
      client.println();
      client.println();
      client.stop();
      }

      P.S. Please mind the quotation marks (‘ and “) when pasting, there could be formatting that affect code.

      1. Hi, thanks for the tutorial. I tried to write the php code to send 2values to mysql

        $sql = “INSERT INTO test.sensor (value1, value2) VALUES (‘”.$_GET[“value1″].”, “.$_GET[“value2″].”‘)”;

        when I try to open this link: (localhost/write_data.php?value1=100&value2=234) writes nothing to mysql.

        Please, can you help me?

      2. I used your example and now have an arduino posting to my database with a mercury tilt switch and a magnetic reed switch. You’ll need to add an ampersand in your GET statement for it to work or the data will only get entered into the value1 column as “value 1″,value2=”value 2”. Took me a long time to figure out, but this is what I came up with and it works:

        client.print(“GET /cats/write_data.php?”); // This
        client.print(“value1=”); // This
        client.print(value1Reading); //
        client.print(“&”); // This
        client.print(“value2=”); // This
        client.print(value2Reading);
        client.println(” HTTP/1.1″

  6. Hi, thanks for this detailed tutorial, it’s helped me a lot!

    However, I’m stuck, this is what I’ve got:

    if (client.connect(serverIP,80)) {
    Serial.println(“connection succeeded”);
    client.print(“GET /pruebaArduino1/readingCard.php?”);
    client.print(“data=”);
    client.print(data);
    client.println(” HTTP/1.1″);
    client.println(“Host: 192.168.1.102”);
    client.println(“Connection: close”);
    client.println();
    client.println();
    client.stop();
    }else{
    Serial.println(“connection failed”);
    }
    delay(10000);

    data is a String that contains a RFID, then my readingCard.php is:

    <?php
    if ($_SERVER['REQUEST_METHOD'] == 'GET') {
    if(isset($_GET["data"])){
    echo("Got by ARDUINO “);
    $ID_tag = $_GET[‘data’];
    echo($ID_tag);
    }
    }
    ?>

    Pop up

    Slice your card in front of sensor…

    But I’m never goint into that if statement [ if(isset($_GET[“data”])) ], would you help me with this please?

    I’m running Xampp, still haven’t mounted any database, I just want to get the RFID into the variable data in my .php.

    Thanks in advance.

  7. Hello i keep getting connection failed i got my ip address using ipconfig placed that in the char server line and its still not able to connect.

    1. No problem, could you post your code?
      When you say “you got your ip address using ipconfig”, what I think is that you set up your Arduino to use the same address as your computer?

      1. i am basically using my surface as a server and i am using a router to connect my ethernet shield and my laptop to xampp could this be the problem?

  8. #include
    #include

    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(193, 168, 0, 16);

    int PV_ID = ‘0’; // Analog input pin on Arduino we connected the SIG pin from sensor
    //int PVReading; // Here we will place our reading

    char server[] = “193.168.0.125”; // 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() {

    //PVReading = analogRead(PV_ID); // 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(“PV_ID=”); // This
    client.print(PV_ID); // 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: 193.168.0.125”); // 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);
    }

    I am just trying to send a integer value to the my server but arduino keeps failing to connect

  9. Hi,

    I Have data in my server (add.php) which code as echo values . so now i want to get this date from arduino and print the value (1) in my arduino serial monitor, with help of them i need to switch on my LED ON.

    Please help me out and thanks in advance.

  10. Hello and many many thanks for the article. It is well written and helpful.

    I have performed all the steps including the arduino sketch for a setup with a wifi shield.

    When I run it on the Arduino I get connected to the server but no value is saved in the database.

    I have tried everything with no luck at all and I’m struggling as I need to have it ready by Monday for an assignment. Any help would be highly appreciated! 🙂

    My code is the following:

    [….
    // Connect to the server (your computer or web page)
    if (client.connect(server, 90)) {
    Serial.println(“–> connection ok\n”);
    client.print(“GET /write_data.php?”); // This
    client.print(“value=”); // This
    client.print(hp1); // 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.1.8”); // 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( “Content-Type: application/x-www-form-urlencoded” );
    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
    Serial.println(“–> finished transmission\n”);
    }
    else {
    // If Arduino can’t connect to the server (your computer or web page)
    Serial.println(“–> connection failed\n”);
    }
    }
    ….]

  11. Hey…I am facing this Error. //i am using wamp server
    Light sensor readings

    ID Timestamp Value
    ( ! ) Deprecated: mysql_pconnect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead in C:\wamp\www\Code\getdata.php on line 26
    Call Stack
    # Time Memory Function Location
    1 0.0000 135432 {main}( ) ..\getdata.php:0
    2 0.0000 135920 mysql_pconnect ( ) ..\getdata.php:26

    ( ! ) Warning: mysql_pconnect(): No connection could be made because the target machine actively refused it. in C:\wamp\www\Code\getdata.php on line 26
    Call Stack
    # Time Memory Function Location
    1 0.0000 135432 {main}( ) ..\getdata.php:0
    2 0.0000 135920 mysql_pconnect ( ) ..\getdata.php:26

    ( ! ) Warning: mysql_select_db() expects parameter 2 to be resource, boolean given in C:\wamp\www\Code\getdata.php on line 27
    Call Stack
    # Time Memory Function Location
    1 0.0000 135432 {main}( ) ..\getdata.php:0
    2 1.0387 136496 mysql_select_db ( ) ..\getdata.php:27

    ( ! ) Notice: Undefined variable: con in C:\wamp\www\Code\getdata.php on line 30
    Call Stack
    # Time Memory Function Location
    1 0.0000 135432 {main}( ) ..\getdata.php:0

    ( ! ) Warning: mysqli_query() expects parameter 1 to be mysqli, null given in C:\wamp\www\Code\getdata.php on line 30
    Call Stack
    # Time Memory Function Location
    1 0.0000 135432 {main}( ) ..\getdata.php:0
    2 1.0557 136664 mysqli_query ( ) ..\getdata.php:30

    ( ! ) Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, null given in C:\wamp\www\Code\getdata.php on line 34
    Call Stack
    # Time Memory Function Location
    1 0.0000 135432 {main}( ) ..\getdata.php:0
    2 1.0637 136680 mysqli_fetch_array ( ) ..\getdata.php:34

    ( ! ) Notice: Undefined variable: con in C:\wamp\www\Code\getdata.php on line 45
    Call Stack
    # Time Memory Function Location
    1 0.0000 135432 {main}( ) ..\getdata.php:0

    ( ! ) Warning: mysqli_close() expects parameter 1 to be mysqli, null given in C:\wamp\www\Code\getdata.php on line 45
    Call Stack
    # Time Memory Function Location
    1 0.0000 135432 {main}( ) ..\getdata.php:0
    2 1.0747 136752 mysqli_close ( ) ..\getdata.php:45

  12. hey… i am trying to track locations and speed and other info of a vehicle and store them in database but can i do it without ethernet or wifi sheild?? i have a sim900a gsm module can i use that instead?

  13. CREATE TABLE ‘test’.’sensor’ (

    ‘id’ INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ‘time’ TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ‘value’ VARCHAR( 10 ) NOT NULL
    )
    showing error
    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near

  14. Is it mandatory to have Ethernet shield ?
    I’m using without Ethernet shield and the result I’m getting is connection failed.

  15. Hello,
    Thanks for your great tutorial!

    I keep getting many problems though, if you could please help/advise me:

    Step 3, when I try to insert -http://localhost/write_data.php?value=100- on the browser, I keep getting a fatal error message for undefined method. After a bit of google research, I found out that I had to edit php.ini, according to this post http://stackoverflow.com/questions/5179535/fatal-error-call-to-undefined-function-mysql-connect-in-c-apache-htdocs-test, so that kind of solved that and I get the entry in the database.
    But even with the above solution I cannot connect to the database with a custom user I create if the user has a password, even though all privileges are granted, I keep getting a message access denied for user using password. I can connect though without a password.
    Third, and most importantly, I tested the previous article “display data over local network” and did everything correctly and it works fine, but when I run the above code for arduino I keep getting the connection failed message.
    Any suggestions would be much appreciated
    Thanks

  16. Hi admin,

    very decent tutorial and neat code. I used your aduino code and merged it with my temperature sensor using ethernet W5100 Module. It worked fine , but I am using a real web server. I set the delay to 20 seconds for the database to capture the sent values, but in phpmyadmin the time difference between each value is more than a minute. Is it related to the speed of the server I am using or can something be done in the code to reduce the difference ? Also, what’s the purpose of the two lines client.println(); before closing the connection ?

  17. help please !
    mysqli_query() expects at least 2 parameters, 1 given in C:\wamp\www\test.php !!
    when i try my php file !!

  18. Hi ! thank you for your help all the php files work so well for me until we get to the arduino code part , it would connect on xamp but not on a live webpage also in both cases it won’t add anything to my database any idea why ? and if i want to update my database in the same field not in a table style what should i use? thanks in advance.

  19. Can we able to send data from arduino to database without using any shield or any gsm module. I mean directly from arduino?

    1. Hi, im using feather huzzah esp8266 and dht22 the php scripts work fine but the arduino code doesnt write to sql database. I just want to write value 1 to start with then use the results from the dht22 can you please help? code is as follows:

      #include
      #include

      // Wifi Connection
      char ssid[] = “TELE2-F1A03B”; // your network SSID (name)
      char pass[] = “F32B722333”; // your network password

      IPAddress server(192,168,1,77); // laptop

      // Initialize the Wifi server library
      WiFiClient client;

      void setup(void)
      {
      Serial.begin(9600);

      WiFi.begin(ssid, pass);

      while (WiFi.status() != WL_CONNECTED)
      {
      delay(500);
      Serial.print(“.”);
      }
      Serial.println(“”);
      Serial.println(“WiFi connected”);

      // print your WiFi shield’s IP address:
      Serial.print(“IP Address of ESP8266: “);
      Serial.println(WiFi.localIP());

      if (client.connect(server, 80))
      {
      Serial.print(“–> connected to server “);
      Serial.println(server);
      client.print(“GET /write_data.php?”);
      client.print(“value=”);
      client.print(“100″);
      client.println(” HTTP/1.1″);
      client.print(“Host: 192.168.1.77”);
      client.println(“Connection: close”);

      Serial.print(“GET /write_data.php?”);
      Serial.print(“value=”);
      Serial.print(“100″);
      Serial.println(” HTTP/1.1″);
      Serial.println(“Host: 192.168.1.77”);
      Serial.println(“Connection: close”);

      client.println();
      client.println();
      client.stop();
      Serial.println(“–> finished transmission\n”);
      }
      else
      {
      Serial.println(“–> connection failed\n”);
      }
      }
      void loop()
      {

      }

  20. here my code my problem are only the id and the time are printed but when assigning a value they send to my database. when i try this code client.print(“GET/project.php?sensor1=100&sensor2=500&sensor3=700”) it works?

    #include
    #include

    byte mac[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
    // if you don’t want to use DNS (and reduce your sketch size)
    // use the numeric IP instead of the name for the server:
    //IPAddress server(192, 168, 0, 101); // numeric IP for Google (no DNS)

    char server[] = “192.168.0.101”;
    // Set the static IP address to use if the DHCP fails to assign
    IPAddress ip(192, 168, 0, 100);

    // 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;

    float sensor1 = 0;

    float sensor2 = 0;

    float sensor3 = 0;

    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…”);

    // if you get a connection, report back via serial:

    }

    void loop()
    {

    if (client.connect(server, 80)) {
    Serial.println(“connected”);

    sensor1 = sensor3 + 5;
    sensor2 = sensor1 + 5;
    sensor3 = sensor2 + 5;

    client.println(“GET /project/data.php?”);
    client.print(“sensor1=”);
    client.print(sensor1);
    client.print(“&&sensor2=”);
    client.print(sensor2);
    client.print(“&&sensor3”);
    client.print(sensor3);
    client.println(“HTTP/1.1”);
    client.println(“Host: 192.168.0.101”);
    client.println(“Connection: close”);
    client.println();
    client.stop();
    delay(5000);
    }
    else {
    // kf you didn’t get a connection to the server:
    Serial.println(“connection failed”);
    client.stop();
    }
    }

  21. i have a problem
    my code runs fine but when ever i try get_data.php it gives no internet connectivity error instead of showing result..
    plz help

  22. SQL query:

    CREATE TABLE ‘test’.’sensor’ (

    ‘id’ INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ‘time’ TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ‘value’ VARCHAR( 10 ) NOT NULL
    I have these massage erre when i apply to copy and past in SQL can you tell me what is the problem please i very need you help
    thanks

    )
    MySQL said: Documentation

    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ”test’.’sensor’ (

    ‘id’ INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ‘time’ TIMES’ at line 1

  23. Whenever I enter the mysql code it says that I have a syntax error. Not sure where the error lies but would love some help.

    CREATE TABLE test.sensor {

    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    value VARCHAR( 10 ) NOT NULL
    }
    MySQL said: Documentation

    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘{

    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    time TIMESTAMP NOT NULL DEFAUL’ at line 1
    Open new phpMyAdmin window

  24. Hi.
    We are doing a project which needs to send data from Arduino+WiFi module to a web2py server. Does the method work for that too?
    Thank you.

  25. thank you for everything….
    everything is fine but the data don’t writen on database…

    my code>>
    #include
    #include
    byte mac[] = { 0x00, 0xAA, 0xBB, 0xCC, 0xDE, 0x02 };
    IPAddress ip( 192,168,*,*** );

    char server[] = “192,168,*,***”;

    float sensor1 =0;
    float sensor2 =0;
    float sensor3 =0;

    EthernetClient cliente;

    void setup()
    {
    Serial.begin(9600);
    Ethernet.begin(mac,ip);
    Serial.println(Ethernet.localIP());
    }

    void loop()
    {
    if( cliente.connect (server,8095))
    { Serial.println(“connected”);

    sensor1 =sensor3 +5;
    sensor2=sensor1+5;
    sensor3=sensor2+5;

    // Make a HTTP request:
    cliente.print(“GET /sensores/insert.php?”);
    cliente.print(“sensor1=”);
    cliente.print(sensor1);
    cliente.print(“&sensor2=”);
    cliente.print(sensor2);
    cliente.print(“&sensor3=”);
    cliente.println(sensor3);
    cliente.println(” HTTP/1.1″) ;
    cliente.println(“Host: 192.168.*.***”);
    cliente.println(“Connection: close”);

    Serial.print(“sensor1= “);
    Serial.println(sensor1);
    Serial.print(“sensor2=”);
    Serial.println(sensor2);
    Serial.print(“sensor3=”);
    Serial.println(sensor3);

    // cliente.stop();

    cliente.stop();

    }else {Serial.println(“disconnected”);
    cliente.stop();
    }

    delay(5000);
    }

  26. i am trying to interface bmp180 sensor value to website i am using xampp but sensor values are not uploading to website can anyone help me?

    #include
    #include
    #include
    #include
    #include
    SFE_BMP180 pressure;
    #define ALTITUDE 1655.0
    Servo myservo;

    byte mac[] = {
    0x98, 0x4F, 0xEE,0x01, 0x65, 0x4E };
    IPAddress ip(192,168,19,170);
    String readString;

    // 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,7);

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

    //EthernetServer server(80);
    int i=0;
    int j=0;
    int counter=0;
    int count=0;
    int temp,t1,t2;
    int mincount=0;
    char server[] = “192.168.19.171”; // 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() {

    pressure.begin();
    Serial.begin(9600);
    while (!Serial) {
    ; // wait for serial port to connect. Needed for Leonardo only
    }
    // Serial.begin starts the serial connection between computer and Arduino
    Serial.begin(9600);

    // start the Ethernet connection
    Ethernet.begin(mac,ip);

    }

    void loop() {

    char status;
    double T,P,p0,a;

    status = pressure.startTemperature();

    if (status != 0)
    {
    // Wait for the measurement to complete:
    delay(status);

    // Retrieve the completed temperature measurement:
    // Note that the measurement is stored in the variable T.
    // Function returns 1 if successful, 0 if failure.

    status = pressure.getTemperature(T);
    if (status != 0)
    {

    status = pressure.startPressure(3);
    if (status != 0)
    {
    //a = pressure.altitude(P,p0);
    // Wait for the measurement to complete:
    delay(status);

    status = pressure.getPressure(P,T);
    if (status != 0)
    {
    // Print out the measurement:

    p0 = pressure.sealevel(P,ALTITUDE);
    a = pressure.altitude(P,p0);

    }
    }
    }
    }
    //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)) {
    Serial.println(“Connection: done”);
    client.print(“GET /xampp/smart/data.php?T=4&P=33”); // This
    client.print(“332”); // This
    //client.print(photocellReading);
    //client.print(“&status=”);
    //client.print(“43″); // 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.0″); // Part of the GET request
    client.println(“Host:192.168.19.171”); // 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(1000);
    }

  27. Hi,

    I have a problem to send the data from arduino to database. The serial monitor shows “You don’t have permission to access /get_data.php on this server”. How to I solve this problem? Thanks a lot

  28. Hi thanks for this excelent posting. I am doing something similar using the wifi shield of the arduino here’s my code:
    #include // For the Wifi Shield of Arduino
    #include // For accessing to MySQL
    #include

    // MySQL database connection:
    byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED }; // It can be anything as long as it follows this structure
    IPAddress server_addr(192,168,137,200); // IP of the MySQL server here (ipconfig in cmd and look IPV4)
    char user[] = “dsm501a_project”; // MySQL user login username
    char password[] = “12345”; // MySQL user login password

    // Wifi connection
    char ssid[] = “tuhhmechatronicssebas”; // your SSID
    char pass[] = “12345678901”; // your SSID password

    WiFiClient client;

    int A = 1;

    void setup() {

    Serial.begin(9600);
    while (!Serial); // wait for serial port to connect. Needed for Leonardo only

    // Begin WiFi section
    int status = WiFi.begin(ssid, pass);
    if ( status != WL_CONNECTED) {
    Serial.println(“Couldn’t get a wifi connection”);
    while(true);
    }
    else {
    Serial.println(“Connected to network”);
    IPAddress ip = WiFi.localIP();
    Serial.print(“My IP address is: “);
    Serial.println(ip);
    }

    }

    void loop() {

    if (client.connect(server_addr, 80)) {
    client.print(“GET /write_data.php?”); // This
    client.print(“value=”); // This
    client.print(A); // 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″);
    client.println(“Host: 192.168.137.200”);
    client.println(“Connection: close”);
    client.println();
    client.println();
    client.stop();
    Serial.println(A);
    }

    else {
    // If Arduino can’t connect to the server (your computer or web page)
    Serial.println(“–> connection failed\n”);
    client.stop();
    }

    // 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);
    A++;
    }

    But the moment I run the sketch no data is saved in the mysql database. What is happening? By the way the php files work correctly. I don’t have XXAMP I have MySQL Workbench but I have WAMP Server with php admin, apache, etc….
    Please help me and thanks in advance.
    Regards,
    Sebastian

  29. Hi, I have a question about the SIM900, do you know how to save data with SIM900? I tried a lot of thing but I don’t know how to do it, I post my code and maybe you can help me. I tried with HTTPPARA and CIPSTART (this is commented).

    #include
    SoftwareSerial SIM900(7, 8); // Configura el puerto serial para el SIM900

    byte statusLed = 13;
    byte sensorInterrupt = 0; // 0 = digital pin 2
    byte sensorPin = 2;

    void setup()
    {
    SIM900.begin(19200);
    delay(25000); //Retardo para que encuentra a una RED
    Serial.begin(19200); /
    Serial.println(“OK”); //Mensaje OK en el arduino, para saber que todo va bien.

    // Set up the status LED line as an output
    pinMode(statusLed, OUTPUT);
    digitalWrite(statusLed, HIGH);

    pinMode(sensorPin, INPUT);
    digitalWrite(sensorPin, HIGH);
    }

    void loop()
    {
    SubmitHttpRequest();
    }

    void SubmitHttpRequest()
    {
    SIM900.println(“AT+CGMR”);
    delay(100);
    ShowSerialData();

    SIM900.println(“AT+CSQ”);
    delay(100);
    ShowSerialData();

    SIM900.println(“AT+CGATT?”);
    delay(100);
    ShowSerialData();

    SIM900.println(“AT+CSTT=?”);
    delay(100);
    ShowSerialData();

    SIM900.println(“AT+SAPBR=3,1,\”CONTYPE\”,\”GPRS\””);
    delay(1000);
    ShowSerialData();

    SIM900.println(“AT+SAPBR=3,1,\”APN\”,\”web.colombiamovil.com.co\””);
    delay(4000);
    ShowSerialData();

    SIM900.println(“AT+SAPBR=1,1,”);
    delay(2000);
    ShowSerialData();

    SIM900.println(“AT+HTTPINIT”);
    delay(2000);
    ShowSerialData();

    /*SIM900.println(“AT+CIPSTART=\”TCP\”,\”http://xxx.xxx.xxx.xxx/InsertTemp2.php?fe=2016-11-29 11:17:00&fl=1&ca=1&lo=0.1&la=0.1\”,\”8081\””);
    delay(5000);
    ShowSerialData();

    SIM900.println(“AT+CIPSHUT”); //init the HTTP request
    delay(2000);
    ShowSerialData();*/

    SIM900.println(“AT+HTTPPARA=\”CID\”,1″);
    delay(1000);
    ShowSerialData();

    SIM900.println(“AT+HTTPPARA=\”URL\”,\”http://xxx.xxx.xxx.xxx:8081/InsertTemp2.php?fe=2016-11-29 11:17:00&fl=1&ca=1&lo=0.1&la=0.1\””);
    delay(4000);
    ShowSerialData();

    SIM900.println(“AT+HTTPACTION=0”);
    delay(50000);
    ShowSerialData();

    SIM900.println(“AT+HTTPREAD”);
    delay(300);
    ShowSerialData();

    SIM900.println(“AT+HTTPTERM”);
    delay(5000);
    ShowSerialData();

    SIM900.println(“”);
    delay(100);
    }

    void ShowSerialData()
    {
    while(SIM900.available()!=0)
    Serial.write(char (SIM900.read()));
    }

  30. nice work!
    can you send me a code.. without using ethernet.. only take sensor from arduino put it into database and display through PHP ?

  31. Hi

    I’m sorry if this is a newbie question, I’m very new at this, apologies and your kind attention would be greatly appreciated, sir.
    I’m stuck at Step Create table. when I try to run the SQL code in PHPMyAdmin I get this error.

    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ”test’.’sensor’ ( ‘id’ INT NOT NULL AUTO_INCREMENT PRIMARY KEY, ‘time’ TIMES’ at line 1

    What exactly am I doing wrong here?

  32. My project is automatic irrigation system i should display both soil moisture value and water level of the tank.
    what should i do ?

  33. Hey,
    How can I setup communication between arduino and xampp without using ethernet shield instead doing it via Zigbee. What are the steps that i need to follow? Since in above comments you had mentioned about Wifi and GSM modules, but is this possible with Zigbee?

  34. Hello.
    I have read so many pages and they seem to all mention about using the Ethernet Shield.
    Is there a method to connect an Arduino to a phpmyadmin without using the Ethernet Shield?

  35. Hello
    Thank you so much for your tip, but I want to have a wireless sensor network with probee ze10, would you please help me for inserting data in mysql database through zigbee coordinator?
    Regards

  36. sir my project is fingerprint attendence system using arduino and xamp server..and i wanna use wifi to transfer data from arduino to xmap..plz help me wid the procedure and code..plzzzzz sir… i need to submit my final year proj till the end of this month otherwise i will fail..plz help sir..

Leave a Reply

Your email address will not be published. Required fields are marked *