Displaying SQL result data as chart, other visualization may be a need for the data science related websites. In our earlier articles, we discussed how to include Jupyter Notebook in WordPress posts and how to provide JSFiddle like widget to run Python commands/scripts for data visualization within WordPress post at frontend. Here is How to Get Chart, Data Visualization in WordPress Posts From SQL & SQL Queries in Easy Way. SQL and SQL queries are two different topic. Solution is different. Question of SQL queries coming as a website may want some interactive mixture of both ways. It can be part of widgets, theme for the site design.
Data Visualization in WordPress Posts From SQL Queries
A list is great for entering, auditing data but data visualization is a great way to show information which matters most and also readers understand quickly. The engaging visual mechanism in our brains inherently detect patterns quite faster.
It is not very difficult like it sounds. Itself there are lot of free and paid tools for quick data analysis like DBVis, Toad, SqlWorkbench, Sql Assistant(Teradata), Team SQL etc.
---
Among free, for WordPress we have an easy to use WordPress Plugins which are limited :
1 2 | https://wordpress.org/plugins/wp-business-intelligence-lite/ https://wordpress.org/plugins/sql-chart-builder/ |
They are indeed good, light framework to extended functions on own need – it builds only charts related to WordPress installation. One is SQL queries to data visualization, another is for SQL to data visualization.
Coming to SQL to data visualization – that has lot of options. There is paid WordPress plugin :
1 | https://wpdatatables.com/ |
We are talking about self hosted options. Obviously we can use Google charts, which has plugin for WordPress :
1 | https://wordpress.org/plugins/display-sql-stats/ |
Rendering chart actually not difficult using Google Chart Tools :
See the Pen JZPzgv by Abhishek Ghosh (@abhishekghosh-the-encoder) on CodePen.
This is good as example first part :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | <?php $con = mysql_connect("blabla","blabla","passwordblabla"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("robotfriend_org", $con); $result = mysql_query("SELECT * FROM Users Order by fname ASC"); $output = array(); while($row = mysql_fetch_array($result)) { // create a temp array to hold the data $temp = array(); // add the data $temp[] = '"' . $row['created_date'] . '"'; $temp[] = '"' . $row['fname'] . '"'; $temp[] = '"' . $row['lname'] . '"'; $temp[] = '"' . $row['country'] . '"'; $temp[] = '"' . $row['gender'] . '"'; $temp[] = '"' . $row['year'] . '"'; $temp[] = '"' . $row['month'] . '"'; $temp[] = '"' . $row['day'] . '"'; $temp[] = '"' . $row['email_encrypted'] . '"'; $temp[] = '"' . $row['password'] . '"'; // implode the temp array into a comma-separated list and add to the output array $output[] = '[' . implode(', ', $temp) . ']'; } // implode the output into a comma-newline separated list and echo echo implode(",\n", $output); mysql_close($con); ?> |
This is the second part to that saved PHP :
1 | https://developers.google.com/chart/interactive/docs/php_example?csw=1 |
