What does it do: It allows you to quickly publish the data from a MySQL database onto the web with minimal effort. Users can then browse your data, run reports or quickly search for any data item.

Security: You can restrict which tables and fields the users can see. By default the system only allows read access to these tables but this can be changed to give write permission on the data to the adminsitrator, selected users or to the public. All user access is logged by the system.

Installing: It will take around 5 minutes to go from installing the program to start seeing your data on the web. 30 minutes following the quick tutorial will allow you to customise the screens to your needs. The tool is still a work in progress and of course I'm open to any suggestions for improving it - just contact me through my home page.



The system should work with any MySQL database but I used the food nutrition database simply because it was free to download and mildly interesting. The other simple databases were set up by me and I just allowed others to add or change data. If you download the tool and get it running on your database then I'll be happy to put a link on here. I've set up the following small demos:



It should take approximately 5 minutes to install this program and start seeing your data on the web. Log on to your web server and go to the html directory. Create a new directory for the program. Download the program into this new directory. Uncompress it and then go to this new directory using your favourite browser.

cd /var/www/html		# go to your web directory
mkdir db2web			# make a new directory here
				# download db2web into this directory
tar xvzf db2web.tar		# uncompress the file
                                # view the page on your web site ie
				# http://www.your_web_site.com/db2web

You won't see your data until you enter the log in details for your database. Click save and, assuming the program can log into your database, click home to start browsing your database on the web.

Of course the data you're shown might not be from the table you want the users to start from. You might want the users to access only certain tables, search particular fields, hide fields or change the table and fields names shown to the user. To do this you need to look at the simple language provided.


Quick tutorial

Step 1/7)     Logging in

You've installed the program and are now viewing the web page. It doesn't know how to log onto your database so you are being shown the default program that the tool creates when it first starts up. You'll need to alter the LOGIN command and enter your username, password and database name. After saving the program and going to the home screen you should now see some of your data on screen.

LOGIN username password db_name localhost

Step 2/7)     Limit tables

As it stands the system doesn't know which of your tables you want to provide access to or which one you want users to start off at. On the admin screen alter the 'SET tables' command to restrict which tables are viewable. You can try any of the following methods but remember to use your own table names.

SET tables ALL                    # access to all tables
SET tables tabA                   # access to just one table called tabA
SET tables tabA, tabB, tabC       # access to these 3 tables
SET tables 'tab%'                 # access to any table starting with tab 
SET tables 'tab%','MyOtherTable'  # as above but also to MyOtherTable

Step 3/7)     The main table

We can force a particular table to be shown at the start with the TABLE command. We can also say which field is the main field to display to the user. Add the following to your program on the admin screen but remember to use your own table and field names.

TABLE YourMainTable START MAIN_COL my_main_field

Step 4/7)     Table options

The TABLE command just says what tables the following options will apply to. We can use options to hide fields and search fields etc. Try adding the following to your program on the admin screen but remember to use your own table and field names. The keyword ALL means apply the following options to all tables that the user is permitted to see.

TABLE tabA MAIN_COL 'product_name'  # product_name is now the main column in tabA
TABLE tabA,tabB MAIN_COL '%name'    # any field ending in name for tabA and tabB
TABLE ALL MAIN_COL '%name'          # any field ending in name for all tables
TABLE ALL MAIN_COL '%name', '%desc' # as above but also use fields ending in desc
TABLE ALL SEARCH '%name'            # allow searches on the fields ending in name
TABLE ALL HIDE '%_id'               # hide all fields that end in _id

TABLE ALL MAIN_COL '%name'          # do the above in one command
          SEARCH '%name'
          HIDE '%_id'

Step 5/7)     More table options

Rather than listing the records in your main table we could just pick a record at random and display it. We could also show the fields within a record vertically rather than horizontally. We could allow changes to the table using the edit option. Here are some examples.

TABLE tabB START                      # List data from table tabB
TABLE tabB START PICK                 # As above but just show 1 record
TABLE ALL VERTICAL                       # Display data vertically 
TABLE ALL EDIT ADMIN                     # Allow editing by the admin
TABLE ALL EDIT GUEST                     # Allow editing by anyone

Step 6/7)     Appearance

