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.
Dynamic Variable Insertion into SQL Queries
Note: This feature is only available in Art Data v2.3.2 and above. If you are on a version prior to Art Data v2.2.0, you'll have to complete the migration before you can use any version greater than v2.2.0. For more information about the scope of differences between Art Data v2.2.0 and previous versions please see here.
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.
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.
Basic Concept
Inserting variables into your queries is particularly powerful because it gives you the ability to return data based on the current user or based on other factors that may change based on the state of the user or the state of the website application.
For example, if you wanted to return data based on the currently logged in user's user id, here's how you could do it.
SELECT * FROM `some_table` WHERE `user_id`={JOOMLAUSER_ID}
Inserting User Variables
Below you'll see the variables that are available to use automatically from the currently logged in user. The "Default Value" column specifies the value that will be returned if the current user is not logged in. You don't have to do anything to set these values, they are built-in and available for use in your Art Data queries.
| Shortcode | Default Value | Description |
| {JOOMLAUSER_ID} | 0 | The currently logged in user's user id |
| {JOOMLAUSER_NAME} | NULL | The currently logged in user's name |
| {JOOMLAUSER_USERNAME} | NULL | The currently logged in user's username |
| {JOOMLAUSER_EMAIL} | NULL | The currently logged in user's email |
| {JOOMLAUSER_REGISTERDATE} | NULL | The currently logged in user's date of registration |
| {JOOMLAUSER_LASTVISITDATE} | NULL | The currently logged in user's last login date |
Inserting Session Variables
Session variables are persistent variables set in the user's session. They persist across page loads making them perfect to use for setting custom values for use in your Art Data queries.
To use session variables in Art Data, you must set them first using PHP somewhere in your website. Many users use Jumi or other custom code plugins/modules which allow them to write custom PHP code. This is how you would set your session variables.
How to set your session variables:
$session = JFactory::getSession(); //get the session object
$session->set('artdata',array('somevar'=>6)); //define the variable name for Art Data and the value that will be used if the variable is empty or not set
$session->set('somevar',10); //set your session variable
The above step is important because if you don't set the variables in PHP first, then your query will produce a 1064 SQL error on the front-end where your Art Data visualization is displayed. You must set the 'artdata' value with an associative array (as is shown above) to tell Art Data what session variable names to look for and what value to use if the variable is not found. Then set your session variable with the same name you defined in the 'artdata' value.
How to use the session variables in your Art Data queries:
SELECT * FROM `some_table` WHERE `some_column`={SESSION_somevar}
If the session variable you set is 'somevar' (as we used in the above session example) you will use {SESSION_somevar}. If the session variable you set is called groupId... you will use {SESSION_groupId}.
Other articles in this category...