History: PluginSql

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

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 AdminDSN.
2. Place a SQL plugin statement in a Wiki page.

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

Version

This documentation is based on TikiWiki 1.7.5.

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

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.

First, define an external database using AdminDSN. Next, learn the plugin's syntax. Then try 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.

SQL Plugin Syntax

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}

Note the following:

• DSN name This is the name you defined using AdminDSN.
• column1, column2, etc. 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.
• 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.

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}

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

Who's working here?

• UserPagedennisdaniels
• UserPagebpfaffenberger

See AdminDSN to learn how to name and define an external DSN connection, which the SQL plugin requires.