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

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.

For more information on SQL when used with MySQL, see

• Select Syntax
• http://www.mysql.com/doc/en/SELECT.html

Examples

Basic Example

This code:

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

Would produce:

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

This code:

{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

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.

Wizard

Click to expand

This code:

{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

Wizard

Click to expand

This code:

{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

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.

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

This code:

||__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

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

This code:

||__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

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:

{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

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:

{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:

{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:

{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.

{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:

{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

Related pages

• See Admin DSN to learn how to name and define an external DSN connection, which the SQL plugin requires.
• http://tikiwiki.org/WikiPluginsDb
• http://tikiwiki.org/WikiPluginsDbTutorial
• PluginDBreport