Finally it's easy to alter the appearance of the web page. Try the following:

SET title 'My favourite database'
SET col_bg white
SET col_tab_bg lightgrey
SET info "Thanks for reading"
TABLE tabA RENAME "My data"
FIELD product_name RENAME product

Step 7/7)     Admin button

The admin button allows you to change the program and do other admin tasks. This button will only show for admin users but you will be automatically logged out after a few hours. To become an admin user again you will need to enter your database password into the search box. You can do this from any browser so make sure your database password is not easy to guess.


The language syntax

The only command required by the program is the login command. All the rest are optional and just aim to improve the look and feel for the user. The keywords are shown in uppercase. Any value that contains a space will need to be enclosed in quotes. The ordering of commands is normally not important but makes the most sense as follows:
  • SET commands
  • TABLE commands that apply to ALL tables
  • TABLE commands for specific tables
  • FIELD commands

If field or table is mentioned then only one table or field can be entered while if tables or fields are mentioned then you can enter more than one value separated by commas. Tables and fields can also include wildcard characters so a field entry of '%_id' would cover all fields ending in '_id'. The keyword ALL implies all tables or all fields depending on where it is used.

# there are 5 basic commands:
LOGIN username pwd database_name localhost
SET variable string
TABLE list table_options
FIELD string field_options
REPORT string [ ON string ] [ START ] string

# the table options can include one or more of:
        HIDE fields
        MAIN_COL fields
        START [PICK]
        SEARCH fields 
        URL fields  
        RENAME string

# the field options can include one or more of:
        RENAME string
        VALUES list
        FORMAT string
	BETWEEN number AND number
        DEFAULT string
        LINK table field

# other definitions are as follows:
string := 'string with spaces', string_without_spaces, 123
tables := ( tabA | tabA, tabB, tabC | 'tab%' | ALL )
fields := ( fieldA | fieldA, fieldB, fieldC | 'field%' | ALL )
list := ( string | string , string , ... )


Detailed tutorial

There are only 5 types of commands so the language is quite simple to learn. The login command is the only essential command. The SET command is used to alter the settings of the tool. The TABLE command defines how tables are shown to the user. The FIELD command defines how fields are shown and what data can be input into fields. The REPORT command simply defines reports.

The LOGIN command

LOGIN username pwd database_name localhost
You'll need to change the username, password and database_name to the values for your MySQL database. By default the program only allows users to read data but it would still be preferable if you use a user name that only has select permissions on your tables. Once the program can login then you can start viewing your data on the web. Most database servers will be on the local host. In future I might add port and the option to have other types of database.


The TABLE command

The table command is followed by a list of tables and then the options that will apply to them. The list could be just a single table name, a list of comma separated table names, a wildcarded string, the keyword ALL or a combintion of these. It is best to have the table commands that apply to all tables before those commands that apply to specific tables. All the table options that follow this command will apply to these tables. Here are some examples of specifying tables :

  • TABLE 'tab%'
  • TABLE tabA,tabB,tabC
  • TABLE tabA



The 1st command tells the program that this is the main table in the database and that the entries in this table should be listed when the user first comes to the system. Only one table can be the main table. The 2nd version of the command will display just one record, chosen at random, from the main table.

TABLE MyPrices MAIN_COL stock_name


When listing the records in a table the program guesses that the primary key should be shown. This is fine if the PK is a descriptive field but hopeless if it's an id field. You can force any field to be shown with the above command. The first example makes stock_name the main field in the MyPrices table while the next example makes any field ending in name the main field in all tables. Only one field can be used per table as the show field.

TABLE MyPrices HIDE stock_id


TABLE ALL HIDE '%_id', '%code'

TABLE ALL MAIN_COL '%name' HIDE '%_id'

The first example hides the stock_id field from a table. The second example hides stock_id from all tables. The third example hides all fields ending in either '_id' or '_code' from all tables. The fourth example combines the above with another table option.

TABLE MyStocks SEARCH 'stock_name'



This option tells the program which fields it can search in. The first example will only search the stock_name field in the table MyStocks. The second example searches all fields ending name though this can be confusing for the user. The third example searches every field in every table but apart from being inefficient it would also show many false matches and confuse the user. If no search command is given then the search box won't appear at the bottom of the screen.

