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:

::Attach:mypmwiki.php

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.2.138 -- Last modified by {{}}?

from IP: 85.171.160.186 ip should be disabled by default for security reasons