History: PluginSQL
Source of version: 48 (current)
Copy to clipboard
! 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
{pluginmanager plugin="sql"}
{REMARKSBOX(type="note" title="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.{REMARKSBOX}
!! 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:
#Use __Admin DSN__ to define the database to be used by the SQL plugin. For instructions, please see ((Admin DSN)).
#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 [http://dev.mysql.com/doc/refman/5.0/en/select.html|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.
{PLUGINPARAMS()}
"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 | {PLUGINPARAMS}
For more information on SQL when used with MySQL, see
* [http://www.mysql.com/doc/en/SELECT.html|Select Syntax]
* [http://www.mysql.com/doc/en/SELECT.html]
!! Examples
{REMARKSBOX(type=note, title=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.{REMARKSBOX}
!!! Basic Example
''This code:''
{CODE(colors="tiki")}{SQL(db="tiki")} SELECT authorName,title FROM tiki_articles {SQL}{CODE}
''Would produce:''
^::{img src=http://tikiwiki.org/img/wiki_up//sql-plugin-unsorted-table.jpg width=430 height=219}::^
!!! 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__
{img src="display744" link="display744" width="400" rel="box[g]" imalign="center" desc="Click to expand" align="center" styleimage="border"}
''This code:''
{CODE(colors="tiki")}{BOX(bg="beige" width="384px")}There are {SQL(db="tiki" raw="1")}select count(*) from tiki_pages{SQL} total pages on this site.{BOX}{CODE}
''Would produce:''
{img src="display743" link="display743" width="400" rel="box[g]" imalign="center" desc="Click to expand" align="center" styleimage="border"}
!!!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__
{img src="display745" link="display745" width="400" rel="box[g]" imalign="center" desc="Click to expand" align="center" styleimage="border"}
''This code:''
{CODE(colors="tiki")}{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}{CODE}
''Would produce:''
{img src="display746" link="display746" width="400" rel="box[g]" imalign="center" desc="Click to expand" align="center" styleimage="border"}
-= ::With Parsing:: =-
__Wizard__
{img src="display749" link="display749" width="400" rel="box[g]" imalign="center" desc="Click to expand" align="center" styleimage="border"}
''This code:''
{CODE(colors="tiki")}{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}{CODE}
''Would produce:''
{img src="display748" link="display748" width="400" rel="box[g]" imalign="center" desc="Click to expand" align="center" styleimage="border"}
!!!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:__
{img src="display750" link="display750" width="400" rel="box[g]" imalign="center" desc="Click to expand" align="center" styleimage="border"}
''This code:''
{CODE(colors="tiki")}
||__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}||{CODE}
''Would produce:''
{img src="display751" link="display751" rel="box[g]" imalign="center" desc="Click to expand" align="center" styleimage="border"}
-=::Deluxe Wiki Table::=-
The below modification to the Wiki Code creates a Wiki Table with a summary statement at the bottom.
''This code:''
{CODE(colors="tiki")}
||__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__::||{CODE}
''Would produce:''
{img src="display753" link="display753" rel="box[g]" imalign="center" desc="Click to expand" align="center" styleimage="border"}
!!!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:''
{CODE(colors="tiki")}{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}^{CODE}
''Would produce:''
{img src="display754" link="display754" width="400" rel="box[g]" imalign="center" desc="Click to expand" align="center" styleimage="border"}
!!!! 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:
{CODE(colors="tiki")}{SQL(db="tiki")} SELECT authorName,title FROM tiki_articles ORDER BY authorName asc, title asc{SQL}{CODE}
Use WHERE to limit the output to rows that meet specified criteria:
{CODE(colors="tiki")}{SQL(db="tiki")} SELECT authorName,title FROM tiki_articles WHERE authorName='rocky'{SQL}{CODE}
In the above example, note the use of single quotes -- double quotes won't work.
Whoopee - a join:
{CODE(colors="tiki")}{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}{CODE}
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.
{CODE(colors="tiki")}{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}
{CODE}
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:
{CODE(colors="tiki")}{SQL(db="local" 0="$user" 1="5")}SELECT * from users_users WHERE login=? OR userid=?{SQL}{CODE}
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))