As I promised yesterday, here is the snipet how I do the access to a remote database.
First, in the sketch, use the following function to do a curl call:
//This function executes a curl command. You can use it to make a rest request.
String doCurl(String COM) {
Process p;
p.runShellCommand(COM);
while (p.running());//Wait for the process to finish
String result;
if (p.exitValue() == 0) {
while (p.available() > 0) {
char c = p.read();
result += c;
}
}
else {
resultado = F("ErrorCurl:");
result += p.exitValue();
}
return result;
}
For example, you want to send to your server a temperature:
float temp=12.2;
//The curl command is curl --data "db=m&t=i_t&te=12.2" http://my_web_server/my_script.php
//We must escape the "
//db=m this is for switching between a mysql database and a sqlite database
//t=i_t this means "task=insert temperature"
//te=12.2 this is the temperature we want to insert
String s = F("curl --data \"db=m&t=i_t&te=");
s += tem_act;
s +="\" http://my_web_Server/my_script.php";
String result=doCurl(command);
Finally, the php script that is on your server:
<?php
try {
if (isset($_POST["t"]) && $_POST["t"] != "") {
#t is the task we want to do
$task = $_POST["t"];
#bd is the database (m for mysql, s for sqlite)
if (isset($_POST["db"]) && $_POST["db"] != "") {
$db_type = $_POST["db"];
if ($type_db === "m") {
$link = new \PDO('mysql:host=localhost;dbname=db_name', 'db_user', 'password', array(
\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
\PDO::ATTR_PERSISTENT => false,
\PDO::MYSQL_ATTR_INIT_COMMAND => 'set names utf8mb4'
));
} else {
$link = new \PDO('sqlite:/mnt/sd/sqlite_file_in_the_yun_sd');
}
#Task i_t= insert temperature in the temperature table
if ($task === "i_t") {
$temp = $_POST["te"];
$query = "INSERT INTO temperatures (temperature)VALUES (?)";
$handle = $link->prepare($query);
$handle->bindValue(1, $temp);
$handle->execute();
echo 'OK';
}
$link = null;
} else {
echo "DB type missing";
}
} else {
echo "Task missing";
}
exit;
} catch (\PDOException $ex) {
echo $ex->getMessage();
}
?>
The php script works for a sqlite database or for a mysql database. Of course the structure of the tables have to be equivalent if you want to reuse your different querys.
Yo can add more tasks or antoher taks, such as deleting, retrieving data form a table, etc. But be carefull, you can not handle in the arduino yun a resultset in Json format with 100 rows. I work only with small results (words like OK or data like strings with ten characters and so on).
There are other ways, like don't using a sketch and do all the work in a php script on the linino side, and run the php script periodically with a cron job, like discussed here: Program in the sketch side vs program in the linino side... - #17 by roquec - Arduino Yún - Arduino Forum
Of course if you use the php script on the linino to work with a sqlite database, you need to install php and sqlite.
Hope this helps you in some way.
Best regards
R. Campos.