Send Yun sensor data to MySQL on external server

So I've got my Yun and now i'm trying to send sensor data to a MySQL database on an external server.

I have the database set up but as somewhat of a novice I need help with how I can insert the data to a MySQL database preferably using PHP. Whether to have the processing done by a script on the linino or server side I'm open to advice.

I know how to send sensor information to serial and to a local webpage (web server example) and I can submit to the database from a html and php form but need help on this next step.

Thanks

Start writing a PHP script that you can invoke from the command line and that can connect and insert data into your mysql database. This is a pure PHP question, so this forum is not the best place for finding answers.
Once you have it, get back here if you need support in using Process for calling your script from the yun

There are three ways to connect php with mysql

http://forum.arduino.cc/index.php?topic=206746.msg1524891#msg1524891

Running php script from linino terminal

http://forum.arduino.cc/index.php?topic=229077.msg1655208#msg1655208

Php communication with mysql: 3 ways

  1. MySQL shared module
  2. MySQL Improved Extension
  3. PHP Data Objects plus PDO driver for MySQL

Arduino communication with php: 2 ways

  1. HttpClient->uhttpd->php-cgi
  2. Process->php-cli

The total method send Yun sensor data to mySql on external server via php is 6 ways.

Sample code (Process->php-cli+MySQL Improved Extension)

At Mysql console:

mysql> CREATE DATABASE sensors;
mysql> USE sensors;
mysql> CREATE TABLE sensor_data (
    -> id INT NOT NULL AUTO_INCREMENT,
    -> temperature INT,
    -> insert_date TIMESTAMP,
    -> PRIMARY KEY (id)
    -> );
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;
mysql> INSERT INTO sensor_data (temperature) VALUES(20);
mysql> select * from sensor_data;
opkg update
opkg install php5-mod-mysqli
opkg install php5-cli
nano /mnt/sda1/db.php
#!/usr/bin/php-cli
<?php
$temperature = $argv[1]; 
$DBServer = '192.168.0.20'; 
$DBUser   = 'root';
$DBPass   = 'password';
$DBName   = 'sensors';  
$conn = new mysqli($DBServer, $DBUser, $DBPass, $DBName);
// check connection
if ($conn->connect_error) {
  trigger_error('Database connection failed: '  . $conn->connect_error, E_USER_ERROR);
}
$sql="INSERT INTO sensor_data (temperature) VALUES ($temperature)";
if($conn->query($sql) === false) {
  trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);
} 
?>
chmod 755 /mnt/sda1/db.php

Send test insert data:

/mnt/sda1/db.php  80

Arduino code:

#include <Process.h>
int temperature;
void setup() {
  Bridge.begin();  // Initialize Bridge
}
void loop() {
  int temperature = random(0, 100);
  Process p;              
  p.begin("/mnt/sda1/db.php");      
  p.addParameter(String(temperature)); 
  p.run();
  delay(5000); 
}

Sonnyyu you are an absolute life saver, thank you very much.

I will have a fiddle and see if I can get it working.

Thanks again,

Charlie

Are the first 3 blocks of code just for setting up the database and installing PHP on the Linux or do they form part of some code I have to use constantly?

Is the. 4th section the file called db.php?

Sorry for being such a n00b I'm just trying to work out where to begin with this.

mortonc: Are the first 3 blocks of code just for setting up the database and installing PHP on the Linux or do they form part of some code I have to use constantly?

Is the. 4th section the file called db.php? ...

Yes.

Hi sonnyyu
Thanks for your help and contribution of your knowledge with us.
I tried the tut but i get no values into the db. I was not able to send some testdata (/mnt/sda1/db.php 80)
What make me suspitios is if i check the SD card there is the db.php not in the www-folder. Should it not be into the www folder to process php?
Sorry for boudering you with my issues but the data transfair to a external mysql db is essential to my project and examples to do such tasks are extremely valuable.

best regards

What make me suspitios is if i check the SD card there is the db.php not in the www-folder. Should it not be into the www folder to process php?

Doesn't matter.

Testing method:

Step 1;-

opkg update 
opkg install mysql-server  //only use client portion

Access MySQL Server From The Shell Prompt (Command Line)

http://www.cyberciti.biz/faq/how-do-i-access-mysql-server-from-the-shell-prompt-command-line/

Use Mysql Command Line to test server and network.

Step 2;- Run

/mnt/sda1/db.php  80

at Shell Prompt, and use step 1 to confirm data in the DB.

Step 3;-

Put code into Arduino test again.

The list of programming language could use;-

sonnyyu:

sonnyyu:
The list of programming language Yun support:

  • Erlang (a general-purpose concurrent, garbage-collected programming language and runtime system)
  • Java
  • JavaScript
  • Lua
  • PHP
  • Perl
  • Python
  • Ruby
  • 4th (an imperative stack-based computer programming language and programming environment)
  • eggdrop (Eggdrop is a popular IRC bot and the oldest still in active development)
  • sigscheme (SigScheme is a R5RS Scheme interpreter for embedded use.)
  • slsh (Interpreter for S-Lang scripts)
  • tcl (Tool Command Language)
  • vala (C-sharp like language for the GObje)

Last one might be most important one is C, by using cross compile.

MIPS Assembly Language is also technically correct answer.
Since Linino is Linux based, all shell scripts are.

lua-mysql:

http://forum.arduino.cc/index.php?topic=206746.msg1524868#msg1524868

python-mysql:

http://forum.arduino.cc/index.php?topic=206746.msg1524874#msg1524874

ash-mysql:

http://forum.arduino.cc/index.php?topic=206746.msg1524879#msg1524879

sonnyyu,

Thanks for the code, it saved me a lot of time.

I was able to write to the database from the Yun via Putty by invoking “php-cli /mnt/sda1/db.php 856”
root@Arduino:~# php-cli /mnt/sda1/db.php 856
but wasn’t able to get the Yun script to do it itself without a few code changes:

added
#include <Bridge.h>
and changed the p.begin and p.addParameter strings to mimic what worked at the command prompt.

#include <Process.h>
#include <Bridge.h>
int temperature;
void setup() {
  Bridge.begin();  // Initialize Bridge
}
void loop() {
  int temperature = random(0, 100);
  Process p;              
  p.begin("php-cli");      
  p.addParameter("/mnt/sda1/db.php");
  p.addParameter(String(temperature));
  p.run();
  delay(5000);
}

I don’t know if all the changes were needed, but it works for me.
Other than that, I followed your directions verbatim.
Thanks again!

nano /mnt/sda1/db.php

#!/usr/bin/php-cli
<?php

...

chmod 755 /mnt/sda1/db.php

will make db.php run without php-cli .

#include <Process.h>
#include <Bridge.h>

<Bridge.h> is duplicate, the code should work without it.

nice topic, this data can be easily sniffed, how can we use a secure connection?

nano package/feeds/packages/mysql/Makefile
...
--without-vio \
--without-ssl \
--without-docs \
--without-man \
...

The mysql compile without ssl support, There is no secure connection for mysql at Yun

Plan B: workaround, set VPN between Yun and Mysql Server.

Help!! Yun can't connect to mysql. How can i solve it?

mysql -u temp -h localhost -p

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld.sock' (146)

http://stackoverflow.com/questions/14792200/mysql-error-2002-hy000-cant-connect-to-local-mysql-server-through-socket-v

OP has solved this question himself by removing an existing cygwin install and mysql install and then subsequently reinstalling the mysql and cygwin again.

Reinstall WAMP server!