InfoCaptor

  1. Home
  2. Docs
  3. InfoCaptor
  4. Data Sources
  5. MySQL

MySQL

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

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 

Connection handle from Data Tab

You can access as described in this section

Data Tab : Connections

Was this article helpful to you? Yes No

How can we help?