Hi, I was trying to move an Arduino project to the Spark where I was using this library: http://drcharlesbell.blogspot.ca/2013/04/introducing-mysql-connectorarduino_6.html. I tried the best as I can to implement the library without any success. What would be the best way to get a lot (more than 20) variables from a MySQL to the Spark ?
Thanks
William Simetin-Grenon
Ooooh good find, i would be interested in this too…
Im currently using a php page to pull strings from mySQL and send them to my core. i publish an event from the core, this triggers the php webpage, it grabs the data from the database and formats it for the core, it then uses curl to send to a function on the core.
How timely. I am working on porting an arduino project to spark and am stuck at exactly this point.
What I am trying to do is: Say I have a fleet of cores that all need a unique api key for a certain cloud service. Maybe some other settings too like sensor thresholds that would be unique to each device.
On uno and yun with python I use a get to a php page. It retrieves the mysql data and spits it back in json. I parse it into the variables I need.
I like the idea of publishing an event that would tell my php page to get the data and send it to me. Seems easier and already built in. I’ve only had the spark about a week so I’m not sure exactly how that works yet or if it even works like that…
I’ve done something like this quite recently. I think it’s the most straightforward to just directly call the php page. You can easily have the headers for the http request include extra data such as an api key or something like that. I have the php page output to json, which is easily translatable. I used the OpenWeatherMap library as a starting point, which in turn uses the httpclient lib and the jsonparser lib. The relevant code in the OWM library is:
request.hostname = "api.openweathermap.org";
request.port = 80;
request.path = "/data/2.5/forecast/daily?q=" //
+ location // e.g. "Berlin,de"
+ "&units=" + unitsForTemperature // metric or imperial
+ "&cnt=1" // number of days
+ "&mode=json" // xml or json
+ "&APPID=" + apiKey; // see http://openweathermap.org/appid
request.body = "";
http_response_t http_response;
this->client->get(request, http_response);
if (http_response.status == 200) {
return parse(http_response.body, response);
} else {
Serial.print("weather request failed ");
return false;
}
Hi guys ! Thanks for your answers. Sorry for being so long to answer. As I can see there is no other way to do communicate with a MySQL database that a PHP page. Is there someone who can point me in the right direction to get variable from de PHP page ? A tutorial or something ?
Thanks !
Are you asking how to get output from the php/http request into the core, or how to interface php to a mysql database? If it’s the latter, this or this might be decent places to look.
I know how to get variables from MySQL database to a PHP page, what I don’t know is how to take them from the PHP page and use them in the Spark.
Here is an example bit of code i very quickly whipped up, the query part is missing some bits as its a cut and paste from another PHP script, but you will get the idea. you will see right at the end there is ‘addmessage’ in the send curl function call, thats the name of the function on the core (it sends a new comment to the spark message torch so have a look at the functions in that to see the core side of things). the PHP script has a security check to make sure the right thing is calling it, and not just a robot, i trigger it with a webhook from spark at the moment, and the event name that triggers the webhook gets checked… but you could cut all that out if its not suitable.
<?php
$spark_id = 'type core ID'; //torch core
$spark_access_token = 'type access token here';
function sendCurl($spark_id, $spark_access_token, $type, $postfields) {
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, 'https://api.spark.io/v1/devices/' . $spark_id . '/' . $type);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 15);
curl_setopt($ch, CURLOPT_TIMEOUT, 15);
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, 'access_token=' . $spark_access_token . '&args=' . $postfields);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
$result = curl_exec($ch);
$answer = json_decode($result, true);
if ($answer['return_value']==1) {
return;
}
else {
$file = 'connect.txt';
file_put_contents($file, 'Curl Failed' , FILE_APPEND);
die();
}
}//end of sendCurl function
if (isset($_POST['event']) && isset($_POST['coreid'])) {
//split the event into spark user id and event name
list($sparkuid, $event) = explode( '/', ($_POST['event']), 2);
if (!ctype_xdigit($sparkuid)) {
$file = 'connect.txt';
file_put_contents($file, 'sparkuid not hex' , FILE_APPEND);
die(); } //check spark user id is hex characters
if ($event != 'messagefinished') {
$file = 'connect.txt';
file_put_contents($file, 'wrong event' , FILE_APPEND);
die(); }
$coreid = $_POST['coreid'];
if (!ctype_xdigit($coreid)) {
$file = 'connect.txt';
file_put_contents($file, 'wrong core ID' , FILE_APPEND);
die(); } //check spark core id is hex characters
//$coreid = cmtx_sanitize($coreid, true, true);
if ($coreid != $spark_id) {
$file = 'connect.txt';
file_put_contents($file, 'wrong core' , FILE_APPEND);
die(); } //must be the right core
} else { $file = 'connect.txt';
file_put_contents($file, 'Post fields not set' , FILE_APPEND);
die(); } //must be set
$comments = mysqli_query($this->getLink(), "SELECT * FROM `" . $this->getPrefix() . "comments` WHERE `is_approved` = '1' AND `displayed` = '0' ORDER BY `dated` ASC LIMIT 1");
$comment = mysqli_fetch_array($comments);
$message = $comment['comment'];
sendCurl($spark_id, $spark_access_token, 'addmessage' , (urlencode($message)));
?>
Just wanted to pop in and say this is a great thread! I’ve been thinking about building some pieces that would go the other way (core -> database), but this has got me thinking… 
Thanks,
David
@Dave, I do that too hence the reason the i perform a few checks to where the request comes from and the data it contains. (i actually took some of the sanitising and checks out when i posted the code here because the functions are buried deep in other code and wont make any sense)
Im using commentics on a webpage to keep a database of messages for the message torch. I added a column called displayed that keeps a record of how many times each message has been played, then there is a table that keeps the requests and IP, failed requests etc. when there is a good response from the core then it updates the database. failed attempts go into a list that bans that IP. most of the functions i am re-using are already in the commentics library.
here is an example of the database getting updated when a message has been displayed, mySQL is pretty straight forward…
mysqli_query($this->getLink(), "UPDATE `" . $this->getPrefix() . "comments` SET `displayed` = `displayed` + 1 WHERE `id` = '$id'");
Hi, I don’t really understand how the POST [] is used. You don’t post anything with this page you get them from a DB. And can you tell me a bit more about the Spark part of the code ?
Thanks
Sorry its kind of difficult to explain,
my current setup:
spark publish -> webhook -> PHP -> mySQL -> curl -> spark function
the spark publish triggers the webhook, the event name is message finished.the code on the core is
Spark.publish("messagefinished", NULL, 60, PRIVATE);
The webhook does a POST to my server containing a few things, like spark user ID and core ID and the event name, there is also some other stuff like time etc… exactly the same thing could be done with a TCPclient quite easily, and you could make the Post data match whats required by the PHP script.
the script then goes to my database and retrieves the latest message from the database, thats where the mysqli_query line comes into it, im not up to speed on how the database object and stuff works, its like magic stuff in the commentics library i use. the query returns a whole row of data into an array, its the oldest undisplayed message (as per the query) the PHP script then takes just the comment part of the array, which contains a message to display on the message torch.
the message is sent to the spark core using curl to call a function, this is the function on the core (in the setup() section)
Spark.function("addmessage", addMessage);
and after the main loop there is a function to deal with the comment sent to the core
int addMessage(String aText){
...
}
There is probably much simpler ways to do this, i just modified what was already written to get this going in the shortest time possible.
If i did it again i would do it with a TCPclient, do a post request to my PHP script with the query, and the result would be returned in the body. that way the PHP script does all the heavy work. same could be done for saving data, just include it in the POST, and you can use the key:value pair in the PHP script. Just make sure you think about security and SQL injections, i see attacks on my server on a daily basis. make sure you dont store anything without sanitizing it first, if anything thing looks sus its best to drop it and alert you, otherwise you could loose your whole database