Programming a Telegram Bot Using Node-RED, PHP, and MySQL


 
Thread Tools Search this Thread
Top Forums Programming Programming a Telegram Bot Using Node-RED, PHP, and MySQL
# 1  
Old 03-05-2020
Programming a Telegram Bot Using Node-RED, PHP, and MySQL

Yesterday, I announced the first release of a "Telegram Computer Trivia Bot" in this post:

Play Computer Trivia on Telegram

In this post, I will provide a high level overview of the application.

The main components in this application are:
  • Node-RED
  • LAMP (Linux, Apache2, MySQL, PHP)
  • Telegram (the messaging app)

The key Node-RED modules / nodes used:
  • exec : runs a system command and returns its output
  • node-red-contrib-telegrambot

Please note, I do not use the Node-RED nodes from node-red-node-mysql because I find this approach less flexible (and more buggy) than just writing the queries in javascript in a Node-RED function. However, you are free to use the node-red-node-mysql node if that moves your spirit and fits your style.

Here is the highest level overview of the current bot flow in Node-RED in this app. The little white squares are called "Link Nodes" and these are nodes which allow us to control inputs and outputs between flows and busy areas (so we can keep the actual flows easier to read and manage, and keep the wiring between nodes easier to read when there is a lot going on):

Programming a Telegram Bot Using Node-RED, PHP, and MySQL-screen-shot-2020-03-05-111320-amjpg


Next, is a screenshot of the trivia game play flow of this Telegram bot application:

Programming a Telegram Bot Using Node-RED, PHP, and MySQL-screen-shot-2020-03-05-111516-amjpg


In a future post, I will add some PHP and Javascript code used in the app, but in this post, I want to keep it high level.

So, in a nutshell and at a high level:
  1. The Telegram receiver listens for incoming Telegram messages on the designated "bot channel".
  2. All valid messages are logged in a MySQL DB.
  3. The valid messages go to a "big switch" node which uses a REGEX on the command strings to route them appropriately.
  4. Commands are processed using either a shell "exec" node or in a JS functions I wrote.
  5. Most of the input / output (but not all) are JSON formatted messages.
  6. The message replies from the APIs (PHP scripts) are formatted and sent back the sender.

The heart of the trivia game bot is illustrated in this second screenshot where:
  • Requests for a new trivia question is checked against the DB calling a PHP script, which in turn, does all the MySQL queries to the trivia DB tables.
  • If there are no unanswered questions for the player, a new question is retrieved and send to the user. Otherwise, the player is reminded to answer the unanswered question.
  • When the player responds true or false, their answer is sent to either one of two PHP scripts which processes true or false, updated the DB, formats the reply and returns it to the player.
  • If the player answers true or false when all questions have been answered, they are reminded to request another trivia question.
  • Players can also check their score in a similar manner (there are no public leaderboards in this app, unlike the web-based version on the site).

Of course, as all experienced programmers know, there are myriad ways to build a Telegram bot and a trivial game like this in Node-RED; so let me tell you why did it this way. My way may not work the best for you.

When the MySQL DB is on the same server as the Node-RED application (as it is in this case) I prefer to do the DB query and all the "heavy lifting" in PHP. This provides me more error checking and more flexibility than doing the MySQL queries directly from Node-RED. This method is also easier to debug. However, we already have a ton of operational PHP code, so it "fits" our LAMP backend. If you don't have a big LAMP application running already, this method may not be appropriate.

I started using this "exec a PHP script to talk to MySQL" a bit later in the process of building this app, so there are still a few Javascript function (mysql query) nodes in the flow. These could easily be converted to an "exec a PHP script to talk to MySQL" or the other way around. Everyone has their own preferences (and I am not in the "clean-it-up" stage at this time).

As a side note, I have noticed from participating in the excellent Node-RED forum that a few very experienced Node-RED people are a bit "religious" about Node-RED nodes and some folks will spend a lot of time trying to make a "node" work when a javascript function or shell exec is faster to code and works just as well. Sometimes, I read posts like "why don't you do this in this node or that node", when a few lines of Javascript or a shell exec works just as well.

From my perspective, I see a some good people miss the point about system engineerings and the true beauty of Node-RED as a systems integration, visual programming tool; because, as we often see, some folks are more worried about "node purity", or their "religious view of Node-RED" versus embracing the fact that we don't need to get to "wrapped around the axel" over "nodes".

Node are good. Coding is good. It's all good. That's my view. I don't have any religion about Node-RED (or any tech).

