Read a value from MySql/SQL

Using Arduino Networking, Protocols, and Devices
1

Hello,

First: i`m dutch, so my english is bad... But i try... :wink:

I started my project to read temp-sensors. This works fine now :slight_smile:
My data will be stored in a MySql database where i can generate grafics.

But now i want to read data from the MySql.
Like:

int variable 1 = ##read data from mysql db##
When temp is lower then variable1, led1 off.

Sow, is it possible to read data from an MySql database? If so, how can i do this?

Thank you!

2

The Arduino can not directly read from any file, database, text, or graphics, on the PC. The Arduino can talk to an application that runs on the PC that CAN talk to the database. But, you'll most likely need to write that application.

3

oke.... :frowning:

But i saw an topic of TextFinder (libery). I can't get it work...
Can anybody tell me if this is a goof function? If so, how it works?

Or is there an other libery for my goal?

4

Hi Goldvinkie,

Spend some time looking at PHP examples on the forum and playground.
PHP can be the wrapper around your mySQL db. An Arduino with ethershield can connect to PHP quite easily .

An Arduino has not enough memory and CPU power to do the encryption for direct MySQL access. And by using PHP you have a clear open interface which you can test in your browser! If you don't have a webserver with PHP, check - http://www.apachefriends.org/en/xampp-windows.html - it includes all you need - can take a few days before you are familiar with all of it...

further you can improve your English by using google translate -> libery => library :slight_smile:

Rob

5

Hoi Rob,

Als je toch uit nederland komt ... :wink: ...
Ik heb een webserver draaien met alle toeters en bellen erop en eraan.

Echter krijg ik het niet voor elkaar om een variabele uit een bestand.php te halen...
Ik ben al 3 dagen aan het rond neuzen op het forum en de playground, maar heb mijn antwoord nog niet gevonden...

Heb je nog een andere tip misschien?

Bedankt! :smiley:

6

(keep the discussion in English for the other members of the forum)

To store a value in the SQL database you need to do a HTTP POST from the Arduino to the PHP server.

some PHP code that processes a POST and store it in MySQL (assume database & table exist)
(code not tested by the way)

<?php
$id = $_POST["id"];
$data = $_POST["data"];

$file = "./debug.txt";
$fp = fopen($file, 'a');
$x = fwrite($fp, $data."\r\n"); 
fflush($fp);
fclose($fp);

$con = mysql_connect("localhost","name","password");
if (!$con) die('Could not connect: ' . mysql_error());
mysql_select_db("testdb", $con);

$sql = "INSERT INTO rawdata (id, data) VALUES ($id, $data)";

$result = mysql_query($sql,$con);
if (!$result) die('Error: ' . mysql_error());
mysql_close($con);
echo 'OK';
?>

To fetch data from the webserver you use a similar POST - but now only the id of the record you want to extract

<?php
$id = $_POST["id"];   // the id to fetch

$con = mysql_connect("localhost","name","password");
if (!$con) die('Could not connect: ' . mysql_error());
mysql_select_db("testdb", $con);

$sql = "SELECT data FROM rawdata WHERE  id = $id";

$result = mysql_query($sql);
while($row = mysql_fetch_array($result)) echo $row['data'];  // note this can be 0,1,more rows as result.
mysql_close($con);

echo 'OK';    // 
?>

Hopes this helps to get you started.