• View
• Edit
• History
• Attach
• Print
• Backlinks

MyPmWiki

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

Example

 (:MyPmWiki SELECT COUNT(*) as Number FROM devtable WHERE ACTION LIKE LOWER('%ipp%'):)

Working example:

Changelog

• 2005-09-28
  • changed Markup from _begin to inline

See also:

I've just contributed two new recipes that IMHO are safer and more functional than this one: SelectQuery and UpdateForm. Ben Stallings

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.

This is my first pmwiki addition. I adopted the script from pmwiki-1. There are a lot of expansion possible, it is only a hack!

This is a very nice extension, however the display look causes an error when the SQL query returns no result. philippe? January 11, 2005, at 16:18 AM

I've modified the code a bit to have different background color for odd rows

    $resultStr = "<table $MyPmWiki_TableFmt style=\"border-collapse: collapse;\"><tr>";
    for ($i = 0; $i < mysql_num_fields($result); $i++) {
      $resultStr .= "<th>".mysql_field_name($result, $i)."</th>";
    }
    $resultStr .= "</tr>";
    $x = 1;
    while($row = mysql_fetch_row($result)) {
      $resultStr .= "<tr>";
      if($x%2): $color = "#dee"; else: $color = "#eed"; endif;
      foreach ($row as $field) {
        $resultStr .= "<td style=\"background-color: ".$color."\">$field</td>";	
      }
      $x++;      
      $resultStr .= "</tr>";
    }
    $resultStr .= "</table>";
  }

(luca marletta)

    I added variables in Markup that override defaults in local/config.php.
    I.e., if these variables are not specified in the directive they will
    default to the variables defined in local/config.php for MyPmWiki.
    This allows you to use multiple databases and table formats in one pmwiki
    site.
    The variables are the same as the ones above but in lowercase and without
    the MyPmWiki part so they are:
    hostname
    username
    password
    dbname
    tablefmt
    sql (new variable, required due to the need to quote the sql string.  This
    may be not strictly necessary (one could join the array of all strings not
    in other variable assignments), but in my opinion it is not a burden to use
    the new variable.)
    Example usage would be:

    (:MyPmWiki hostname="localhost" dbname="pmwiki" sql="select * from mytable where name='wiki';":)

    The code is hereΔ.

    - Paul Eden

18 Nov 2005

This is a helpful script.

• I think that the two instances of "local.php" above should read "local/config.php".
• Here is the table format I use. It helps make the table more readable.

       $MyPmWiki_TableFmt = 'border="1" cellpadding="6" frame="box" rules="rows"
         style="border-collapse: collapse; font-family:Times New Roman; font-size:10pt"';

• More information about the MySQL SELECT statement is available here.

-- Mike

Contributors

JamesDavis, fraz, Mike