arduino data to MySQL with php

Hi All,

I’m trying to make my own “smart electricity meter” where the arduino is detecting the rotating disk on the electricity meter and gives pulses. The end product should be a website that shows total kWh usage, current Watts etc.

I found numerous examles on the internet which i have tried to follow. Until now, I didn’t manage to get the data from the arduino to the MySQL database.

I’m wondering if someone can look over my codes and give me some hints where i’m going wrong.

arduino skechts;

/*
deze sketch is gemaakt om een "ouderwetse" kWh meter met draaischijf
uit te lezen en het aantal pulsen naar een MySQL database door te zenden

layout; sensor is op analoge pin 5 geprikt
Ethernet shield attached to pins 10, 11, 12, 13
*/

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

int sensorPin = A5;
int sensorValue = 0;
int ledPin = 2;
int threshold = 650;
int pulse = 0;
int kWh = 0;
int prevMillis = 0;
int previousMillis = 0;
int pulseTime = 0;
int curWatts = 0;
int Wh = 0;
int sendTime = 60000;
bool connected = false;


//Zet MAC adres vast en start ethernet client met verkregen IP adres
byte mac[] = { 0x00, 0xAB, 0xBA, 0xBC, 0xDD, 0x02 };
EthernetClient client;


void setup() {
pinMode(ledPin, OUTPUT);

  // Open serial communicatie en wacht op poort opening:
  Serial.begin(9600);

  // start Ethernet verbinding:
  if (Ethernet.begin(mac) == 0) {
    Serial.println("Failed to configure Ethernet using DHCP");
    // Geen zin om verder te gaan, dus doe niks:
    for(;;)
      ;
  }
}


void loop() {
//voer meting uit
sensorValue = analogRead(sensorPin);

//SensorToSerial();//zend ruwe sensordata naar serial port, gebruikt
om threshold te bepalen

if (sensorValue > threshold) {
Blink();
PulseCount();
CalcKWH();
pulseTimeCount();
CalcWatt();
Serial.println ();

DataToMySQL();
}

}

//----------------------BLOCKS--------------------------

void SensorToSerial(){
//zend meting naar serial monitor 10 x per sec
Serial.print(sensorValue);

//------------------------------------------------------

if(sensorValue > threshold) {
  Serial.print("High");
Serial.println();
delay(100);
}
}

//------------------------------------------------------

void PulseCount(){
pulse ++;
delay(1000);
Serial.print ("Puls #; ");
Serial.print (pulse);
Serial.print ("  ");
}

//------------------------------------------------------

void Blink() {
  digitalWrite(ledPin, HIGH);
  delay(1000);
  digitalWrite(ledPin, LOW);
}

//------------------------------------------------------

void CalcKWH(){
  float Wh = (pulse / 0.375);
Serial.print(Wh);
Serial.print(" Wh  ");
}

//------------------------------------------------------

void pulseTimeCount() {
  pulseTime = ((millis() - prevMillis));
  prevMillis = millis();
  Serial.print ("pulseTime ");
  Serial.print (pulseTime/1000);
  Serial.print (" sec,");
}

//------------------------------------------------------

void CalcWatt(){
  curWatts = (1350/(pulseTime*0.0001));
 Serial.print (" Verbruik ");
 Serial.print (curWatts);
 Serial.print (" Watt");
}

//------------------------------------------------------

void DataToMySQL(){

connected = true;

client.print("GET 192.168.1.75/add_data.php?");


client.print(pulseTime); //pulsetijd in sec
client.print(Wh); //verbruik in Wh
client.print(curWatts);//Huidige wattage verbruik


client.println(" HTTP/1.1 200 OK");
client.println("Host:192.168.1.75");
client.println("User-Agent: Arduino");
client.println("Content-Type: text/html");
client.println("Refresh: 5"); //refresh page every 5 sec
client.println("Connection: close");
client.println();
client.println();
client.stop();
connected = false;

//delay(sendTime);
}

add_data.php;

<?php
    

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

// Prepare the SQL statement
    
mysql_query  ("INSERT INTO energie.kWh_meter (Aantal_kWh ,Pulsen_minuut, Watt) VALUES ('".$_GET["pulseTime"]."', '".$_GET["Wh"]."', '".$_GET["curWatts"]."')");    
    


?>

dbconnect.php;

<?php

$Username = "arduino";  // enter your username for mysql

$Password = "password";  // enter your password for mysql

$Hostname = "localhost";      // this is usually "localhost" unless your database resides on a different server


$dbh = mysql_connect($Hostname , $Username, $Password);

$selected = mysql_select_db("energie",$dbh);


?>

MySQL setup; databank name; energie, Table; kWh_meter.

Thanks alot in advance!!

p.s. sorry that some comments are in Dutch. If translating is required, i’m happy to do so.

I dont think you are forming your http request properly. See the updatethingspeak() function from https://github.com/iobridge/ThingSpeak-Arduino-Examples/blob/master/Ethernet/Arduino_to_ThingSpeak.ino for an example.

You appear to be sending data before the headers rather than after and you have the wrong content-type. Also you will have to format your data into a string of name-value pairs before sending something like

client.print("pulseTime="); client.print(pulseTime); //pulsetijd in sec client.print("&Wh="); client.print(Wh); //verbruik in Wh client.print("&curWatts="); client.print(curWatts);//Huidige wattage verbruik

Although you have to pre-calculate the length of data and send it as part of the header so this wont work as I have written it. This page will help you with making it into a String() http://www.arduino.cc/en/Tutorial.StringAdditionOperator

If you examine the log files of your web server it may help you track down whats wrong.

Thanks alot for your reply!
I followed your lead and after a lot of extra google-ing now have a working system; Arduino is reading the turning disk on the electricity meter, it sends it to the mysql database and i can view the raw data on my website.
For other people that want to create the same system, below are the codes I used;

Arduino sketch;

/*
deze sketch is gemaakt om een "ouderwetse" kWh meter met draaischijf
uit te lezen en het aantal pulsen naar een MySQL database door te zenden

layout; sensor is op analoge pin 5 geprikt
Ethernet shield attached to pins 10, 11, 12, 13
*/

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

int bstate = 0;
int sensorPin = A5;
int sensorValue = 0;
int ledPin = 2;
int threshold = 650;
int pulse = 1;
int kWh = 0;
int prevMillis = 0;
int previousMillis = 0;
int pulseTime = 0;
int curWatts = 0;
int Wh = 0;
int mWh = 0;
int sendTime = 60000;
String txData ="";

//Zet MAC adres vast en start ethernet client met verkregen IP adres
byte mac[] = { 0x00, 0xAB, 0xBA, 0xBC, 0xDD, 0x02 };
char server[] = "192,168,1,75 ";

EthernetClient client;


void setup() {
pinMode(ledPin, OUTPUT);

  // Open serial communicatie en wacht op poort opening:
  Serial.begin(9600);

Ethernet.begin(mac);
delay(1000);
startEthernet();
}


void loop() {
//voer meting uit
delay(1000);
sensorValue = analogRead(sensorPin);

//SensorToSerial();//zend ruwe sensordata naar serial port, gebruikt
om threshold te bepalen
//delay (1000);


if (sensorValue > threshold) {
Blink();
PulseCount();
CalcKWH();
pulseTimeCount();
CalcWatt();
Serial.println ();
DataToMySQL();
}
}

//----------------------BLOCKS--------------------------

void SensorToSerial(){
//zend meting naar serial monitor 10 x per sec
Serial.print(sensorValue);

if(sensorValue > threshold) {
  Serial.print("High");
Serial.println();
delay(100);
}
}

//------------------------------------------------------

void PulseCount(){
//pulse ++;
//delay(1000);
Serial.print ("Puls #; ");
Serial.print (pulse);
Serial.print ("  ");
}

//------------------------------------------------------

void Blink() {
  digitalWrite(ledPin, HIGH);
  delay(1000);
  digitalWrite(ledPin, LOW);
}

//------------------------------------------------------

void CalcKWH(){
  float Wh = (pulse / 0.375);
  int mWh = Wh*1000;
Serial.print(mWh);
Serial.print(" mWh  ");
}

//------------------------------------------------------

void pulseTimeCount() {
  pulseTime = ((millis() - prevMillis));
  prevMillis = millis();
  Serial.print ("pulseTime ");
  Serial.print (pulseTime/1000);
  Serial.print (" sec,");
}

//------------------------------------------------------

void CalcWatt(){
  curWatts = (1350/(pulseTime*0.0001));
 Serial.print (" Verbruik ");
 Serial.print (curWatts);
 Serial.print (" Watt");
}

//------------------------------------------------------

