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.

    Simple MySQL query

    Scheduled Pinned Locked Moved Requests
    7 Posts 3 Posters 3.1k Views 3 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.
    • K Offline
      kayhanbelek
      last edited by

      Hi there,
      I’m new here. I installed MagicMirror on RasPi without any problems. My current goal is to print the data of a table in the MySQL database. But I did not find a practical method on the forum. Anyone know of a ready-made module that can do this?

      Thanks in advance.

      Belek

      1 Reply Last reply Reply Quote 0
      • R Offline
        ralberth
        last edited by

        Sounds like a lot of fun. What’s the current feeling on a UI and expected config for this?

        How about the config is a string value holding database DNS, username, password, SQL SELECT statement, etc.
        Output just creates a formatted

        with alternating row coloring, date and number formatting, left/right alignment based on type, etc.?

        Should it refresh the query on a schedule maybe?

        1 Reply Last reply Reply Quote 0
        • T Offline
          TimCadieux
          last edited by

          I would create a RESTful api on your source server and then call the api using MMM-json-feed.

          You would then loop though the returned dataset.

          1 Reply Last reply Reply Quote 0
          • R Offline
            ralberth
            last edited by ralberth

            Sounds good. For a lark, I just wrote MMM-MysqlQuery which might help if your MM has access to the database directly.

            Let me know if this would work and if not, we’ll talk about REST?

            K 1 Reply Last reply Reply Quote 0
            • K Offline
              kayhanbelek @ralberth
              last edited by kayhanbelek

              @ralberth looks good. Is it possible to add additional variable like date (as bugun in sample code) in sql query?
              My table has monthly data, and I want to display related day’s data. here is my php code:

              $servername = "localhost";
              $username = "user";
              $password = "pass";
              $dbname = "hometest";
              $bugun = date("Y-m-d");
              // Create connection
              $conn = new mysqli($servername, $username, $password, $dbname);
              // Check connection
              if ($conn->connect_error) {
                  die("Connection failed: " . $conn->connect_error);
              }
              
              $sql = "SELECT tarih, sabah, oglen, aksam FROM yemek WHERE tarih='$bugun'";
              $result = $conn->query($sql);
              
              if ($result->num_rows > 0) {
                  echo "<table>";
              ......
              
              
              R 1 Reply Last reply Reply Quote 0
              • R Offline
                ralberth @kayhanbelek
                last edited by

                SELECT tarih, sabah, oglen, aksam FROM yemek WHERE tarih=‘$bugun’

                Looks like Turkish :-)
                “select date, morning, noon, evening from… where…”

                You could pick the current date on the MySQL server directly by calling a function on the MySQL server:

                SELECT tarih, sabah, oglen, aksam
                FROM yemek
                WHERE tarih=current_date()
                

                You can see this in action:

                mysql> create table dailystuff (tarih date);
                
                mysql> insert into dailystuff values ('2018-09-10');
                mysql> insert into dailystuff values ('2018-09-11');
                mysql> insert into dailystuff values ('2018-09-12');
                
                mysql> select * from dailystuff where tarih=current_date();
                +------------+
                | tarih      |
                +------------+
                | 2018-09-11 |
                +------------+
                1 row in set (0.00 sec)
                
                K 1 Reply Last reply Reply Quote 0
                • K Offline
                  kayhanbelek @ralberth
                  last edited by

                  @ralberth said in Simple MySQL query:

                  current_date()

                  Yes it’s Turkish :)

                  Module is working :thumbs_up:
                  But I need a little todo :)
                  I’m using this module for my son’s daily course schedule and meal chart.

                  Previously, I use a php script (on my NAS) to get data from mysql and and parse with my Home Assistan server from RestApi and pull form Home Assistant server via MMM-homeassistant-sensors :smiling_face_with_halo: (I know it’s very dirty method)

                  MySql query in php is displaying next day’s data after 15:00 each day (sample code bellow) to aware of next day program.

                      $bugun = date("l");
                      $yarin = date("l", time()+86400);
                      $hour = date('H');
                      if ($hour > 15) { $bugun = $yarin; }
                  

                  Is it possible to use additional string and if statement in config file?

                  1 Reply Last reply Reply Quote 0
                  • 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