My advice / recommendation is to use "nodes" when they are convenient and work well for you; but don't waste your application development time trying to force a "node" to do a function which can easily be done another way. Time is precious. Don't waste it trying to force square pegs into round holes.

In addition, if you keep seeing bugs in a node / flow and cannot get a particular "node" to work as you expect, my advice is to quickly "move on" and write your own code / function / methods. That is what I do, and I can now prototype, build and test applications in a very short time period.

Node-RED is a great tool and I really love it. However, I do not get stuck in the "we must use a pre-packaged node" mode of thinking, and realize that many tasks can be also done in any of my (your) favorite programming languages and integrated into Node-RED.

After all, that is the true beauty of a system engineering / integration tool like Node-RED, right?

The downside is that you might find yourself on the opposite end of the barb from an experienced Node-RED lover / purist who sees all Node-RED tasks as a "third party node" application, but don't let that discourage you.

Code in your own style and enjoy!

Later on, I'll post some of the JS function code and some of the PHP scripts I used for this application, which is currently running and operational, as you can see from this earlier post.

Play Computer Trivia on Telegram

I am a bit busy with other tasks the rest of this week, but I'll try to find some time to color in more details in some follow-up posts.

Cheers!
This User Gave Thanks to Neo For This Post:
# 2  
Old 03-05-2020
Thanks a TON Neo for sharing this Smilie It really helps to all of us who want to learn.

After getting inspired from you, highly, I too started, yesterday, and installed node-red in windows system and created a very basic flow of reading a csv file and reading Earthquake data from tutorial itself as of now Smilie

you are really GREAT in learning things FAST(I.T techs), I have never seen a person learning this much fast honestly saying Smilie

Let me see if I could prepare one and share it here too with all of us Smilie

Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
# 3  
Old 03-05-2020
Quote:
Originally Posted by RavinderSingh13
Thanks a TON Neo for sharing this Smilie It really helps to all of us who want to learn.
After getting inspired from your highly, I too started, yesterday installed node-red in windows system and created a very basic flow of reading a csv file and reading Earthquake data from tutorial itself as of now Smilie

you are really GREAT in learning things FAST(I.T techs), I have never seen a person learning this much fast honestly saying Smilie

Let me see if I could prepare one and share it here too with all of us Smilie

Thanks,
R. Singh
Thanks Ravinder,

Yes, everyone who knows me well, knows I am a systems engineer at heart love, learning new things and building new apps, cross domain, with new tech. That's just me. I have no "system or domain" religion about tech. It's all use a "tool in the big toolbox" as far as I am concerned.

Thanks for the kind words, Ravinder.

I always try to keep the mind of the beginner, even after many decades of IT experience, and so I keep trying, learning and building new things.

But that approach also can bring criticism from those who love to use (or remain nostalgic for) the same old technology introduced decades (or just a few years) ago, religiously; and my "tech style" also will result in criticism from those who are more of tech "purists".

Honestly, the criticism does not bother nor influence me. Everyone should dance to the music of their own life. To each his or her own, as they say!

Cheers and Thanks Again for the Kind Words, Ravinder!

Anyway, Node-RED is fun and easy to use, I think, especially if you love to program already. I already program "OK" (not great) in Javascript, so this makes Node-RED much easier, I think; and since I already write a lot of PHP code, it's also easy to call PHP scripts (any scripts, actually) from Node-RED as well.
This User Gave Thanks to Neo For This Post:
# 4  
Old 03-12-2020
Hello Neo/All,

Very glad to tell you that after getting inspired from Neo I have build my own very first Bot in Telegram + NR + shell + python(a bit) + JS suit as of now.

Following is its screen shot(my screen is not big as Neo's so couldn't capture all of it Smilie )

Programming a Telegram Bot Using Node-RED, PHP, and MySQL-user302148617_pic1289_1584033169jpg


I will post a detailed thread on weekend in a new post, Thanks again Neo for letting know about this technology; O hope I could learn more from it.

Thanks,
R. Singh
These 2 Users Gave Thanks to RavinderSingh13 For This Post:
# 5  
Old 03-13-2020
Good job Ravinder.

Always a pleasure to see people learning new things and using that knowledge to solve real-world problems.

Can you use kind of system at your work?
This User Gave Thanks to Neo For This Post:
# 6  
Old 03-13-2020
Quote:
Originally Posted by Neo
Good job Ravinder.
Always a pleasure to see people learning new things and using that knowledge to solve real-world problems.
Can you use kind of system at your work?
Thank you for encouragement Neo Smilie

