MyPmWiki
Note: The recipes here are for PmWiki versions 0.6 and 1.0 only. For PmWiki 2.0 recipes, see Cookbook.
Goal
Query a MySQL database server and display the results in a wiki page.
Solution
A module called MyPmWiki that allows you to use SQL statements in a wiki page to query a MySQL database and display the results with a table.
Installation
Add this script to your "local" directory:
Include it in local.php with:
- include_once("local/mypmwiki.php");
Set these variables in local.php:
- $MyPmWiki_Hostname
- hostname of the MySQL server (default is "localhost")
- $MyPmWiki_Username
- username to access the MySQL server (see caution below)
- $MyPmWiki_Password
- password to access the MySQL server (see caution below)
- $MyPmWiki_DBName
- name of the database to use
- $MyPmWiki_TableFmt
- formatting for the table tag (default is "border='1'")
The first and last variables are optional. All others must be set for MyPmWiki to work.
Caution: Unless you want to allow anyone to modify your database, make sure that you set up a user for MyPmWiki that has ONLY select privileges. Also, you must include the unencrypted password for this user in your local.php file. This user should have very limited rights to your MySQL server.
Usage
[[$MyPmWiki SQL statement]]
Working example: [(approve links) edit diff]
Discussion
Currently MyPmWiki uses a single database model. The administrator has control over which server and database will be used as well as table formatting. With appropriate markup, it would be possible to allow the author to select the server, user, and database as well as format the result table.
Contributors
Possible improvement
I have a project at [(approve links) edit diff] and file at [(approve links) edit diff] to provide capabilities to either read or write (by automatic edition of data). A sql wiki... To edit data of a calendar for example, it will be useful. I also have a idea of a wiki translator : when a string have not translation, users can add language, add translation or enter a special mode to make corrections. A wiki to improve directly the wiki... François Elie -> mailto:francois [snail] elie [period] org
I have added some ORDER BY possibilities to MyPmWiki. It supports multiple tables per page and multiple attribute order. [(approve links) edit diff] Ralf Duckstein -> mailto:pmwiki [snail] duckstein [period] net
As I understand now, only the MySQL select statement is supported.
Is it possible, as François suggests, to also support other SQL statements, preferrably insert and alike? Paul Brandt
It will attempt to execute any SQL statement as it simply passes it mysql_query(). It it the permissions for the user specified in $MyPmWiki_Username that will determine what operations are allowed. --Profiles/JamesDavis
Are all options of the select statement implemented, including SORT?
See the answer to the previous question. --Profiles/JamesDavis pmwiki-2.3.38 -- Last modified by {{}}?