Sqlite3 - PHP - not working in Yun

Hello,

I’m monitoring a number of pir and reed sensors through my arduino yun. I wanted to make a personal webpage with a graph of the sensor readings (on/off) using google charts. I developed first on my linux pc with xampp (all files html, php and sqlite db in the same directory) and it works. I request the sqlite3 data via php script through Ajax call.

See png “WorkingGraphXAMPP_Ubuntu.png” (text under the graph is ‘google json’ data)

Then, I moved everyhing to the Yun, also every html, php and sqlite db in the same directory. (/mnt/sda1/arduino/www/htdocs)
But here, it doesn’t work ?? Instead of getting back a graph, the Ajax call returns the text of the php script ???
See png “FailedGraphArduinoYun”.

Why does this not work on the Yun → should I use CORS Ajax ???

Here is my html code

<html>
  <head>
	 <meta charset="utf-8">
	 <meta name="viewport" content="width=device-width, initial-scale=1">
	 <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script> 
	 <script src="https://www.gstatic.com/charts/loader.js"></script>
	 <script src="https://www.google.com/jsapi"></script>
    <script>
    
    // Load the Visualization API and the piechart package.
    google.charts.load('current', {'packages':['corechart']});
      
    // Set a callback to run when the Google Visualization API is loaded.

    var strQuery;
    var jsonData;
    
    function drawChart() {
      var options = {
          title: 'Statistiek Intrusies',
          titleTextStyle: {color:'brown',fontName:'Verdana',fontSize:18,bold:true,italic:true},
          //backgroundColor: {fill:'snow',stroke:'brown',strokeWidth:5},
          //chartArea:{backgroundColor:{fill:'snow',stroke:'brown',strokeWidth:5}},
          pointSize: 5,
          series: {
                0: { pointShape: 'circle'},
                1: { pointShape: 'triangle' },
                2: { pointShape: 'square' },
                3: { pointShape: 'diamond' },
                4: { pointShape: 'star' },
                5: { pointShape: 'polygon' }
          },      
          hAxis: {
          	//title: 'Time of Day',
          	titleTextStyle:{color:'brown',fontName:'Verdana',fontSize:16,bold:true,italic:true},
          	viewWindow: { min: [0,0, 0],max: [24, 0, 0]},
          	gridlines: {count: 8}
          },
          vAxis: {
          	//title:'Intrusion Level',
          	titleTextStyle:{color:'brown',fontName:'Verdana',fontSize:16,bold:true,italic:true},
          	viewWindow: {min: [0],max: [7]},
          	gridlines: {color:'lightgrey'},
          	ticks : [1,2,3,4,5,6,7]
          }
      };
      
      // Create our data table out of JSON data loaded from server.
      var data = new google.visualization.DataTable(jsonData);

      // Instantiate and draw our chart, passing in some options.
      var chart = new google.visualization.ScatterChart(document.getElementById('chart_div'));
      chart.draw(data,options);
    }
    
    function BuildQueryFromInputDate() {
 		var d=('0'+$('#dag option:selected').text()).slice(-2);
		var m=('0'+$('#maand option:selected').text()).slice(-2);
		var j=$('#jaar option:selected').text();
		strQuery='select * from SENSOR where datum=\''+j+m+d+'\' order by tijd';
	 }	

	 $(document).ready(function(){
		var vandaag=new Date();
		var maand=vandaag.getMonth()+1;
		var dag=vandaag.getDate();
		var jaar=vandaag.getFullYear();
	
		// Fill in options list for choosing date to draw intrusion statistics
		for (var i=1;i<=31;i++) $('#dag').append('<option>'+i+'</option>');
		for (i=1;i<=12;i++) $('#maand').append('<option>'+i+'</option>');
		for (i=2010;i<=2016;i++) $('#jaar').append('<option>'+i+'</option>');
		// Set current date in options list, disable maand & jaar 
		$('#dag').val(dag);
		$('#maand').val(maand);
		$('#jaar').val(jaar);
		//$('#maand').prop('disabled',true);
		$('#jaar').prop('disabled',true);
   	BuildQueryFromInputDate();

	 	$("#btnTekenGrafiek").click(function() {
      	jsonData = $.ajax({
	         url: "myphpajax.php",
   	      data: { phpQuery : strQuery},
      	   type: "POST",
         	dataType: "text",
         	async: false,
         	success: function(jsonData){console.log('Gelukt');console.log(jsonData)},
				error: function(xhr,status){console.log('Sorry, problem loading');},
				complete: function (xhr,status) { console.log('Ajax request complete');}
         }).responseText;	 
         	
         $("#fout1").text(jsonData);
         
         if (jsonData == 'EMPTY') $('#chart_div').text('No data available');
         else 	google.charts.setOnLoadCallback(drawChart);
	 	});

	 });


    </script>
  </head>

  <body>
		<select id='dag' name="dag" onchange="BuildQueryFromInputDate()"></select>
		<select id='maand' name="maand"></select>
		<select id='jaar' name="jaar"></select>
		<button id="btnTekenGrafiek">Submit Ajax Request & draw graph</button>
		<div id="chart_div" style="width: 400px; height: 240px;"></div>
  </body>
  <p id="fout1">fout1</p>
