DokuWiki

It's better when it's simple

User Tools

Site Tools


Sidebar

Translations of this page?:

Learn about DokuWiki

Advanced Use

Corporate Use

Our Community


Follow us on Facebook, Twitter and other social networks.

tips:mysql-for-all

MySql for All

Mysql-for-all provides a user friendly interface to generate a mysql query. It is useful for users that do not have visibility into a database structure, do not know mysql syntax, or that want a quick query without having to check syntax or database names.

The software was created to let wiki users generate queries that can be used on wiki plugins that use mysql queries. It is written to run on a webpage outside of the wiki.

The software can be downloaded at mysql-for-all on Github

Requirements

  1. Access to a MySQL database
  2. A web server with the PEAR HTML_Table package installed.
  3. Hooks into your database. The installation will guide you on entering comments in your database fields and creating two tables of database information.

Security

When providing user generated database information on a wiki some security precautions should be followed. This section provides suggestions of some steps that can be taken. They are not required but are recommended. This section is written towards users of the phpMyAdmin tool.

  1. Put user accessible data for wiki display in a separate database that contains only such data. Do not provide access to a database that contains data you do not want exposed such as user information or site internals.
  2. Create a password protected user account that is given access to only your user accessible database.
  3. Restrict the privileges of this user account to only those needed. The only privilege needed for this tool is Data Select privilege.
  4. This software tool separated out the database login code and password. The intent is to place those pieces in folders with more restricted access than your site's general php code area. You can keep all together if you desire.

Database Setup

Create a database for user generated content. See Security section for setup recommendations.

Sample data tables of US waste and mortality data is provided. After you have created the database follow either Using the Sample Data or Using My Own Data to populate the database.

Using the Sample Data

If using the sample data then import the following sql scripts into your database

  1. sample_data_tables.sql to fill in the data tables, and
  2. sample_data_info to create and fill in the two tables of information.

This will complete the necessary database setup and you can proceed to the section on installing the software. After seeing it work with the sample data, I recommend reading the next section and examining the imported tables to learn how to setup for your data.

Using My Own Data

If using your own data then you need to perform the following steps

  1. Import twoTablesNeeded.sql. This will create the following empty tables.
    1. a table of tables in your database
    2. a table of sources of data for those tables
  2. Create or import your data tables.
  3. Update the imported table of tables for each table in your database. Some fields are not required. See Fields of tables table to see what data is required and what goes in the fields.
  4. Update the imported source table. Some fields are not required. See Fields of source table for how to fill out the fields.
  5. Edit the structure of your data tables to add directives for this tool in the Comment fields.
    1. All directives start with #db_
    2. Directives ending with a colon (:) are followed by a parameter(s)
    3. Parameters and directives are parsed as space delimited tokens.
    4. Any text not identified by the #db_ as a directive will be used as an actual comment for describing the field.
    5. Directives and comments can be in any order
    6. See Comment Directives for a list of directives.

Database Fields and Directives

This section is simply the tables referred to in the previous section

Comment Directives
Directive Description Parameter
#db_Relation: this field links to another field table field
#db_NoDisplay Do not display this field
#db_Filter User can filter on this field
#db_Default: Default value for the Filter value

The Relation directive requires two parameters of a table name and a field for the linked field. A field could have multiple Relation directives.

Fields of tables table
Field Description Required
name table name Yes
brief brief description of the table Yes
description a full description of the table No
date latest date of the data in the table No
source id of the corresponding source record in source table Yes
author user that submitted the table data to your website No
key key field of the table No
helper 1=helper table, 0=main table Yes

A helper table supports a main table and would not be displayed in the initial list of tables for the user to choose.

Fields of source table
Field Description Required
name Source Name Yes
short_name Source abbreviation No
website Data source website url No
title Data title No
pub_date publication date No
data_date Date of data No

Install Software

To run the software on a localhost such as XAMPP:

  1. Create a mysite folder into your highest level localhost folder.
  2. Copy the software release into the mysite folder
  3. If not already done, perform the database setup listed in Database Setup
  4. Update your database visitor username and database name in /mysite/protectedIncludes/opendb.php
  5. Update your database visitor password in /mysite/otherProtectedArea/arcaneName.php
  6. Enter in your browser the address http://localhost/mysite/php/getQuery.php

The steps to running the software on your actual website are roughly the same except that you will need to integrate the /mysite/php code onto a webpage of your site. On my site I have integrated it on a page outside the wiki. I felt that was a more standard (ie: easier) integration and since it is not a dokuwiki plugin was not needed to be on the wiki. If your site is only a dokuwiki implementation there is material on running php from the wiki on the dokuwiki website. However I have not done so.

ToDo

  1. Extract Source Table from core. For some wiki database data the source table would not be needed. I will look at removing it from the core of this tool and provide it like an option or add-on.
tips/mysql-for-all.txt · Last modified: 2012-05-12 04:36 by tom_c