Art Data
Art Data allows you to create 2 types of tables and 12 types of charts. Custom templates can be created to control the look of your tables and charts. Quickly populate a table or chart using our built in dataset spreadsheet or you can load a dataset from SQL Query, HTML or CSV file.
Writing SQL Queries for your Visualizations
Note: This guide only covers Art Data v2.2.0 and above. If you haven't migrated to Art Data v2.2.0 yet, please make sure to get the latest version and complete the migration. For more information about the scope of differences between Art Data v2.2.0 and previous versions please see here.
To use SQL/database query with your Art Data visualization will require knowledge of how to write a database query as well as the proper syntax for the database server that will be used with your Art Data visualization.
The following databases can be used:
- MySQL - Joomla Database
- MySQL - Other Database
- PostgreSQL
- MSSQL (mssql extension)
- MSSQL (sqlsrv extension)
- Oracle
- Access
- SQLite
- Firebird
- Informix
- Foxpro
- ADO
- Sybase
- FrontBase
- DB2
- SAP DB
- Netezza
- LDAP
- ODBC
- ODBTP
The concepts shown in this article will be illustrated using MySQL. Similar concepts can be applied to the other Databases in their respective query languages.
Aliases
SQL aliases are used to give a database table, or a column in a table, a temporary name. Basically aliases are created to make column names more readable.
This is especially important when using a database function in the SELECT clause of your query.
Consider the query below
SELECT COUNT(*) FROM `some_table` WHERE `some_column`=1
The above query will not produce the desired result in Art Data. You must alias the function in the SELECT clause with a name like the example `column_count` - see below
SELECT COUNT(*) AS `column_count` FROM `some_table` WHERE `some_column`=1
The names that are selected in the SELECT clause are used as your column headers when using Custom data source and SQL. If you use a database function in the SELECT clause without the alias your column headers will look like a javascript function call when rendered in the browser. This will result in a javascript error and your visualization will not render correctly.
SQL and Charts
When writing a query for chart display there are a few concepts to keep in mind. First, charts work with name => value pairs. For example consider the data below.
| name | age |
| John | 46 |
| Dave | 22 |
| Mary | 39 |
| Jane | 30 |
I want to create a Bar Chart that graphs the above data. How do I do it?
SELECT `name`, `age` FROM `#__peoples_ages`
It's really that simple. But it's important to think about what data you're going to select for your chart. In reality, most use cases can be more complicated than the simplified example above. Remeber that you want to show name => value pairs. This means the first column in your SELECT clause should be a text name and the second column in your SELECT clause should be an integer value. Charts are used to show the relationship of different integer values to each other and if your query doesn't produce any integer data then your chart will not work correctly. I.E. don't select a table full of columns of text data for your chart.
Other articles in this category...