MagicMirror² v2.5.0 is available! For more information about this release, check out this topic.

Simple MySQL query



  • 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



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



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



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



  • @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>";
    ......
    
    ```</table>


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


  • @ralberth said in Simple MySQL query:

    current_date()

    Yes it’s Turkish 🙂

    Module is working 👍
    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 😇 (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?