How to create MySQL database connection
From within the Dashboard Editor, go to InfoCaptor menu Action > Manage Data Source
You can access the connection manager from the Data Tab “Create Connection”
Select MySQL from the left panel and create a connection for MySQL
Connection Handle : Give a good name so you can identify the database that you will be connecting to. This is the value that you will reference henceforward
Hostname or IP Address : This should be the IP address or the host name where your database is hosted. The above example is for mediatemple external host
Port : The default value is 3306 but verify if it is different and use it appropriately
Database name : The name of the database
Database user : In our case we created a readonly db user so we use it here. But you can use any user that can connect to the mysql db.
Database Password : password for the db user.
Description : Give some useful description.
Once connected, you can go to the dashboard and write SQL against the handle
1. Add a box or icon in the canvas
2. Right click on the box and select “Data Source”
3. Select the above connection handle “mediatemple_one”
4. Type the SQL query in the SQL box5. Click OK
The results will populate in the box. You have total freedom with this approach. Query any table and as many databases. On the same dashboard you can show information from multiple mysql databases.
For e.g if you have more than 1 wordpress blog then just create connection for each database and duplicate the widget and just change the connection handle. The sql query does not need to be changed.
Example 2: Consider the following query against wp_posts table
DATE_FORMAT(post_date, '%m-%b') AS period
, COUNT( * )+1 AS post_count
WHERE DATE_FORMAT( post_date,'%Y') =2009
GROUP BY DATE_FORMAT( post_date,'%b')
order by 1 asc
The output of the above query looks like this in phpmyadmin
Same query run against a Grid widget looks like below
Same query run against a bar chart looks like below
Connection handle from Data Tab
You can access as described in this section