void DataToMySQL(){

bstate++;

txData = "pulseTime="+ (String (pulseTime/1000)) + "&mWh=" + (String
(2666)) + "&curWatts="+(String(curWatts));

EthernetClient client;

if (client.connect("192.168.1.75", 80))
{
  Serial.print("Connected to MySQL server. Sending data...");

  client.print("POST /add_data.php HTTP/1.1\n");
  client.print("Host: 192.168.1.75\n");
  client.print("Connection: close\n");
  client.print("Content-Type: application/x-www-form-urlencoded\n");
  client.print("Content-Length: ");
  client.print(txData.length());
  client.print("\n\n");
  client.print(txData);
  Serial.println("Successfull");
}

  else{
  Serial.println("Connection failed");
  Serial.println();

}
}

//----------------------------------------------------
void startEthernet()
{
 client.stop();

Serial.println("Connecting Arduino to network...");

delay (1000);

//connect to network and obtain an IP address using DHCP
if (Ethernet.begin(mac) ==0)
{
  Serial.println("DHCP failed, reset Arduino to try again");
  Serial.println();
}
else
{
  Serial.println("Arduino connected to network using DHCP.");
  Serial.print("IP address is ");
  Serial.println(Ethernet.localIP());
  Serial.println();
}

delay(1000);
}

Setup of MySQL; Databank; “energie”, table; kWh_meter. This database is running on a pc with IP address 192.168.1.75.
In the table are 5 columns; “ID”, TimeStamp, “pulseTime”, “mWh”, “Watt”.

on the website root folder are 3 additional files;

  • add_data.php
  • dbconnect.php
  • review_data.php

Below the codes from these files

add_data.php;

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

    // Prepare the SQL statement
    $query =  "INSERT INTO energie.kWh_meter (pulseTime, mWh, Watt) 
	VALUES ('$_POST[pulseTime]', '$_POST[mWh]', '$_POST[curWatts]')";    

    // Go to the review_data.php (optional)
    header("Location: review_data.php");
	
	if(!@mysql_query($query))
	{
		echo "&Answer; SQL Error - ".mysql_error();
		return;
	
	mysql_close();
	}
?>

dbconnect.php;

<?php
$Username = "root";  // enter your username for mysql
$Password = "password";  // enter your password for mysql
$Hostname = "localhost";      // this is usually "localhost" unless your database resides on a different server
$Database = "energie"; //database name

$dbh = mysql_connect($Hostname , $Username, $Password) or die (mysql_error());;
@mysql_select_db($Database) or die (mysql_error());
?>

review_data.php (to view the raw data on a website);

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


$selected = mysql_select_db($Database, $dbh);
?>

<html>
<head>
    <title>Arduino kWh log ruwe data</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 kWh log ruwe data</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">pulseTime</td>
            <td class="table_titles">mili-Wh</td>
            <td class="table_titles">Watt</td>
          </tr>
<?php
    // Retrieve all records and display them
    $result = mysql_query("SELECT * FROM kWh_meter 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["Date-Time"].'</td>';
        echo '   <td'.$css_class.'>'.$row["pulseTime"].'</td>';
        echo '   <td'.$css_class.'>'.$row["mWh"].'</td>';
		echo '   <td'.$css_class.'>'.$row["Watt"].'</td>';
        echo '</tr>';
    }
?>
    </table>
    </body>
</html>

I hope more people can use the info above.

Excellent! Glad to hear you got it working.

What kind of sensor are you using? Is it accurate and consistent? My attempts to use optical methods to sense the position of mechanical dials have never been very successful.

It's always nice to see a 'full' solution, complete with supporting code in other languages.

Have you thought about doing a write up in the Exhibitions section?

rw950431: Excellent! Glad to hear you got it working.

What kind of sensor are you using? Is it accurate and consistent? My attempts to use optical methods to sense the position of mechanical dials have never been very successful.

You can find the sensor I used on the following site (with datasheet) http://hackerstore.nl/Artikel/140. I think the same sensor can be found in other shops. The advantage of this sensor is that it blocks out visible light (according to the description). This takes care of "false reading" when opening the meter-room.

In the arduino sketch you can find a line that is commented out that starts with "SensorToSerial()". If you remove the comment, you get the raw sensor data on the serial monitor (10x per second). With this raw data, the threshold value can be found. The difference between low and high value is significant (low = ~ 500, High = ~ 700 on analogue value). On visual checking, the threshold is 100% accurate. There is a led on the arduino that signals when threshold value is found.

dannable: It's always nice to see a 'full' solution, complete with supporting code in other languages.

Have you thought about doing a write up in the Exhibitions section?

