PHP, SQLITE3 and Yun

Hardware Arduino Yún
#1

Install Sqlite3:

opkg update
opkg install sqlite3-cli

sqlite3 /mnt/sda1/sensor.db

sqlite> CREATE TABLE sensor_data(
    id INTEGER PRIMARY KEY,
    temperature VARCHAR(64),
    sqlitetimestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

sqlite> INSERT INTO sensor_data (temperature) VALUES (30);
sqlite> INSERT INTO sensor_data (temperature) VALUES (40);

sqlite> select * from sensor_data;

sqlite>.quit

sqlitetimestamp DATETIME DEFAULT CURRENT_TIMESTAMP, will auto CURRENT_TIMESTAMP.

#2

Php command line:

opkg update
opkg install php5-cli php5-mod-sqlite3

nano /mnt/sda1/sensor.php

#!/usr/bin/php-cli
<?php
$db = new SQLite3('/mnt/sda1/sensor.db');
$query = "INSERT INTO sensor_data (temperature) VALUES( ".$argv[1]." )";
$db->exec($query);
$db->close();
?>

chmod 755 /mnt/sda1/sensor.php

/mnt/sda1/sensor.php 50

Use

sqlite3 /mnt/sda1/sensor.db

sqlite> select * from sensor_data;

confirm insert is OK.

#3

ATmega32u4 code:

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

Change

int temperature = random(0, 100);

to

int temperature = 50 + random(0, 5);

for better represent nature.

#4

Set uhttpd and php:

opkg update
opkg install php5-cgi

nano /etc/config/uhttpd

        list interpreter        ".php=/usr/bin/php-cgi"
#       list interpreter        ".cgi=/usr/bin/perl"

/etc/init.d/uhttpd  stop
/etc/init.d/uhttpd  start

nano /www/sensor.php

<?php
$db = new SQLite3('/mnt/sda1/sensor.db');
$results = $db->query('select * from sensor_data');
while ($row = $results->fetchArray()) {
print "<pre>";
    var_dump($row);
print "</pre>";
}
$db->close();
?>

visit “http://arduino.local/sensor.php”

#5

Revision Php code:

<?php
$db = new SQLite3('/mnt/sda1/sensor.db');
$results = $db->query('select * from sensor_data');
$cols = $results->numColumns();
print "<table width='400'  border='1' cellspacing='0' cellpadding='0'>\n";
print "<tr>";
for ($i = 0; $i < $cols; $i++) {
        print "<th align='left'>".$results->columnName($i)."</th>";
}
print "</tr>\n";
while ($row = $results->fetchArray()){
        print "<tr>";
        for ($i = 0; $i < $cols; $i++) {
                print "<td>".$row[$i]."</td>";
        } //end for
        print "</tr>\n";
}
print "</table>\n";
$db->close();
?>

#6

Thank you so much sonnyyu, it all works fine. Now my aim is to show this php file as a part of a website or in

other words update a website for worldwide access through my arduino yun’s database (sqlite or mysql). Do you

think you can direct me somewhere for this?

#7

fatihturhan:
Thank you so much sonnyyu, it all works fine. Now my aim is to show this php file as a part of a website or in

other words update a website for worldwide access through my arduino yun's database (sqlite or mysql). Do you

think you can direct me somewhere for this?

Start new thread?

#8

fatihturhan:
other words update a website for worldwide access through my arduino yun's database (sqlite or mysql). Do you think you can direct me somewhere for this?

Now you're stepping out of the realm of something that is specific to the Yun, and into the domain of general web programming. For the most part, web page programming with PHP and SQL should be pretty generic and equally applicable to the Yun.

Your best bet is to do some general web programming research, sticking to this forum will likely severely limit your audience. Years ago, I got the book PHP and MySQL Web Development by Luke Welling and Laura Thomson, published by Sams Publishing, and found it very helpful. While it's specific to MySQL, I would think most of the concepts would also apply to sqlite. Or, I'm sure that there are books out there dedicated to PHP and sqlite which may be equally valuable.

#9

@fatihturhan,

As others have suggested this is a new topic. However, sqlite is a library and mysql is a server - two different beasts. The SQL is standard, but sqlite is limited - the official website is also thin on information. And Mysql is now owned by Oracle. Yes, start a new thread.

Jesse

#10

Use D3.js to display line charts from data:

#11

Thanks a lot to all of you guys :smiley:

#12

I have tried to get this to work on my Yun, I manage to install Sqlite3 but when i put the code

nano /mnt/sda1/sensor.php

it says it cannot find nano…

-ash: nano: not found

So i created the php file with the code provided and saved it in the root part of the SD card. (which I think is correct??)

I then do:

Use

Code: [Select]
sqlite3 /mnt/sda1/sensor.db

Code: [Select]
sqlite> select * from sensor_data;

confirm insert is OK.

which all works perfectly…I then install the code into the ATmega32u4 with no issues.

The next part works fine:

Set uhttpd and php:

Code: [Select]
opkg update
opkg install php5-cgi

but I have the same problem when i put

nano /etc/config/uhttpd

It come up with

-ash: nano: not found
root@MD3000:~# /etc/config/uhttpd
-ash: /etc/config/uhttpd: Permission denied
root@MD3000:~# list interpreter “.php=/usr/bin/php-cgi”
-ash: list: not found
root@MD3000:~# # list interpreter “.cgi=/usr/bin/perl”

I definitely think I am doing something wrong and don’t quite understand the process, so any input would be great help!

#13

nano is a basic text editor. You can use any text editor you choose, or you can install it:

opkg update
opkg install nano
#14

Hardcj:
I have tried to get this to work on my Yun, I manage to install Sqlite3 but when i put the code
it says it cannot find nano...
...

nano is in current factory firmware, if it says it cannot find nano, mean you need update firmware.

Please upgrade your Yún - latest is 1.5.3

#15

it is possible to change directory /www → to /mnt/sda1/www

if i change config uhttpd also. Can I change its directory? Because I want to load and use everything from my sd card only.

Thank you for helping.

Becaue

sonnyyu:
Set uhttpd and php:

opkg update

opkg install php5-cgi

nano /etc/config/uhttpd

list interpreter        “.php=/usr/bin/php-cgi”
#      list interpreter        “.cgi=/usr/bin/perl”

/etc/init.d/uhttpd  stop
/etc/init.d/uhttpd  start

nano /www/sensor.php
<?php

$db = new SQLite3(’/mnt/sda1/sensor.db’);
$results = $db->query(‘select * from sensor_data’);
while ($row = $results->fetchArray()) {
print “

”;

    var_dump($row);

print “
”;
}
$db->close();
?>




visit "http://arduino.local/sensor.php"
#16