Overclock.net banner

Web Tracking Board to Display SQL Query

360 views 2 replies 3 participants last post by  Mrzev 
#1 ·
At work we have a SQL query that gets run against one of our application databases and then gets emailed out to a list of recipients who need to then follow up and take action on the items in that report.

What we would like to do is create a web tracking tool that auto refreshes say every 5mins that runs and displays that SQL query. What would be the best way to achieve this?

Would something like using XAMPP on a VM webserver to host a php site that's displaying the query work?

Could there be a way to send out text message or email alerts when that query returns something?

Any guidance would be appreciated!

Thanks
 
#2 ·
You seem to have come up with the gist of it yourself. One thing to note is that XAMPP is a development software suite, so you wouldn't want to run your live server with it. For scripting the SQL query, you have plenty of options, but if you have php developers at work or somebody who knows some basic php you should be able to write a service quite easily. Using javscript with Node.js is also a good option, as you don't need to setup apache since node.js can be used as a webserver directly via express.js.

If you want to setup a way to respond to the new data, I'd make sure you've got the server setup with SSL. After that you can send the data via POST requests. I'd also set up some type of authentication scheme, but you'll have to decide weather you want to use basic authentication or something more complicated. In either case, make sure all data input from the user is properly escaped to avoid SQL injection attacks.

Depending on how many people are accessing the page at once, you may want to so some caching of the SQL query results. Refreshing the page is a just a bit of javascript.
Emails are pretty simple, as PHP has a built in function to send mail, and sending mail with node.js is nearly as simple. I have no experience with text messages, but you might want to take a look at this page: https://davidwalsh.name/send-text-message

I hope this helped you out a bit
 
#3 ·
Quote:
Originally Posted by Jtvd78 View Post

Depending on how many people are accessing the page at once, you may want to so some caching of the SQL query results. Refreshing the page is a just a bit of javascript.
Emails are pretty simple, as PHP has a built in function to send mail, and sending mail with node.js is nearly as simple. I have no experience with text messages, but you might want to take a look at this page: https://davidwalsh.name/send-text-message
Emails can be a pain. The main issue i have with doing Emails is figuring out which server and port to target and how to pass the credentials to send the email. Like for me to send an email from my gmail account, the first time i did this I hardcoded my username and password which is BAD to do (was for a quick test project). The second time i think I was able to generate an API key from them and use it, but then I ran into issues with something else. I also ran into issues where emails that were being sent were getting flaged as spam for whatever reason.

But, I do agree, this is a pretty simple straight forward application. Setting up PHP with Apache is sometimes easy, and sometimes a pain... i never know why. Just to point out, you have 2 componets to this. You have your webpage that reads data from a DB, and an application that checks the DB for alerts every 5 minutes. The webpage will need to be a server side scripting language like PHP\Java\ASP\Node.JS so you can get information from the webpage. The app that reads every 5 minutes can be a simple Python script, C++ app, C# app, really just about anything. You then need to tie a scheduler to that application, or perhaps make it a service that is always on.

To take it another step, you add a watchdog app where its only job is to make sure that application is always running, or start it back up if it goes down for whatever reason.
 
This is an older thread, you may not receive a response, and could be reviving an old thread. Please consider creating a new thread.
Top