Quick Links:
You've got your data in a database finally and now you've found that managing the data (modifying, deleting and adding to it) has been pretty tedious. Entering SQL commands through a terminal session requires people with some understanding of databases. SQLView helped in some cases, but it shows all the columns and rows to the user and sometimes there is data the user should not see or the user should not be changing.
What you really want is a nice HTML <form> which shows some (or all) columns as TEXT input areas, drop downs, radio boxes etc. and the user can see/modify only those parts of the database you want. Creating such HTML forms is tedious. Small changes to the database must be coordinated with changes in the HTML. Even worse, you must write a CGI script to act on the form taking the various fields and updating the database. Isn't there a better way?
FormEntry will build an HTML form for your SQL tables allowing users to maintain some or all of the content through familar HTML forms and yet you don't need to bother to create the HTML files. FormEntry is available at SourceForge.
FormEntry will generate the form directly from the tables and control the behavior and access of each column of data - not showing some, allowing only read-only viewing for others, and allowing full modification for others. Lastly, FormEntry provides the CGI scripts to deal with the action of the <form>, so you don't need to write them.
Sounds just like what I want - how's it work?
Pick most any table (as long as it has a primary key) with data for which you want to generate an HTML <form>. For example:
CREATE TABLE formentry_cars ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, category VARCHAR(20) NOT NULL, /* Select Sports Car, SUV, Compact, Sedan, Truck */ manufacturer VARCHAR(20) NOT NULL, /* Text string */ model VARCHAR(20) NOT NULL, /* Text string, varies by manufacturer */ color VARCHAR(6), /* Select blue, red, yellow, pink, white, green */ topspeed INT, /* Integer minimum comparison */ state VARCHAR(2), /* Select MI, CA, KY */ owner_name VARCHAR(32), /* Text string */ engine_cc INT, /* Integer maximum comparison */ no_seats INT, /* Integer range comparison */ cost INT, /* 10000..20000 */ extracol int, /* Column is not described in a definition */ purchasedate VARCHAR(10), /* YYYY-MM-DD, YYYY-MM, YYYY */ purchasetime VARCHAR(8), /* HHMM, HHMMSS or with colons*/ stopdist FLOAT(5,2), /* Float maximum comparison */ height FLOAT(5,2), /* Float minimum comparison */ car_length FLOAT(5,2), /* Float range comparison */ satfact INT /* Conditional IF clause comparison */ ); |
Looking at this, you can pretty quickly work out how most columns should be presented to the user. You don't want the primary key id to be visible, but you know it'll be necessary so the CGI script can UPDATE the database. Some fields are obviously TEXT input fields (e.g. owner_name), but many of them are clearly candidates for drop down SELECT lists (e.g. color, state and others). While we're wishing for it, we want the input verified - dates should be valid dates, numbers should be reasonable.
Yup - that's exactly what FormEntry was designed for.
There are two steps - (1) create a project and (2) generate the end user <form>. Each project has a definition table which controls what FormEntry presents to the user. Our first task is to create a project and set up the definition table.
We start the browser at formentry/admin/projects.php which gives us a list of all predefined 'projects'. Projects are defined manually when you create a file (more on that elsewhere) whose name in this case is 'cars_config.php'. This fragment of PHP provides details which are particular to this project - database connection details, customizations on the look at feel, etc. Usually these are only a few lines and can be created in a minute or two with your favorite editor.
Once the project PHP file is created, the name (e.g. cars) will show in the 'Select a Project' list. The first time you select the project, you get an error that the definition table does not exist - and a suggestion to select one of these choices:
Using 'Create Definition Table' creates the table and then tells you the table has no data and suggests you select 'Populate Definitions'. Doing this reads all the columns in data table (e.g. cars, or whatever your project file defined it to be) and assigns defaults. Now you tailor what the <form> is going to look like. Be sure to assign the primary key by editing the id column. As soon as the primary key column is set, selecting 'Show Generated Form' will give you a fully functional form which will allow a user to modify data in the table.
You only need go through this process the first time you create a project. Once a project has been created, then it's just a matter of making changes to the project definition.
Continuing to edit the other columns allows you to set up drop-down SELECT lists and even add verification of the input (more on editing elsewhere). The process continues iteratively. Work on the defintion and view the <form> that it generates until you are content with the result.
Since we can already see the form the user will get, you know we have a URL we can give the user and let them at their data. The URL you provide your user will simply be:
/formenty/form.php?project=PROJECTNAME |
This will cause FormEntry to display the form for the project specified. Use this URL in links you provide to your users.
As the user begins to work with the form, you may get requests for changes - add this to the drop down, move topspeed after engine_cc etc. "No problem", you cheefully respond. Back to FormEntry, choose the 'cars' project, edit the columns and make the changes - all done! "Thank you so much", your user says, backing away in stunned awe at your responsiveness.
FormEntry is not designed to replace all possible database and HTML applications. It is specifically aimed for data entry scenarios, where a set of users are interested in seeing and modifying parts or all columns/rows of a table. There is great flexibility here, but FormEntry will never create web elaborate web applications like 'My Favorite Movie Site'.
Having said that, 'My Favorite Movie Site' probably has a database that may require vast amounts of data input that is done behind the scenes. FormEntry would allow your movie experts to browse the database and keep it in sync.
We assume your user is interested in changing a table (or parts thereof) - so FormEntry starts with a search page. The user is given a URL that invokes FormEntry, specifying the project and sees a page something like the one above (e.g. /formenty/form.php?project=PROJECTNAME) The definition may specify one or many search fields. Selecting 'Search' generates an SQL query and something like that shown below.
The user may modify ('Edit') or even delete the record shown. The query only returns a limited number of rows (controlled by the project file). This example used drop-down lists, since the columns defined as 'searchable' in the project definition table were all of that type. If the searchable field were a conventional TEXT input field, the user could enter any part of the string and an SQL glob search (e.g. '%string%') is used. The intention is to get the user as quickly as possible near the row being searched for.
The page to the right demonstrates most of the variety you can define with a project definition. These are discussed in detail elsewhere. Note that we make heavy use of Javascript.
The links on the left hand side (e.g. 'Catgeory') are javascript popup windows to provide additional help text you defined in the definition. There are labels like 'Enter Vehicle Information', subtitles ('Owner Profile') and comments for each field, among other types of columns you may define as described elsewhere.
Clicking on 'Save Data' starts a rather substantial amount of processing. Many of the fields use Javascript to check the <form> before the data is ever submitted to the web site. For instance, the field height requires a number that is >= 36.6. An invalid value results in an error message in a popup window and the data is not saved. There is a large set of predefined conditions that can be specified. In addition to the Javascript checks, there are PHP "triggers" which can be executed at the web server - PHP functions you provide to do whatever checking you want. Finally, if your database supports them, you can have database triggers defined on a field to do further checking.
Adding a record brings up a page very much like the Edit page and invokes all of the same checks. If authotized, deleting a record provides a prompt before taking the action.
FormEntry reads a local PHP configuration file (named local_config.php) which sets a number of variables:
More complexity and control is provided by projects which are 'named' configurations which allow one installation of the application to provide many different views of the data. Read the details on configuration here
Quick Links:
Direct questions to:
Terry Gliedt tpg@hps.com
(Include 'Formentry' in the subject line so my SPAM filter lets your message through.)
This is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published
by the Free Software Foundation.
See
http://www.gnu.org/copyleft/gpl.html