Did you know that you can query your Tiki database (or any other MySQL database) from a wiki page? For example, you can execute a SQL query to count the number of objects in a specific category. This section explains how.

Creating a DSN

In order to arbitrarily access your Tiki data from a page, you must first create a Database Source Name (DSN). This is how Tiki will direct your queries to a specific database connection.

1. At the bottom of the Administration page, in the Crosslinks area, click DSN.
   
   

   Accessing the Admin: DSN page.
2. On the Admin: DSN page, use the Create/Edit area to create a new DSN for your Tiki.
   
   

   Creating a new DSN connection.
3. Enter the following information in each field:
   • Name: The name of the DSN. You will use this name when accessing the database from the wiki page.
   • DSN:The location and name of the database in the following format:
     

     database-type://user:password@host/database-name

     
     Where:
     • database-type = mysql
     • user = The database user with access to the database
     • password = The password of the database user
     • database-name = The name of the database
4. Click Save. Tiki adds your new DSN.
   Now you must specify which of your Tiki groups can use the DSN.
5. Click the Perms icon ( ) for the newly created DSN.
   
   

   The available DSN connections.
6. On the Assign permissions to this object page, select which groups should be able to execute queries on the DSN, and click Assign.
   
   

   Assigning permissions to the DSN.
   
   If you want all visitors to have access to the queries on this connection, select the Anonymous group.

Now that your DSN is fully configured, you can use the SQL plugin to execute queries against the database.
{NOTE()}For complete information about the Tiki DSN, refer to the Tiki Documentation .{NOTE}

Using the SQL Plugin

Now that you have a connection (DSN) defined, you can use the SQL query to access the database and return information to the wiki page.
{NOTE()}Refer to the Tiki Documentation for complete information on the using the SQL Plugin. For detailed information on using SQL queries, see http://www.mysql.com/.{NOTE}
{WARNING()}Using the SQL plugin presents a potential security hazard, as your are allowing users to directly access the database. You should lock the page to avoid allowing users to see the page source (which would also expose the SQL code).{WARNING}

Use the SQL Plugin to query the database:

{SQL(db=>"DSN_NAME'')} 
     ...
{SQL}

For example, to execute a query that will display the number of objects in a specific Tiki category, you might use:

{SQL(db="DSN_NAME")}
     SELECT count(*) FROM `tiki_category_objects` WHERE `categId`=5
{SQL}

Using the DBReport Plugin

The DBReport Plugin allows you to produce formatted reports from SQL queries. You can use a simple "microlanguage" to create a report definition that includes formatted headers, footers, and links.
{TIP()}The DBReport plugin requires access to your database via ADOdb. For more information, see http://adodb.sourceforge.net/.{TIP}

{DBREPORT('dsn'=>'mysql://user:password@server/database' )}
 SQL "SELECT CONCAT(CONCAT_WS('_',FirstName,MiddleNames,Surname),'@',Server) AS Email
        FROM People"
 TABLE ROW
   CELL "[Email]"
{DBREPORT}

{NOTE()}Refer to the Tiki Documentation for complete information on the using the DB Report Plugin.{NOTE}