Table of Contents
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
- Access to a MySQL database
- A web server with the PEAR HTML_Table package installed.
- 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.
- 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.
- Create a password protected user account that is given access to only your user accessible database.
- Restrict the privileges of this user account to only those needed. The only privilege needed for this tool is Data Select privilege.
- 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
- sample_data_tables.sql to fill in the data tables, and
- 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
- Import twoTablesNeeded.sql. This will create the following empty tables.
- a table of tables in your database
- a table of sources of data for those tables
- Create or import your data tables.
- 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.
- Update the imported source table. Some fields are not required. See Fields of source table for how to fill out the fields.
- Edit the structure of your data tables to add directives for this tool in the Comment fields.
- All directives start with #db_
- Directives ending with a colon (:) are followed by a parameter(s)
- Parameters and directives are parsed as space delimited tokens.
- Any text not identified by the #db_ as a directive will be used as an actual comment for describing the field.
- Directives and comments can be in any order
- 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:
- Create a mysite folder into your highest level localhost folder.
- Copy the software release into the mysite folder
- If not already done, perform the database setup listed in Database Setup
- Update your database visitor username and database name in /mysite/protectedIncludes/opendb.php
- Update your database visitor password in /mysite/otherProtectedArea/arcaneName.php
- 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
- 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.