I'm not sure what the Exhibitions section is and how to get a write up in there. Can you explain?

Thanks for the info about the sensor. I've got some of those TCRT5000 sensors (commonly used for end-stop detection in DVD drives and the such like) but have never had much success. I guess your meter has a nice silver dial with a black dot on it? I was attempting to detect the position of a little red pointer on a white dial but didn't succeed- maybe the field of view was too large?

Now that you have your logging solved you can check the accuracy of your counter by manually reading the dials on your electricity meter every few days then comparing this to your count

Indeed i have a silver dail with a big black bar, so a lot of contrast. Maybee you can try to use a led of opposite color (green-blue) or a color filter on the light sensor so you also get a high contrast.

I'm now playing with highcharts to visualize the logged data. unfortunatly no luck yet to use the logged MySQL data to visualize. If someone can get me going on this topic, I would be very happy :) .

With a realy high or low usage, the sensor did give some errors; Negative value's when the pulse-time was longer than 30 sec. The solution for this is to calculate the millis() and prevMillis as "unsigned long" instead of "int".

For the realy high usage; a few "if" functions with different "delay" functions; if(pulseTime > 6 {delay(3000);} , same for 2 sec delay 1000 and 1 sec, delay 300. This gives pretty good values. Of course i'm also going to check the accuracy with these settings.

So a lot of fun work ahead.

Hallo,

If you would like some idea on using HighCharts, then head down to my signature below and then follow through to the section where I talk about my project 'HydroSolar'.

You will see a button that will then load up a full javascript SCADA GUI on your system.

As I mention in my text, it is still very much a work in progress, so only a number of the screens are functional. But if you then select from 'History Charts | Hydro Turbine' you will get up a highchart of the data. I do have a small problem from time to time with my system with stored data, but is fixed in a new upgrade I have yet to install on the live system. Presently the time scroll is disabled until the update is installed in some weeks to come.

What you can then do is use your web browser development tools to take a look into the javascript code I developed to do these things.

One difference to note: I initially used a method like you are doing to send data from Arduino to host site using PHP and then store data into MySQL database.

But for my needs I needed a system that could be as close to real time as possible and therefore not be dependant on a host site or PHP or MySQL, as it is the wrong approach for the direction I am taking.

I still have a lot of my original code that may well suit your needs.

groeten, Paul

Hi Paul,

Thanks a lot for your reply. I think the dashboard that you have on your site is brilliant! Thats looks a lot like what I want to achieve.

You write that i can use development tools to see how your webpage is coded, but unfortunatly your website is currently down (maybee I looked to much around and your webserver is overloaded :-s ).

Can you post your code on this forum? Also nice for other people if they want to use this knowledge.

Thanks a lot again!

With kind regards, Danny

p.s. are you Dutch?

Hi Danny,

Yes, my server was down, due to the small issue I currently have with the way it gets historical data from the history files. I won't fix it at this point in time as the current development project is close to being installed to update the current live system. There are many many changes to the whole code base.

The web app you see was also working when I was pushing data from the Arduino to a host site where I used PHP and MySQL to handle the data and to store it.

Like I mentioned, my current system doesn't use a traditional LAMP stack any longer due to the direction I want this project to go. It is a purpose built application that talks to the Arduino as well as being a clever HTTP server with a specialised API for web apps.

But getting back to what you need, I want to confirm with you, it seems you have your own local server at home there that has Apaché and PHP and MySQL on it, is that correct?

I looked through my previous project files and do have code for both Arduino that uses HTTP POST to send to a HTTP server with PHP and MySQL . I have the PHP code, also for an HTTP web client to then get that data from MySQL or file.

The code I have does both live data as well as historical data. Live data gets saved to standard text file on the HTTP server for improved speed and historical data gets saved to SQL database.

I'm thinking how best to manage this, as the code for all parts are quite lengthy, especially the javascript code which is a full web app and includes many files. I'm thinking I will shorten it down to something smaller and more manageable for people to use and have it on GitHub.

The project I developed was for an industrial monitoring project and was run on a Arduino Mega with on-board Ethernet which has the necessary memory resources.

Am I Dutch, Ja, I live in Australië though, I guess with a name like you see, I could only be Dutch :)

EDIT: I just noticed your code uses a page refresh every 5 seconds. What I have doesn't use this method at all, rather, it uses AJAX methods to get new data at specified intervals. This is all done in the web app, which is what is called a single page web app, in that, all necessary HTML pages are loaded in one go at start-up together with a menu system. The web app uses Backbone.js as the main framework to keep the code in a manageable form. The web app uses a number of libraries, backbone.js, underscore.js, HighCharts.js, Bootstrap.js, jquery.js, steelseries.js. They all help :)


