Creating Live Charts of MySQL Data

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

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:

• ystocks.sql
• newquotes.sql
• zip with both sql files

Creating new MySQL Database

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

C:\temp\ystocks>mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 28 Server version: 5.6.17 MySQL Community Server (GPL) Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create DATABASE ystock; Query OK, 1 row affected (0.00 sec) mysql> exit; Bye C:\temp\ystocks>mysql -u root ystock < ystocks.sql

The tables should be as follows:

mysql> show tables; +------------------+ | Tables_in_ystock | +------------------+ | ohlc | | quote | | stock | +------------------+ 3 rows in set (0.00 sec) mysql> select * from stock; +------+---------------------------------+--------+----------+--------+-------+-----------+------------+--------+---------+---------------+--------------------+ | sym | name | price | volume | pe | eps | week52low | week52high | daylow | dayhigh | movingav50day | marketcap | +------+---------------------------------+--------+----------+--------+-------+-----------+------------+--------+---------+---------------+--------------------+ | AMZN | Amazon.com Inc. | 678.99 | 4416158 | 972.77 | 0.7 | 285.25 | 682.77 | 668.45 | 682.77 | 607.9 | 318280000000 | | AXP | American Express Company Common | 72.24 | 3257427 | 13.04 | 5.54 | 70.21 | 94.89 | 72.17 | 72.74 | 74.53 | 71100000000 | | BA | Boeing Company (The) Common Sto | 148.34 | 3185032 | 18.67 | 7.95 | 115.14 | 158.83 | 147.71 | 149.7 | 143.67 | 99390000000 | | CAT | Caterpillar Inc. Common Stock | 71.02 | 3847626 | 14.63 | 4.86 | 62.99 | 106.63 | 70.89 | 71.9 | 71.24 | 41350000000 | ....

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: