4. Simple MySQL query
MagicMirror² v2.6.0 is available! For more information about this release, check out this topic.

Simple MySQL query

  • K

    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

    0
  • R

    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?

    0
  • T

    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.

    0
  • R

    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?

    0
    K
     1 Reply
  • K

    @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>
    0
    R
     1 Reply
  • R

    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)
    0
    K
     1 Reply
  • K

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

    0