Need help with storing event variable in a database connected to website

General
1

Hello everyone,
I am working on a university project at the moment where we have an exhibition of an interactive art installation that will be placed in my local city for 3 days. On our website about the art installation we want to implement a counter that simply counts the amounts of interactions that people have done with our installation in these 3 days. The signal that an interaction has taken place would come from the Arduino which is connected to the Photon.

I have done a prototype of this where I used a simple pushbutton that substitutes the signal coming from the Arduino.

The html/js code for this:


<script type="text/javascript">     
function start() {

  // document.getElementById("flower").innerHTML = "Waiting for data...";
    var deviceID = "CENSORED";
    var accessToken = "CENSORED";
    var eventSource = new EventSource("https://api.spark.io/v1/devices/" + deviceID + "/events/?access_token=" + accessToken);

    eventSource.addEventListener('open', function(e) {
        console.log("Opened!"); },false);

    eventSource.addEventListener('error', function(e) {
        console.log("Errored!"); },false);

    eventSource.addEventListener('+1', function(e) {
        
        //console.log("received '" + e-data + "'" );
        var parsedData = JSON.parse(e.data);
        var flowerSpan = document.getElementById("flower");
        var tsSpan   = document.getElementById("tstamp");
        localStorage.flowercount = Number(localStorage.flowercount)+1;
        //tempSpan.innerHTML = "Core: " + parsedData.coreid + " flower: " + parsedData.data + " (h:m:s)";
        //tempSpan.innerHTML = "flower";
        flowerSpan.innerHTML = "There have been " + localStorage.flowercount + " interactions with the flower.";
        flowerSpan.style.fontSize = "28px";
        tsSpan.innerHTML = "At timestamp " + parsedData.published_at;
        tsSpan.style.fontSize = "9px";
    }, false);
}
`

This works, the problem is that it stores the counter variable only locally in the browser.
What I actually need is storing the counter number somewhere on a web server and then display the number on my project website, where it gets updated regulary.

I’ve already researched a lot and tried to work with a MySQL database, unfortunately I’m lacking the experience and skills in these fields.
I’ve also followed this tutorial, as it looks like something that I need. Unfortunately it gives me a hook error in the console.particle event log. I can’t seem to find where the error is so I am looking for a different(possibly easier) solution for my problem.

At the moment I am quite desperate and any help or tips are appreciated!

2

Just a thought, why not store the number on the Photon and publish and expose as Particle.variable()?

3

Thanks for your reply. I thought about that but figured that the number would probably be erased in case the Photon turns off or loses connection to the WiFi. Do you think there is a way to keep the number stored even if the Photon turns off or loses connection?

4

If you use retained variables the data won’t be lost after a reset (and mere cloud loss doesn’t erase any variables).
Retained variables are stored in a special RAM area that’ll be held valid as long the device is powered, and to gain independency from the grid power you can add a coin cell to provide backup power to VBAT.
And if that seems too insecure, you could even use EEPROM.

5

Thank you, this is a great idea. I just talked to my teachers again and unfortunately I HAVE to use a database in this project… So I still need to do it in the complicated database way

6

Does firebase database qualify?

7

I gave it a look and I think it would qualify as a database, yes.

8

This tutorial should be handy:

9

Ubidots can also be used to database the data sent out from a Photon or Electron.

It’s probably easier to setup than the Firebase solution also.

Just another option.

10

Call a php script that adds the data to the mysql database. If you have a mysql database with phpmyadmin you just create a table with a column autoID and another field (string) for data or whatever. Than call a simple phpscript that inserts some data into the table. Script in php would look something like this:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "INSERT INTO MyTable (youdatacolumn)
VALUES ('".$_POST['yourdata']."')";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

Taken from w3schools

Assuming you got jquery in your code you call the script with:

$.ajax({
    data: 'yourdata=' + yourdata,
    url: 'your.php',
    method: 'POST',
    success: function(msg) {
        alert(msg);
    }
});

You can edit according to your needs, like no alert on succes.

1 Like