The vertical option displays the fields of a single record vertically down the page rather than across the page. It will also display any image field in large format next to the fields.

TABLE MyUsers URL 'user_img', user_email, 'home_page_url'

TABLE ALL URL '%_img', '%_email', '%_url'

The URL option will treat the following fields as either clickable emails, pictures or links to external web pages. If a table is set to vertical and it has a URL field containing an image then this image will be displayed alongside the fields. The first example is telling the program to treat the user_img, user_email and home_page_url fields as special links. The second example does the same for all tables.



This option allows users to edit the data in tables. The first example gives everybody the write to insert, update or delete data in the MyPrices table. The second example only gives permission to the admin user. All changes get logged.

TABLE MyPrices RENAME 'Prices'
The program will normally display the names of your tables on screen however these names can be changed to something more user friendly using the above command. It only makes sense having a single table name appearing in the table list for this option.


The FIELD command

The FIELD command is similar to the table command but obviously applies to fields rather than a tables. It gives the ability to rename fields accross the database, to define allowed values or allowed formats, and to set default values. Fields can also be linked to other tables using this command, say to link a user_id field to a table called user and a field called id. Normally only one field will be specified in the list but it is possible to have multiple fields or all fields ie:
  • FIELD top_speed, mpg, hp FORMAT NUMBER
  • FIELD engine_type VALUES 'petrol 4s', 'petrol 2s', 'diesel'
                RENAME 'Type of engine'
                DEFAULT 'petrol 4s'

FIELD price_usd RENAME 'Price (USD)'
Renames the price_usd field across all tables to Price (USD). The quotes aren't needed if the string contains no spaces.

When inserting new data via the screen a field can be given a default value using this command. In the above example the gender field will default to Male.

FIELD gender VALUES Male, Female


When entering data via the screen the user is given a choice between a set of defined values. There are a number of predefined values available which saves on typing (GENDER, TITLE, MONTH, WEEKDAY, COUNTRY). This list of predefined values will probably be expanded with each new version of the code.

FIELD prod_code FORMAT '[A-Z][0-9][0-9]'

FIELD top_speed FORMAT '[0-9]+'


FIELD month FORMAT '[A-Z][A-Z][A-Z]'

You can define particular formats for fields using this command. The string is a standard PHP regular expression. A few predefined formats are available such as NUMBER and DATE. This list of predefined values will pobably be expanded with each new version of the code.

You can define a numeric range for a field. This means that if a value is entered then it must be numeric and within the range specified.

FIELD user_id LINK users.id
The above example links all occurrences of the field user_id to the table users on the field id. Now when a table contains a user_id field then instead of the id field being shown, it will now show the name of the user and provide a link to this record.


The REPORT command

The report command allows you to build standard select statements and present them as easy to run reports. By default they can be run at any time but you can specify a field name which means the report will only be shown when the user has selected an item where this field is the primary key. The id of the current item can be passed to the SQL using the {ID}. Only one id field can be passed.

REPORT 'My Bunnies'
          'select * from Animals
          where type="Rabbit" '
You can create a special named report from standard SQL using the above syntax. The SQL will need to be enclosed in quotes and you'll need to be careful not to use the same type of quote within your SQL.

          'select * from Animals
          where type="Rabbit" '
The START option means this report will run by default when the user first enters the site or hits the home page.

REPORT 'Animal mates' ON animal_id
          'select * from AnimalFriends
          where animal_id={ID}'
Note the ON clause in this command, this means the report will only be shown when you're looking at a table where this field is the key. The {ID} string will be replaced by the id of the current record. Note if the ID is a string then you'll need quote characters around it.


The SET command

SET variable value
There are a number of settings on the system and these are always defaulted to a sensible value however you can change them to values more suited to your system using this command. You cannot create new variables as they'd be no use in a declaritive language like this. The variables used so far are :

