Go Down

Topic: PHP, SQLITE3 and Yun (Read 11312 times) previous topic - next topic

sonnyyu

Install Sqlite3:

Code: [Select]
opkg update
opkg install sqlite3-cli


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

Code: [Select]
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.







sonnyyu

Php command line:

Code: [Select]
opkg update
opkg install php5-cli php5-mod-sqlite3


Code: [Select]
nano /mnt/sda1/sensor.php

Code: [Select]
#!/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();
?>


Code: [Select]
chmod 755 /mnt/sda1/sensor.php

/mnt/sda1/sensor.php 50


Use

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

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

confirm insert is OK.




sonnyyu

#2
Mar 17, 2015, 10:36 pm Last Edit: Mar 19, 2015, 05:47 pm by sonnyyu
ATmega32u4 code:

Code: [Select]
#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

Code: [Select]
int temperature = random(0, 100);

to

Code: [Select]
int temperature = 50 + random(0, 5);

for better represent nature.



sonnyyu

#3
Mar 17, 2015, 10:40 pm Last Edit: Mar 17, 2015, 10:41 pm by sonnyyu
Set uhttpd and php:

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


Code: [Select]
nano /etc/config/uhttpd

Code: [Select]
       list interpreter        ".php=/usr/bin/php-cgi"
#       list interpreter        ".cgi=/usr/bin/perl"



Code: [Select]
/etc/init.d/uhttpd  stop
/etc/init.d/uhttpd  start


Code: [Select]
nano /www/sensor.php

Code: [Select]
<?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"




sonnyyu

#4
Mar 17, 2015, 11:56 pm Last Edit: Mar 17, 2015, 11:59 pm by sonnyyu
Revision Php code:

Code: [Select]
<?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();
?>





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?

sonnyyu

#6
Mar 18, 2015, 04:46 pm Last Edit: Mar 18, 2015, 04:46 pm by sonnyyu
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?




ShapeShifter

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.

jessemonroy650

@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

sonnyyu

#9
Mar 19, 2015, 06:14 pm Last Edit: Mar 19, 2015, 06:23 pm by sonnyyu
Use D3.js to display line charts  from data:

http://forum.arduino.cc/index.php?topic=309600.0









fatihturhan

Thanks a lot to all of you guys :D

Hardcj

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

Quote
nano /mnt/sda1/sensor.php
it says it cannot find nano...

Quote
-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:
Quote
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:

Quote
Set uhttpd and php:

Code: [Select]
opkg update
opkg install php5-cgi
but I have the same problem when i put

Quote
nano /etc/config/uhttpd
It come up with

Quote
-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!

ShapeShifter

nano is a basic text editor. You can use any text editor you choose, or you can install it:
Code: [Select]
opkg update
opkg install nano

sonnyyu

#13
Jun 01, 2015, 03:56 am Last Edit: Jun 01, 2015, 03:57 am by sonnyyu
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

http://forum.arduino.cc/index.php?topic=279008.0








salad

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
Set uhttpd and php:

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


Code: [Select]
nano /etc/config/uhttpd

Code: [Select]
        list interpreter        ".php=/usr/bin/php-cgi"
#       list interpreter        ".cgi=/usr/bin/perl"



Code: [Select]
/etc/init.d/uhttpd  stop
/etc/init.d/uhttpd  start


Code: [Select]
nano /www/sensor.php

Code: [Select]
<?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"





Go Up