Loading...
 

Plugin SQL

Use this wiki plugin to display the results of database queries. The plugin displays the results of the query in a formatted table. Or in other words: have you ever wished you could retrieve data that is actually in Tiki's (or some other) database, but not organized or displayed on-screen in the way you'd like? The SQL plugin allows you to do that. If you want to create a database, please see Trackers.

Parameters

Query a MySQL database and display the results
Introduced in Tiki 1. Required parameters are in bold.
Go to the source code
Preferences required: wikiplugin_sql

Parameters Accepted Values Description Default Since
(body of plugin) The SQL query goes in the body. Example: SELECT column1, column2 FROM table
db DSN name of the database being queried. The DSN name needs to first be defined at tiki-admin_dsn.php 1
delim The delimiter to be used between data elements (sets raw=1) 11.0
audit Create a log entry containing information about the SQL call. 0 21.2
audit_csv text If set, a CSV file will be created or appended with information about the SQL call performed. 21.2
raw (blank)
0
1
Return with table formatting (default) or raw data with no table formatting 0 11.0
wikiparse (blank)
0
1
Turn wiki parsing of select results on and off (default is on) 1 11.0

 Note
If you find this plugin isn't working with version 4.0, it can likely be fixed by the site administrator adding this line at the end of the db/local.php file:
&api_tiki='adodb';
This issue is fixed in version 5.0 so that this adjustment isn't necessary.

Troubleshooting

DSN

  • Check your DSN exists (see Admin DSN)
  • Check your DSN has the right syntax, naming the right user, password, host and database.
  • Check the user in your DSN can actually login into your database (for mysql you could test a login with phpmyadmin for example)

Permissions

  • Ensure the Tiki user you are logged in as is a member of a group that has the permission to use the DSN

A Word About Security

Use of the SQL plugin can pose a risk to the security of your Tiki site. If you use the SQL plugin on a Wiki page and give users permission to access the page, be sure to lock the page avoid users to see the source of that page. If you don't, users with appropriate permission could see the SQL code — and might decide to do a little "experimentation" to see what else they can pull out of the database!

The SQL plugin can be used to pull in data from other databases, but this capability should be used only with caution. If you want to use the SQL plugin to access confidential or sensitive data, do so ONLY in the context of a well-secured intranet that is not accessible to external users.

To enforce security any user viewing a page that uses the PLuginSQL will need the permission tiki_p_dsn_dsnname for the PluginSQL to execute where dsnname is the name of the dsn. For instance a dsn localhost , will need a perm tiki_p_dsn_localhost . Further information can be found at Admin DSN and in the Troubleshooting section below.

How to use the plugin

To use the SQL plugin, you must do the following:

  1. Use Admin DSN to define the database to be used by the SQL plugin. For instructions, please see Admin DSN.
  2. Place a SQL plugin statement in a Wiki page.
    Start by trying a simple query — and experiment!


The following sections detail the plugin syntax, discuss some simple queries, and illustrate some more complex (and useful) queries you can try.

Remember, though, that this page tells you how to insert the SQL statement in a Wiki page, but it won't work until you've defined a DSN.

SQL

This table represents some helpful information about the structure of an SQL "SELECT" statement. Detailed information regarding MySQL specific statements can be found on the MySQL documentation site. Much of the MySQL syntax is transferrable to other databases, but database specific documentation should be referenced to take full advantage of database nuances.

Param Values Defaults Req ? Explanation Since
"Select" Just the word "select" None Y SQL command to retrieve data from database SQL 1.0
column1, column2, etc. names of columns None Y The names of the columns from which you want to retrieve data. These must be typed exactly as they are stored in your database program. Tip: To make sure you spell the column names correctly, take a look at your database's structure with a utility such as PHPMyAdmin. SQL 1.0
from Just the word "from" None Y Descriptor used to indicate the next parameter is the name of of an data entity, usually a table, but also maybe a view SQL 1.0
table name of table None Y The name of the table from which you want to retrieve the data. Again, the name must be typed exactly as it is stored in the database
Unable to load the jQuery Sortable Tables feature.


For more information on SQL when used with MySQL, see

Examples

 Note
Though you may be accustomed to terminating SQL queries with punctuation such as a semi-colon (;), none is needed with the SQL statement within an SQL plugin.

Basic Example

This code:

Copy to clipboard
{SQL(db="tiki")} SELECT authorName,title FROM tiki_articles {SQL}


Would produce:

Image

Raw Parameter Example

The raw parameter requests that returned data be presented with no headers or table formatting at all. This is useful for creating in-line queries. As illustrated in the below example:

Wizard

Click to expand
Click to expand


This code:

Copy to clipboard
{BOX(bg="beige" width="384px")}There are {SQL(db="tiki" raw="1")}select count(*) from tiki_pages{SQL} total pages on this site.{BOX}


Would produce:

Click to expand
Click to expand

Wiki Parse Flag Example