VariableDefault valueDescription
admin_ipnoneIf set then log items for this ip are not shown
admin_emailnoneIf set then users can contact you via the contact button
txt_size14Text font size
txt_fontVERDANAText font
txt_colblacktext colour
col_bglightgreyBackground colour
col_bglightgreyBackground colour
col_buttonsyellowButton colour
col_tab_bglightgreenTable background colour
col_txtblueText colour
descriptionnoneOptional system description
img_bgnoneBackground image
img_x_size300Size of images when shown individually
img_x_size_small30Size of images when shown in list
img_y_size300Size of images when shown individually
img_y_size_small30Size of images when shown in list
infononeInformation text at the bottom of the screen
max_all_show1000How many items can be shown at one time
max_groups15Number of A-F, G-L type groups
max_linked_list12Maximum number of linked items that can be shown
max_logs1000Maximum number of log records to be held
max_radio_list4Show radio buttons rather than pull down below this number
max_recent_items5number of recent items remembered
max_show25Maximum number of individual items shown
special_pwdnonePassword for special users
tablesnoneList of tables that can be viewed
titledb2webMain title for system
url_base_dirnoneRelative URLs have this added to them to make them absoloute

The tables variable is slightly different to the other variables in that it can hold list values rather than just a string. The display variables like title, description and info can contain any html tags. The img_bg variable may contain the url of an image to be repeated across the screen however there are a number of inbuilt images you can use (ROCK, PAPER, WOOD, WATER and PLANT).


Sample programs

The initial program

The initial program is created for you when first start the system. Change the login details to access your database. You will also want to change the value of YourMainTable to the name of your main table.
# Change the login details to access your MySQL database
LOGIN username password db_name localhost

# Give your system a title
SET title 'NameForYourSystem'

# Restrict it to view certain tables
SET tables_matching ALL

# Customise which fields are shown, searched or hidden etc
         MAIN_COL '%name'

# Make one table your main starting point
TABLE YourMainTable START MAIN_COL '%name'

The spider database program

The following program is for a scooter database. It accesses a very simple database and contains references to pictures and external URLs.
LOGIN username password db_name localhost

# alter the look of the screens
SET tables 'scooter%'
SET title 'Scooter Database'
SET info "Feel free to correct data or add new scooters"
SET col_tab_bg lightgrey

# applies to all tables
      URL link, '%img%'
# applying to specific tables
TABLE scooter_links MAIN_COL comment
      START PICK  
      SEARCH '%name'
TABLE scooter_maker RENAME "Manufacturer"
TABLE scooter_countries RENAME "Countries"
TABLE scooters RENAME "Scooters"
TABLE scooter_links RENAME  "Links"

# field commands
FIELD gearing VALUES '3 speed', '4 speed' ,CVT  
      RENAME Gearing
FIELD engine_type VALUES '2 stroke','4 stroke',Electric,Hybrid 
      RENAME 'Engine type'
FIELD weight_kg, service_interval_km, from_year, top_speed_mph, 
      petrol_tank_ltr, generator_watts, price_usd, engine_cc, max_hp,
      economy_mpg, seat_height_cm, wheel_size_inch FORMAT NUMBER
FIELD carb_type VALUES Carb,EFI RENAME 'Carb type'

# a few reports
REPORT "Scooter range"
       "select name,from_year,
               cast(( economy_mpg * petrol_tank_ltr / 4.55 ) as signed ) miles,
               cast(( ((economy_mpg * petrol_tank_ltr / 4.55 ) *8 ) / 5 ) 
                   as signed ) km
        from scooters
        order by ( economy_mpg * petrol_tank_ltr ) desc"

REPORT "Economic scooters"
       "select name,from_year, economy_mpg, service_interval_km
        from scooters
        order by economy_mpg desc"

The food database program

The following program is for the nutrition database web page. It is longer than the other programs because it contains reports which are just simple SQL select statements that have been given a name.
LOGIN username password db_name localhost

SET tables 'food_%'
SET title 'Food Nutrient Database'
SET info "data taken from the USDA Nutrient Data Laboratory"

TABLE food_des RENAME Foods
TABLE food_nutr_def RENAME Nutrient
TABLE food_nut_data RENAME Nutrients
TABLE food_group RENAME 'Food Groups' 
TABLE food_group START

      MAIN_COL FdGrp_Desc, Long_Desc, NutrDesc
      HIDE ComName , Shrt_Desc , ManufacName ,Ref_desc, Survey,
           Num_Data_Pts, Std_Error, Deriv_Cd, Ref_NDB_No, Num_Studies,
           MinVal, MaxVal, Low_EB, Up_EB, Add_Nutr_Mark, Stat_cmt, CC,

