MySQL

 
 
Scenario: You have a PHP + MySQL application hosted on a webhosting company's server. You want to connect directly to the database and show data on the dashboard.
 
In this example we will assume the webhosting company is "MediaTemple" and they have instructions on how to enable your MySQL database so that it can be queried from outside
 
 
In the IP address section enter "74.207.232.152" (this is the IP for https://my.infocaptor.com)
 
So basically you are authorizing "my.infocaptor.com" to contact your MySQL database that is hosted on the webhost server.
 
Next create a database user for readonly access (following screenshots are from mediatemple administration)
 
 
e.g xyz_reader and give a password
 
Next, we update the permissions for this user so that it can only read the selected databases. This ensures that only "Select" operations can be performed on
this database tables.
 
 
 
 
 
Next, go to InfoCaptor menu Action > Manage Data Source
 
 
 
Select MySQL from the
Connection Template 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 box
5. 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
 
SELECT DATE_FORMAT(post_date, '%m-%b') AS period, COUNT( * )+1 AS post_count
FROM wp_posts
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
 
 
 
 
 
 
 
Use online Web Dashboards in the cloud or download and host your own Dashboards