Paul

I am building a project that has need to display my data to a website. My studies indicated that what I am searching for would best be made with PHP. Being so new to Arduino and learning how to use the coding language in the IDE (C & C++?), I was nervous about being side tracked from these studies by also undertaking new studies of new languages. So I found a webpage building site (hostGator) that uses WordPress to build PHP websites with point and click design tools, like that of MS Word. The WP sites are purported to use MySQL.

My studies on this subject usually leads me to dead ends, or the leads are just too incomplete for my unlearned brain to carry into fruition. In the opinion(s) of those subbed to this thread, would you say that it is possible to direct the Arduino data to be displayed onto a WordPress website? Or am I just chasing after a fantasy?

My project is an EtherMega with an RTC clock, a climate sensor and an 8 channel relay to switch 8 duplex AC receptacles. The only data that needs to be sent is the RTC readings, 2 floats for the sensor, and pin states of pins 30-37.

I do not want to intentionally redirect this thread, but I do not yet know enough about PHP to know if WP can be used or not for my project.

TYIA

Hi Michael,

I've been following your posts where and when I can to track your progress with your project. I have also been wanting to find a niche in my time where I could assist more with what you are wanting.

With the above thread, I proceeded to restructure one of my projects to make it more generic for people with other ideas to be able to use. It's a fair amount of work, but I almost did complete the restructure the other week. It will be up on GitHub as soon as I get another monitoring project installed and commissioned. Lots of CO2, Temp and RH sensors for a couple of cool stores storing potato seed.

I can suggest you use a simple hosted site where you can control all parts. It should have basic PHP, MySQL support. With a simple hosted site, you create to style and content you want. With this, you can get as fancy as you please.

I would hope that in one or two weeks I will have it complete for re-testing and using I guess. So much to do :)


Paul

@myggle An alternative approach is to use thingspeak for your data storage and embed a graph into your wordpress site. Theres even a tutorial on how to do it. (Although see the comment about wordpress.com sites not allowing iframe)

@rw950431 Thanks for the lead and link. I will digest what I can, and even if WP gives me problems, I’ll still learn more about what I want and if push comes to shove, I might look for a new domain host, as it’s all about this project for me!

@ Paul, Thanks so much for the public and private help, and for taking interest. I just sewed up my project box as all internal circuitry is in order and further code adjustments can be tested with AC light bulbs on each of the outlets. As of now, it can be a stand alone unit until I can get a good network built and my data posted. I look forward to what you can share. Thanks again!

Just a heads up regarding PHP and MySQL. The "mysql" PHP functions are due to be deprecated in the newer versions of PHP. Replacement functions use "mysqli" as the function prefix. From memory there are some minor format changes between a few of the old and new functions although for most it would simply be a replacement of "mysql" with "mysqli". Reference should be made to the PHP web site http://www.php.net for more info.

The work around to embed iFrames is a WP plugin called Global Content Blocks, and I was able to embed the field1 chart from ThingSpeak to my site. My only problem now is finding a functional ThingSpeak tutorial to get my data to my channel. Got a link?

Hi Paul,

It has been a few weeks. Some other things had to be finished first. Now time has come to continue with this fun hobby project.

But getting back to what you need, I want to confirm with you, it seems you have your own local server at home there that has Apaché and PHP and MySQL on it, is that correct?

That's correct. There is a cubieboard running ubuntu that is hosting a MySQL server and Apache package. Than there is a arduino that is running the earlier described sketch.

The MySQL server is now collecting data a few weeks, and when I compare it to the official meter, it is very accurate. Now i'm copying the raw data in Excel and doing some pivot graph stuff to see some nice effects.

About showing the data with HighCharts, AJAX and JSON functions (I'm even not sure what the difference between the 2 is); I have nearly no programming experience. I now suspect that this is a step to far for me. I will continue to google my way around to get the simple speedometer working.

Thanks alot again for your help this far.

When I have a working speedometer, I will post the code here.

With kind regards,

Danny

Slightly OT, but would it be possible to do this with some sort of non-invasive and non-reactive inductive tap to measure the current being used?

habanero:
Slightly OT, but would it be possible to do this with some sort of non-invasive and non-reactive inductive tap to measure the current being used?

Only one way to find out I think…