Photon to mySQL DB

Hello!

I’m working on a project with my makerspace to collect sensor data from a bee hive, and would like to insert that data into a mySQL database. (I’m specifically interested in this method vs other alternate means)

I have the photon successfully collecting data and pushing it to the particle event log, however i’m at a loss regarding the process of taking that data and writing it to a database.

What’s the in-between step look like? Does anyone have sample code that I could play around with?

I personally have experience building websites with PHP and MySQL and have access to those resources.

Any tips would be greatly appreciated.

Adam

1 Like

@rickkas7 has had a great sample project here

Which I successfully adapted for a bee hive monitoring project too.

1 Like

Thanks! I’ll give it a shot!

1 Like

Okay, so with this project I don’t have access to node.js so I’m having to try something else. After a lot of mucking around, I’ve focused on using TCP Client & a GET request through PHP to insert into the mySQL DB

I’m basing my code on the TCP Client documentation here: https://docs.particle.io/reference/firmware/photon/#tcpclient

And similar code from an Arduino project here:

Here’s my photon code:

TCPClient client;
char server[] = "www.example.net";

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

void loop() {
    
    Serial.println("connecting...");


  if (client.connect(server, 80)) {
    client.println("GET /write-data.php?value=10");
    client.println(" HTTP/1.1"); // Part of the GET request
    client.println("www.example.net"); 
    client.println("Connection: close");
    client.println(); // Empty line
    client.println(); // Empty line
    client.stop();    // Closing connection to server
    Serial.println("done..");

  }

  else {
    // If Arduino can't connect to the server (your computer or web page)
    Serial.println("connection failed");
  }
 
  delay(5000);
 
}

Here’s my PHP

<?php

// Prepare variables for database connection

$servername = "dbland.example.net";
$username = "user";
$password = "pass";
$dbname = "db";

// Create connection
$connect = new mysqli($servername, $username, $password, $dbname);



    // Prepare the SQL statement


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

    // Execute SQL statement

    mysqli_query($connect, $sql);

?>

the PHP seems to work perfectly fine when i just paste the url into the browser like so: the new data is inserted as a fresh row into the database with whatever value i enter. (it’s just two cols, one auto incrementing and one to hold the value.)

www.example.net/write-data.php?value=10

When I watch the Serial Monitor, I’m seeing it say

connecting...
done...
connecting...
done...

Never connection failed. So based on the code I wrote, it seems to be continuously reaching the server at least. Something about the GET isn’t working though? I don’t know. I feel like i’m so close.

Anyone have any thoughts?

1 Like

Rather than using a TCP client, on the photon, would it not be simpler to user a Particle.publish event that calls a Webhook that passes the data to your site where PHP can handle inserting it into your database?

1 Like

Would it be? I’ve not used one… Is there a good example to look at?

I’d like to completely own all steps of the process (if that makes sense), and TCP client seems like the least moving parts if I can understand how to make GET work.

1 Like

Your GET request isn’t correctly formed.

You’ve added an extra linefeed, omitted the 'Host: ’ prefix, added an extra empty line at the end and omitted the content-length header.

You might be better off trying the HTTPClient library as it will handle the response for you, or at least take a look at that code to see how it waits and parses the response.

3 Likes

That helped a ton.

I’ve gone ahead and included the HTTPClient library and now my test is successfully inserting rows into the database.

Here’s the working code so far. My new confusion is how do i put my sensor variables into the actual GET request?

// This #include statement was automatically added by the Particle IDE.
#include <HttpClient.h>

sensor example values, which i’m not sure how to include in the request.path or the request.body

int lightsensor = 10;
int tempsensor = 5;

HttpClient http;

// Headers currently need to be set at init, useful for API keys etc.
http_header_t headers[] = {
    //  { "Content-Type", "application/json" },
    //  { "Accept" , "application/json" },
    { "Accept" , "*/*"},
    { NULL, NULL } // NOTE: Always terminate headers will NULL
};

http_request_t request;
http_response_t response;


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

void loop() {
    
Serial.println();
    Serial.println("Application>\tStart of Loop.");
    // Request path and body can be set at runtime or at setup.
    request.hostname = "www.example.com";
    request.port = 80;
    request.path = "/write-datas.php";

    // The library also supports sending a body with your request:
    //request.body = "{\"key\":\"value\"}";
    


    // Get request
    http.get(request, response, headers);
    Serial.print("Application>\tResponse status: ");
    Serial.println(response.status);

    Serial.print("Application>\tHTTP Response Body: ");
    Serial.println(response.body);
  
  delay(5000);

}

After a whole ton of struggling I got the test to work. Hopefully this helps people in the future. Here’s the functioning code:

#include <HttpClient.h>

int lightsensor = 12345;

HttpClient http;

// Headers currently need to be set at init, useful for API keys etc.
http_header_t headers[] = {
    //  { "Content-Type", "application/json" },
    //  { "Accept" , "application/json" },
    { "Accept" , "*/*"},
    { NULL, NULL } // NOTE: Always terminate headers will NULL
};

http_request_t request;
http_response_t response;


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

void loop() {
    
Serial.println();
    Serial.println("Application>\tStart of Loop.");
    // Request path and body can be set at runtime or at setup.
    request.hostname = "www.example.com";
    request.port = 80;

// the path including variables for GET
char thedata[64];
char *thepath = "/write-datas.php";
char *varone = "?value=";
sprintf(thedata, "%s%s%d", thepath, varone, lightsensor);

request.path = thedata;



    // Get request
    http.get(request, response, headers);
    Serial.print("Application>\tResponse status: ");
    Serial.println(response.status);

    Serial.print("Application>\tHTTP Response Body: ");
    Serial.println(response.body);
  
  delay(5000);

}
3 Likes

Thank all for share code! I have the same problem. I really like when anyone solve similar problems that how occur me.

My code php multiple data:

<?php

$con = mysqli_connect(‘ipsql’,‘root’,‘123456’);
if(!$con)
{
echo ‘No se establecio conexión con el servidor\r’;
}
if (!mysqli_select_db ($con,‘sismo1’))
{
echo ‘Error al conectar con base de datos\n\r’;
}
// URL IS LIKE THIS http://localhost:8080/send.php?id=1&x=2&y=3&z=8
$SismoID = $_GET[“id”];
$acelx = $_GET[‘x’] ;
$acely = $_GET[‘y’] ;
$acelz = $_GET[‘z’] ;
echo $SismoID;
echo $acelx;
echo $acely;
echo $acelz;
$sql = “INSERT INTO database (SismoID, acelx, acely, acelz) VALUES ($SismoID, $acelx, $acely, $acelz)”;
if (!mysqli_query($con,$sql))
{
echo ‘Error al insertar! Quizás sea tabla errónea!’;//Spanish comment
}
else
{
echo ‘Insertación perfecta +1!’;//Spanish comment
}
?>

More of my project here:

https://github.com/ZurMaD/SistemaDeAlertaSismica/tree/master/php/photon

This may be a dumb question but – is there not a way for the Photon to connect directly to a MySQL database without having to use an intermediary service?

Hi @rac_atx

Of course it is possible to connect a Photon directly to a host running mySQL! The hard part comes when trying to do that securely. Currently there are several HTTPS (secure HTTP) clients for Photon but they take a lot of on-chip resources in order to run and many people have found that using a web hook or other intermediary is an easier path.

If your mySQL host is on your local network and no security is needed, then is it pretty simple as shown in Atomic’s post above.