Creating Live Charts of MySQL Data

We are going to create a sql Dashboard to display live data from a MySQL database.

Creating a MySQL SQL Dashboard

Getting Started

In this tutorial we will step-by-step show you how to initialise your database, start a new dashboard, add your charts and to save your work. To get started download the following files:

Creating new MySQL Database

Create an empty database and pipe the SQL file from above into it to create three tables:

The tables should be as follows:

Add MySQL Server Connection

To add a connection goto Server->"Add Server.." and fill in your details.
You can try clicking test to see if the connection works before adding it.

Adding our First Charts

Click on "Add Data Table" and paste in the following query:

select * from stock where sym='AMZN'

Adding a Time Series Graph

Next click to add a time series graph and paste in the following SQL query

select date,adjClose from ohlc where sym='AMZN'
You should now have a chart that looks like this:

Adding an OHLC Chart

The next SQL chart we want is a candlestick:

select * from ohlc where sym='AMZN' AND (date > CURDATE()-60)
You should now have charts that look like this:

Inserting more data to see a Live Chart Update

To see how quickly the charts update run the SQL from newquotes.sql while watching the charts. You will see the charts update in real-time.

Allowing User Interaction with Forms

Forms allow user interaction by placing user input within the SQL queries. The dollar symbol is used to specify these keys. below you can see the configuration to specify an argument called key1

The SQL code then changes from/to:


select * from ohlc where sym='AMZN' AND (date > CURDATE()-60)
select * from ohlc where sym=$key1 AND (date > CURDATE()-60)

Our Demo Has Much More

sqlDashboards have a builtin MySQL demo with much more functionality. It can be found under the help menu: