How to download data from mysql database to Arduino Yun

Hardware Arduino Yún
1

I am using Arduino Yun for my project. I need to download data from web host, 000webhost, mysql database to my Arduino Yun. How can I achieve that? I want to use php file, which should be working independently. How should I on the Arduino Yun side.

Thanks!

2

Here is my PHP file:

<?php $db_name = "00000"; $mysql_user = "0000"; $mysql_pass = "0000"; $server_name = "mysql9.000webhost.com"; $con = mysqli_connect($server_name,$mysql_user,$mysql_pass,$db_name); $dname = $_GET["dname"]; $dlastname = $_GET["dlastname"]; $pname = $_GET["pname"]; $plastname = $_GET["plastname"]; $statement = mysqli_prepare($con, "SELECT * FROM drug WHERE dname = ? AND dlastname = ? AND pname = ? AND plastname = ?"); mysqli_stmt_bind_param($statement, "ssss", $dname, $dlastname, $pname, $plastname); mysqli_stmt_execute($statement); mysqli_stmt_store_result($statement); mysqli_stmt_bind_result($statement, $userID, $dname, $dlastname, $pname, $plastname, $drug, $time, $minute, $amount, $op); $response = array(); $response["success"] = false; while(mysqli_stmt_fetch($statement)){ $response["success"] = true; $response["userID"] = $userID; $response["dname"] = $dname; $response["dlastname"] = $dlastname; $response["pname"] = $pname; $response["plastname"] = $plastname; $response["drug"] = $drug; $response["time"] = $time; $response["minute"] = $minute; $response["amount"] = $amount; $response["op"] = $op; } //echo"Drug: ".$drug; //echo"Hour: ".$time; //echo"Minute: ".$minute; //echo"Op: ".$op; ?>

Basically just get the input from Arduino: dname, dlastname, pname, and plastname. And then search the whole table. If these four columns match, just download the whole information of that row from Mysql database.

Two question: 1. If there is no problem with php script, how should write the code in Arduino yun 2.How to download data from php file? Is there something like a return so that I can get all data in Arduino Yun?

3

This is something I have running, so maybe it will shed some light on a method for you:

This is the code in the arduino which retrieves a table (I generate updates to the table elsewhere) from mysql and populates an array with the information:

void dbcpreadstats() {
  String result; //creates a result string
  char c; //creats a char
  Process readstats; //
  readstats.begin("php-cli");
  readstats.addParameter("/mnt/sda1/db_cpreadstats.php");
  readstats.run();

  for (int d = 0; d < 15; d++) {
    result = "";
    c = 1;
    while (readstats.available() > 0 && c != '\n') {
      c = readstats.read();
      if (isDigit(c)) result += c;
    }
    avgarray[d] = result.toInt();
  }
}

This is the php script which simply selects a table and prints each line back to the arduino.

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

$DBServer = '127.0.0.1'; 
$DBUser   = 'root';
$DBPass   = 'xxxxxxxxxxxxxx';
$DBName   = 'yun';  
$conn = new mysqli($DBServer, $DBUser, $DBPass, $DBName);
// check connection
if ($conn->connect_error) {
  trigger_error('Database connection failed: '  . $conn->connect_error, E_USER_ERROR);
}
$query = "SELECT data FROM assy5cp_stats;";
$result = $conn->query($query);
if($result === false) {
  trigger_error('Wrong SQL: ' . $query . ' Error: ' . $conn->error, E_USER_ERROR);
}
$result->data_seek(0);

    while ($row = mysqli_fetch_row($result)) {
 printf ("%s \n", $row[0]);
    }
 

$result->free();
$conn->close();
?>

I'm have added these packages:
php5
php5-cli
php5-mod-mysqli

I happen to be running the mysql server locally on the arduino with mysql-server, but there is no reason you would have to.

You can pass variables to the php script by adding a parameter to the process and then assigning a variable name to each argument in the php script:

$var1 = $argv[1];  //runid
$var2 = $argv[2];  //stage
$var3 = $argv[3];  //thermostat
$var4 = $argv[4];  //continuity

Your query would then look like this:

INSERT INTO ovenshifter_data (runid, stage, thermostat, continuity) VALUES ($var1,$var2,$var3,$var4);

I'm not a php expert at all, so I'm of little use actually troubleshooting your code.

4

Just a quick 'food for thought'

Why don't you use process to just send the search criteria (ie dname, dlastname etc) to the linux side and then let linux to the rest? You can catch the variables using $var1 = $argv[1]; as DarkSabre described and use those in your php file.

Maybe I am missing something on the objective of your project so this is not an option but otherwise maybe the best solution.