</html>

Here is my php code

<?php
$db = new SQLite3('MySensors.sq3');
$phpQry = $_POST['phpQuery'];
file_put_contents("Query.qry", $phpQry);
$results = $db->query($phpQry);
//$results = $db->query('SELECT * FROM SENSOR where datum=\'20160208\' order by tijd;');

ob_start(); // output buffering started

if (! $results->fetchArray()) {
	print "EMPTY";
} 
else {
	print "{\"cols\":[";
	print "{\"type\":\"timeofday\",\"label\":\"TIJD\"},";
	print "{\"type\":\"number\",\"label\":\"PIR1\"},";
	print "{\"type\":\"number\",\"label\":\"PIR2\"},";
	print "{\"type\":\"number\",\"label\":\"PIR3\"},";
	print "{\"type\":\"number\",\"label\":\"PIR4\"},";
	print "{\"type\":\"number\",\"label\":\"VDEUR\"},";
	print "{\"type\":\"number\",\"label\":\"ADEUR\"}";
	print "],";
	print "\"rows\":[";
	
	/* PIR1=0,PIR2=1,PIR3=2,PIR4=3,VDEUR=4,ADEUR=5,DATUM=6,TIJD=7 */
	$results->reset();
	$DOORGAAN = TRUE;
	$row = $results->fetchArray();	
	while ($DOORGAAN) {
		$uur=substr($row[7],0,2);
		$min=substr($row[7],2,2);
		$sec=substr($row[7],4,2);
		print "{\"c\":[{\"v\":[".$uur.",".$min.",".$sec."]}";
		for($i=0;$i<6;$i++) {
			if ($row[$i]==0) print ",{\"v\":null}";
			else print ",{\"v\":".$row[$i]."}";
		}
		print "]}";
		$row = $results->fetchArray();
		if ($row) {
			print ",";
		} 
		else {
			$DOORGAAN = FALSE;
			print "]}";
		}	
	}
}
$db->close();
$strJSON=ob_get_clean(); // output buffering stopped
file_put_contents("sample2Data.json", $strJSON);
echo $strJSON; 
?>

Any help greatly appreciated.

Thanks
Leo

Tutorial by a regular member of the this forum.

http://ibuyopenwrt.com/index.php/8-yun-compatible/52-php-and-sqlite3

Hey Jesse,

Thanks for your answer but I can't see how this helps me further.

Everything works under Linux (Ubuntu with XAMMP), if I move everything to my arduino yun (all in the same arduino yun map), it doesn't work anymore ???

The thing is that the PHP script is not executed but the code-text of the script is returned as answer of an ajax request ???

So why is that ?

Best wishes
Leo

@Leo,
your PHP is not setup properly. Specifically, it is not returning the proper header.

NOTE: php is NOT pre-installed on Arduino Yun. Hence, the link I gave you.

So, you cannot just copy the PHP scripts into a directory and expect it to work. Reinstall PHP.
Test PHP first, then run you scripts.

Jesse

I dont know if yun has xampp support, Id move from php to python and use Flask for the framework.

Hello Jessy, Mart,

Finally it works. I installed php5-cgi (php5-cli was already installed) and edited the config file of uhttpd by uncommenting

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

Thanks for the help.

Best wishes
Leo

Thats good, nice that openwrt has php support.