The "Wiki Parse Flag" parameter enables the plugin implementor to determine if the returned data should be Wiki parsed. An example for how this can be handy is if someone had a list of servers they were maintaining and were to select server information out of the database with the hostname bracketed in parens to make the hostname automatically become a link. In this case a location available to anyone with access is automatically created for keeping special notes as related to each server.
In the below case Wiki pages are select from the Tiki site with the page reference being bracketed to become a live link. Column headers are also made bold.

Without parsing

Wizard

Click to expand
Click to expand


This code:

Copy to clipboard
{SQL(db="tiki" wikiparse="0")}select page_id as '__Page Id__', concat('((',pageName,'))') as '__Page Name__',hits as '__Hits__', version as '__Version__' from tiki_pages order by 1 limit 5{SQL}


Would produce:

Click to expand
Click to expand

With Parsing

Wizard

Click to expand
Click to expand


This code:

Copy to clipboard
{SQL(db="tiki" wikiparse="1")}select page_id as '__Page Id__', concat('((',pageName,'))') as '__Page Name__',hits as '__Hits__', version as '__Version__' from tiki_pages order by 1 limit 5{SQL}


Would produce:

Click to expand
Click to expand

Delim Parameter Examples

The "Delim" parameter allows for the delimiter to be defined when "Raw" is used. Setting "Raw" is optional and assumed when a delimiter is defined.

Straight Wiki Table

The delim parameter can be used to create a straight wiki table instead of relying on the default table created by the SQL plugin itself. This is useful for maintaining a consistent look if regular wiki tables are also used in other areas of the same page. This example combines to queries to create one table.

Wizard:

Click to expand
Click to expand


This code:

Copy to clipboard
||__PID__|__Page Name__|__Hits__|__Version__ {SQL(db="tiki" raw="1" delim="|" wikiparse="1")}select page_id as 'Page Id', concat('((',pageName,'))') as 'Page Name', Hits,Version from tiki_pages order by 1 limit 5{SQL}||


Would produce:

Click to expand
Click to expand

Deluxe Wiki Table

The below modification to the Wiki Code creates a Wiki Table with a summary statement at the bottom.

This code:

Copy to clipboard
||__PID__|__Page Name__|__Hits__|__Version__ {SQL(db="tiki" raw="1" delim="|" wikiparse="1")}select page_id as 'Page Id', concat('((',pageName,'))') as 'Page Name', Hits,Version from tiki_pages order by 1 limit 5{SQL}::__FIRST 5 of {SQL(db="tiki" raw="1")}select count(*) from tiki_pages{SQL} SITE PAGES__::||


Would produce:

Click to expand
Click to expand

CSV C&P

The delim parameter is useful for providing a CSV format for use in a poor man's export through cut and paste.

This code:

Copy to clipboard
{SQL(db="tiki" wikiparse="1")}select page_id as 'Page Id', concat('((',pageName,'))') as 'Page Name', Hits, Version from tiki_pages order by 1 limit 5{SQL} __C&P for spreadsheet import use__ ^{SQL(db="tiki" raw="0" delim="," wikiparse="0")}select page_id ,pageName, Hits, Version from tiki_pages order by 1 limit 5{SQL}^


Would produce:

Click to expand
Click to expand

Sorting and Selecting Data with the SQL Plugin

You can use ORDER BY to sort the output by a specified column name:
You do not have the permission that is needed to use this feature

Secondary sort:

Copy to clipboard
{SQL(db="tiki")} SELECT authorName,title FROM tiki_articles ORDER BY authorName asc, title asc{SQL}


Use WHERE to limit the output to rows that meet specified criteria:

Copy to clipboard
{SQL(db="tiki")} SELECT authorName,title FROM tiki_articles WHERE authorName='rocky'{SQL}


In the above example, note the use of single quotes — double quotes won't work.

Whoopee - a join:

Copy to clipboard
{SQL(db="stshome")} SELECT t1.name, t2.value FROM tiki_tracker_fields AS t1, tiki_tracker_item_fields AS t2 WHERE t2.itemId = '5' AND t1.fieldId=t2.fieldId{SQL}


An example to retrieve in a single table all the distinct users assigned to every group. This is useful, for instance, in the education area where a teacher appreciates having a list of all the students succesfully registered and assigned to his/her class/subject.

Copy to clipboard
{SQL(db="db_uniwiki2")} SELECT DISTINCT `users_usergroups`.`groupName`,`users_users`.`login`, `users_users`.`email`,`users_users`.`default_group` FROM `users_usergroups` , `users_users` WHERE `users_users`.`userId` = `users_usergroups`.`userId` ORDER BY `users_usergroups`.`groupName` DESC,`users_users`.`login` ASC{SQL}


Note in the example above, that other columns are included in the table, such as their e-mail, default group, ... Note, also, that they are sorted first by group name in descending order ( DESC ), and then, by login (user name in Tiki) in ascendent order ( ASC ).

Global variables can also be used as the username to the query:

Copy to clipboard
{SQL(db="local" 0="$user" 1="5")}SELECT * from users_users WHERE login=? OR userid=?{SQL}

where 0 is for the first ? and 1 for the second

List Slides