Simple MySQL query

[kayhanbelek]

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

[ralberth]

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?

[TimCadieux]

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.

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

[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>";
......

[ralberth]

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)

[kayhanbelek]

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