FIELD Nutr_Val RENAME Amount
FIELD Nutr_No LINK food_nutr_def Nutr_No

REPORT 'High fibre cereals'
        "select f.NDB_No, f.Long_Desc, n.Nutr_Val
        from food_des f,food_nut_data n
        where f.FdGrp_Cd='0800'
              and n.NDB_No=f.NDB_No
              and n.Nutr_No=291
        order by n.Nutr_Val desc
        limit 15"

REPORT 'High alcohol drinks'
        "select f.NDB_No, f.Long_Desc, n.Nutr_Val
        from food_des f,food_nut_data n
        where f.FdGrp_Cd='1400'
              and n.NDB_No=f.NDB_No
              and n.Nutr_No=221
        order by n.Nutr_Val desc
        limit 25"

REPORT 'Foods high in this nutrient' ON Nutr_No
        "select f.NDB_No, f.Long_Desc, n.Nutr_Val,d.Units
        from food_des f,food_nut_data n, food_nutr_def d
        where f.FdGrp_Cd='1400'
              and n.NDB_No=f.NDB_No
              and n.Nutr_No={ID}
              and d.Nutr_No = n.Nutr_No
        order by n.Nutr_Val desc
        limit 25"

REPORT 'Nutrients in this food' ON NDB_No
"select d.NutrDesc,n.Nutr_No, n.Nutr_Val,d.Units
        from food_des f,food_nut_data n,food_nutr_def d
        where f.NDB_No = {ID}
              and n.NDB_No=f.NDB_No
and d.Nutr_No = n.Nutr_No
order by d.NutrDesc
limit 500"



This tool will show your data on the internet. By default it will show all tables in your database but this can be restricted to only certain tables using the SET table command. By default a user will be able to see all data within each table but this can be further restricted by hiding certain fields using the TABLE x HIDE field command. By default users can only read data but they can be given edit permission using the TABLE x EDIT GUEST command but this can be risky. Perhaps better is to give certain users a special password and then give edit access to only these special users.

SET tables 'food_%'
SET tables MyBigTab, MyOtherTab


All access is logged. All changes to data using the edit facility is also logged as a series of SQL statements. Backups can be made of just the tables you've given access to and these backups can be reloaded using the tool. The SQL log is cleared after a backup is made.

When you first create a system using this tool it will default into admin mode where you can alter settings etc. Other users will only be able to access the system as read only users with no access to the program or settings. You can logout from admin mode by selecting logout on the edit screen. You will be logged out automatically after a few hours. To regain admin mode you will need to enter your db password into the search box. For this reason you should have a password that is unlikely to be entered as a search by a user.

All the settings for the program are stored in the /tmp directory. The login details are encoded but a determined user with access to your server could retrieve these details. However if a user had direct access to your server then they could easily gain access to your database so this isn't seen as a risk.


Future plans

At some point I'd like to add:
  • Variables front_img and front_text to build a front page for the system
  • Improved search features:
    • search on multiple fields with ranges
    • Hierarchies of data in search
    • Boolean choices in search
    • bulleted items
    • Search to work on multiple words and in any order
    • Get search to work on look up tables
  • Files:
    • Put GENDER, WEEKDAYS type values into a file
    • Put help text into a file
    • Initial script set up showing login, set front_text, which tables, search on %name, title, edit
  • User logins allowing:
    • The option for restricted access so that users can only access your database after they have registered. I'll also add an option where you can approve users or that users need to belong to the same company.
    • Permisions where user can only delete their data
    • Access to recent items and perhaps comparison of these items
    • Link to view "my data"
    • store locations, language?
  • Admin functions:
    • Log system back to central point so I can set up list of systems
    • Log accesses, no of users, last update
    • Items searched for but not found
    • Check that images are present and remember when not
    • Automatically backup data
  • Hover text for fields and tables that help users enter correct data
  • A wizard to help set up your system
  • A more instructive tutorial
  • Convert between currencies, temp, lengths etc automatically
  • Provide screen layouts for users using mobile phones or just not show images etc