Yes, I did some stuff from this one(already written almost 8 to 9 functions).

But in company I believe they will NOT allow Telegram(since it could be information breach; usually sites apps like Whats app, telegram are NOT trusted in firms) so I am thinking to do this with Microsoft's Skype(which is official and is used in firms too). Let me have more hands on with this one at home then I will try to implement this with official communicator etc.

And again yes, its very powerful; right now I written functions for eg-->
  • 1- Know number of opened files in a server(where code is deployed basically for time being, not connected to other servers).
  • 2- Check disk space for mount(will enhance it to take input from user for mount).
  • 3- Check number of running processes in system.
  • 4- Get N number of lines(entered by user value) of log file.
  • 5- Know system's uptime.
  • 6- Starting and stopping tomcat in progress.
  • 7- Installing few software (like tomcat, systemd etc) are in progress.

I will post a detailed version of it over weekend(migration cancelled this weekend).

Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
# 7  
Old 03-13-2020
Yes, I agree many company will not permit an app like Telegram, especially a big company like yours.

On this point, I cannot advise how to proceed because your companies risk management team will set the policy of what can and cannot be done.

Always work with your IT risk management team in a step-by-step manner, because they are important partners in IT projects!
This User Gave Thanks to Neo For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

7 More Discussions You Might Find Interesting

1. What is on Your Mind?

MQTT, Node-RED, Linux, Apache2, MySQL, PHP, Telegram, ESP32, ESP8266, Arduino

I have just completed the first phase of integrating all these devices and technologies: MQTT, Node-RED, Linux, Apache2, MySQL, PHP, Telegram, ESP32, ESP8266, and the Arduino Uno The glue that binds all this together is MQTT. In fact, MQTT makes this kind of integration nearly trivial to... (1 Reply)
Discussion started by: Neo
1 Replies

2. Programming

Node-RED: Writing MQTT Messages to MySQL DB with UNIX timestamp

First, I want to thank Neo (LOL) for this post from 2018, Node.js and mysql - ER_ACCESS_DENIED_ERROR I could not get the Node-RED mysql module to work and searched Google until all my links were purple! I kept getting ER_ACCESS_DENIED_ERROR with the right credentials. Nothing on the web was... (0 Replies)
Discussion started by: Neo
0 Replies

3. Programming

Publish and Subscribe to AES-256 Encrypted MQTT Messages to Node-RED from PHP Scripts

Various Node-Red crypto modules do not work with PHP, so to send an encrypted message from a PHP script (in this case from a Ubuntu server) to Node-RED we need our own code. After a few hours of searching, testing various libs, more testing and debugging, I got this PHP to Node-RED code... (0 Replies)
Discussion started by: Neo
0 Replies

4. Infrastructure Monitoring

Using Node-RED and MQTT to Monitor Server and Application Stats

After setting up MQTT and testing some ESP8266 and ESP32 modules, where I noted that testing in Programming ESP32 (ESP-WROOM-32) as an MQTT Client Subscribed to Linux Server Load Average Messages, I was so impressed with MQTT that I installed MQTT on three different computers, instantly and... (2 Replies)
Discussion started by: Neo
2 Replies

5. Web Development

Node.js and mysql - ER_ACCESS_DENIED_ERROR

This problem has been killing me all day, and I cannot solve it. Basically, I am using node.js with the mysql module and it will not connect to the database. Here is the JS code snippet in node.js: app.get("/test", function(req, res) { var mysql = require("mysql"); var con =... (4 Replies)
Discussion started by: Neo
4 Replies

6. What is on Your Mind?

Telegram Bots - Bot Code Examples

I'm currently looking into ways to integrate the Telegram API into the forums: Telegram Bots - Bot Code Examples I'm thinking, first off, to use the Telegram API to get forum alerts and notifications (to Bot or Not?). Second, I thinking of ways to more deeply integrate Telegram into the... (5 Replies)
Discussion started by: Neo
5 Replies

7. Red Hat

Can't uninstall MYSQL from RED HAT ES

Hi Everyone Could you kindly advise on how I should do a chkconfig and uninstalling mysql rpm on Red hat ES.Please check the errors that I'm getting below. :eek: I need to uninstall MySQL completely from my linux system ver 2.4.21-37 and use the chkconfig command to switch it on... (15 Replies)
Discussion started by: CollenM
15 Replies
Login or Register to Ask a Question