Displaying data from MySQL database

[wolkenaj]

Hello! I am quite new to MagicMirror and trying to display personal data from a MySQL database (stored on amazon cloud) in text and graphs on my mirror. I currently have a mixture of html and php files to access the data and display on a website. I would like to transfer the functionality but am unsure how to do so in a compatible way.
I currently doing so with php:

	$db = mysqli_connect(REDACTED) or die('Error connecting to MySQL server.');
	//the SQL query to be executed
	$query = "SELECT * FROM table";
	//storing the result of the executed query
	$result = $db->query($query);
	//initialize the array to store the processed data
	$jsonArray = array();
	//check if there is any data returned by the SQL Query
	if ($result->num_rows > 0) {
	  //Converting the results into an associative array
	  while($row = $result->fetch_assoc()) {
		  $date= $row['timestamp'];
	    $jsonArrayItem = array();
	    $jsonArrayItem['label'] = $date;
	    $jsonArrayItem['value'] = $row['columnName'];
	    //append the above created object into the main array.
	    array_push($jsonArray, $jsonArrayItem);
	  }
	}
	//Closing the connection to DB
	$db->close();
	//set the response content type as JSON
	header('Content-type: application/json');
	//output the return value of json encode using the echo function. 
	echo json_encode($jsonArray);
?>

And then displaying with Fusion charts:

$(function(){
	  $.ajax({
	    url: 'http://REDACTED',
	    type: 'GET',
	    success : function(data) {
	      chart1Data = data;
	      var chart4Properties = {
	        "caption": " ",
	        "xAxisName": "Date",
	        "yAxisName": " ",
	        "rotatevalues": "1",
	        "theme": "zune"
	      };
	      api4Chart = new FusionCharts({
	        type: 'column2d',
	        renderAt: 'chart4-container',
	        width: '550',
	        height: '350',
	        dataFormat: 'json',
	        dataSource: {
	          "chart": chart4Properties,
	          "data": chart1Data
	        }
	      });
	      api4Chart.render();
	    }
	  });
	});

Any help would be appreciated. If there is any other information I can provide, please let me know. Thank you :)

[cirdan]

@wolkenaj said in Displaying data from MySQL database:

}

Copy an existing module with a node_helper.js. The graph section should go into the module js file, the php for db access rewritten into js and inserted into the node helper. Look at my rain forecast module for example https://github.com/73cirdan/MMM-rainfc

Alternative is to call the php from within the node helper

[Henrik]

@wolkenaj You could just display the charts from the website with the iFrame module.
https://github.com/alberttwong/MMM-iFrame

[cowboysdude]

@Henrik said in Displaying data from MySQL database:

@wolkenaj You could just display the charts from the website with the iFrame module.
https://github.com/alberttwong/MMM-iFrame

BINGO…that’s the easiest way :) I have a few ‘modules’ running that way on my mirror.

[wolkenaj]

@cirdan Thank you for this tidbit. I was aware that I would need a node helper. I think my preferred course would be to call the php file from the node helper. I will eventually need to also call a bash script from my node helper if possible as well. Do you know of a module that does a similar call that I could look at for reference?

[ninjabreadman]

@wolkenaj MMM-PIR-Sensor uses shell scripting in node_helper.js to turn the screen on and off with tvservice.

[wolkenaj]

@ninjabreadman This was very helpful to get a node helper function up and running. I am able to receive a socket notification and run a function based on the notification. My program in the node helper currently looks like

activateShell: function(){
     console.log("Activating shell");
     exec("/Test -s");
}

I receive the activating shell in the console but the script (a simple echo “Hello World”) does not execute. I’ve tried placing it in my module folder, the Magic Mirror folder, and as a path in the desktop, all to no avail. Would you have any ideas on what is wrong?

[ninjabreadman]

@wolkenaj /Test will tell the shell to look in the root of the filesystem (/) for Test. Instead, ./ will tell it to look in the current working directory (cwd), which I suspect is ~/MagicMirror. So ./Test is likely what you want.

Worst case, you could use the complete path (~/MagicMirror/Test or /home/pi/MagicMirror/Test) which should also work. I also suggest renaming Test to test.sh, just so it’s always immediately apparent that it’s a shell script.

[cruunnerr]

Don’t know if it helps and i am trying to keep it short…

I build an Oiltank measurement system with another raspberry pi.
The results will be written to a mysql database and to a JSON file, which will be uploaded to my NAS.
On the NAS i run the database and a web server.
To Display the results on a webpage i used this php script: https://github.com/cruunnerr/OilTank-Graph-WebPage

To display the results on my MM i got some very much help from our user @doubleT
He wrote this module for me: https://github.com/cruunnerr/MMM-Oiltank
The module grabs the data from a JSON file and shows it on the MM.

Maybe this could help :/

MagicMirror:

Web Page: