MagicMirror Forum
    • Recent
    • Tags
    • Unsolved
    • Solved
    • MagicMirror² Repository
    • Documentation
    • 3rd-Party-Modules
    • Donate
    • Discord
    • Register
    • Login
    A New Chapter for MagicMirror: The Community Takes the Lead
    Read the statement by Michael Teeuw here.

    Displaying data from MySQL database

    Scheduled Pinned Locked Moved Development
    9 Posts 6 Posters 5.0k Views 4 Watching
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • W Offline
      wolkenaj
      last edited by

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

      H 1 Reply Last reply Reply Quote 0
      • cirdanC Offline
        cirdan
        last edited by

        @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

        Modules MMM-rainfc, MMM-bustimes, MMM-Harmony, MMM-Rijks

        W 1 Reply Last reply Reply Quote 0
        • H Offline
          Henrik @wolkenaj
          last edited by

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

          cowboysdudeC 1 Reply Last reply Reply Quote 2
          • cowboysdudeC Offline
            cowboysdude Module Developer @Henrik
            last edited by

            @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.

            1 Reply Last reply Reply Quote 1
            • W Offline
              wolkenaj @cirdan
              last edited by

              @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?

              N 1 Reply Last reply Reply Quote 0
              • N Offline
                ninjabreadman @wolkenaj
                last edited by

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

                Problem with config or JavaScript? Copy/paste it into JSHint.
                Check out the detailed walkthroughs on install, config, modules, etc.

                W 1 Reply Last reply Reply Quote 0
                • W Offline
                  wolkenaj @ninjabreadman
                  last edited by

                  @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?

                  N 1 Reply Last reply Reply Quote 0
                  • N Offline
                    ninjabreadman @wolkenaj
                    last edited by

                    @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.

                    Problem with config or JavaScript? Copy/paste it into JSHint.
                    Check out the detailed walkthroughs on install, config, modules, etc.

                    1 Reply Last reply Reply Quote 0
                    • cruunnerrC Offline
                      cruunnerr
                      last edited by 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:

                      1_1522492360079_Bildschirmfoto 2018-03-31 um 12.30.56.png

                      Web Page:

                      0_1522492360077_Bildschirmfoto 2018-03-31 um 12.30.12.png

                      1 Reply Last reply Reply Quote 1
                      • 1 / 1
                      • First post
                        Last post
                      Enjoying MagicMirror? Please consider a donation!
                      MagicMirror created by Michael Teeuw.
                      Forum managed by Sam, technical setup by Karsten.
                      This forum is using NodeBB as its core | Contributors
                      Contact | Privacy Policy