Plugin SQL

This Wiki Plugin can be used to display the results of SELECT queries in a Wiki page. The plugin displays the results of the query in an attractively formatted table.

Or in other words: have you ever wished you could retrieve data that is actually in TikiWiki'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.

Please also see WikiPluginsDb & WikiPluginsDbTutorial (a different but similar feature)

And also PluginDBreport

Preamble: A Word About Security

Use of the SQL plugin can pose a risk to the security of your Tikiwiki 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! (This has actually happened in the past....)

The SQL plugin can be used to pull in data from other databases, but this capability should be used only with caution. For example, suppose you use the plugin to draw data from an Oracle database that contains information about your company's financial transactions. If you leave Wiki page containing the SQL plugin statement unprotected, an intruder might be able to deduce how to pull confidential data from that database. 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 permision 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.

Introduction

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.

Usage

To use the SQL plugin in a Wiki page, click Edit, and insert a statement that conforms to the following syntax:

{SQL(db=>''DSN name'')} SELECT column1[,column2,column3,...] FROM table{SQL}

Parameters

Param	Values	Defaults	Req ?	Explanation	Since
DSN name	name of DSN			This is the name you defined using Admin DSN	1.7.5
column1, column2, etc.	name of columns			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.	1.7.5
table	name of table			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

If you have any trouble with this see "Trouble Shooting" below

Examples

Basic examples

Here are some examples that are known to work:

{SQL(db=>tiki)} SELECT login,email FROM users_users {SQL}

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

Note: If you're used to terminating SQL queries with punctuation, note that none is needed here!

The result is a table with the select output:

Sorting and Selecting Data with the SQL Plugin

You can use ORDER BY to sort the output by a specified column name:

{SQL(db=>tiki)} SELECT authorName,title FROM tiki_articles ORDER BY authorName{SQL}

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

In tiki1.10, you have the possibilty to pass global variables 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

For more information on SQL when used with MySQL, see Select Syntax (http://www.mysql.com/doc/en/SELECT.html), which is part of MySQL's online documentation.

Note: Please post more examples of how complex SQL select statements can be successfully used with the SQL plugin.

Trouble Shooting

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 inyour DSN can actually login into your database (for mysql you could test alogin with phpmyadmin for example)

Permissions

• Ensure the tikiwiki user you are loged in as is a member of a group that has the permision to use the DSN

Wiki Syntax

• Examples of the wiki syntax for the SQL plugin vary. This may be due to formating issues such as " = > " gettting displayed as " & gt ; ".
• Here are some examples of potential syntaxs:

A {SQL(db & gt ; ''mydsn'')} SELECT * FROM mytable{SQL}
B {SQL(db & gt ; 'mqdsn')} SELECT * FROM mytable{SQL}
C {SQL(db=>'mqdsn')} SELECT * FROM mytable{SQL}
D {SQL(db=>mqdsn)} SELECT * FROM mytable{SQL}

• example D worked for me.

Related pages

• Wiki Plugins
• Mods Type Wikiplugins
• 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

---