HELP: Generate Web Forms for MySQL Database

This application can be used to generate PHP scripts, which in turn can be used as an interface to a small user database.

Currently this application handles a single table from a MySQL database, preferrably with a primary key row id column provided via the AUTO_INCREMENT facility of MySQL.

These scripts now also generate PHP code that implements INSERT, MODIFY and DELETE.

The process of generating the web form(s) for your mySQL database is a two-step procedure where you first provide some database access information and the mysqldump table definition for your table; and then in the second web form you adjust the options for your table's columns for the generator.

The second form will eventually generate the PHP code for your database. Example PHP code generated by this utility can be found at
   http://penguin.epfl.ch/PHP/example/how_to.php

Note that the generated code produces a summary page of all the data in the database. Each row of data has a link that launches another web page detailing all the columns of this row. On this page you have the option to modify or delete this database row.

  1. Fire up WebDB.html and fill in all fields. You should enter false information for passwords, etc., and then correct the generated connect.php file later.

    Note that generated files are removed from my server regularly (ie. about every 5 minutes!), but all the same it is best to avoid sending unprotected system data over the 'web'.

    Following is a brief description of each input field:

    Web Page Title
    simply, the web page title to be used for YOUR generated HTML
    Contact Information
    the URL contact information that should be inserted in the site contact address field at the bottom of YOUR generated HTML pages.
    The default is the web site email address provided by your Apache configuration, if it is configured.
    Example format: mailto:yourname.yourhost.domain
    Hostname
    The hostname where your MySQL database lives.
    Examples:

    machine.subdomain.domain
    localhost

    Database
    The name of the database where your table lives.
    Table
    The name of the table for which you are creating this web interface.
    User
    The user login name expected by MySQL for this table and this database.
    Password
    The password for this MySQL user.
    Table Description
    'Create Table' information generated by mysqldump. Cut and paste the output into this field. Comment lines will be ignored. example:
    $ mysqldump --user=root -p --no-data DB_NAME TABLE_NAME
    
    $ mysqldump --user=root -p --no-data db_how_to how_to
    #
    # Table structure for table 'how_to'
    #
    CREATE TABLE how_to (
      rowid int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
      topic varchar(30) DEFAULT '' NOT NULL,
      os varchar(12) DEFAULT 'unix' NOT NULL,
      ver varchar(10) DEFAULT '*' NOT NULL,
      arch varchar(10) DEFAULT '*' NOT NULL,
      keywords varchar(30) DEFAULT '' NOT NULL,
      notes text NOT NULL,
      PRIMARY KEY (rowid),
      KEY rowid (rowid),
      KEY topic_ind (topic(10),keywords(10))
    );
            

    When ready, you only need to click the Send Data button.

  2. The next form presents your database columns, and gives you a chance to adjust various defaults for your yet-to-be-generated PHP scripts. For each column of your table, you can adjust the following:
    Column
    This field is pre-filled with the current column name. You can change this to something else, but note that differing names will not be used in the generated PHP where the name is also used for web form script input on subsequently generated pages. If you are wondering what I am talking about, don't worry. It isn't very important. You will see the effect if you designate the same column to be 'sortable' -- the web forms will use the real column name.
    P. Key
    You must designate a primary key for your table. Only one column may be selected. The default is the first column.
    Type
    This field is currently not used, and is provided for reference only.
    Precision
    You have a chance to adjust the 'Precision' for the column. Note that this number is used by the generated web forms solely for input field widths. The database precision number is arbitrarily multiplied by 6 to provide an arbitrary field width for text input. You may adjust this number to increase or decrease output widths in your generated HTML.
    Viewable
    The generated PHP will itself generate HTML that will present all of your data in one large form. To prevent some columns of data from appearing you should make them non-viewable (for example, columns containing multiple lines of data). By default, all columns are viewable.
    Sortable
    Each column could be sorted by the generated code, but only if you mark it for sorting. By default, sorting is turned off for all columns.
    Searchable
    At the top of the generated HTML page you may have a search field. Each column that you designate as searchable is added to the pop-up menu beside the search field. By default, searching is turned off for all columns.
    Edit field
    When you select a single row from your generated database HTML code, you will be presented with all of the row data. For row insertions or modifications you need the option of specifying the HTML technique used to present this data. The three options are:
    • text --input text fields
    • select --popup window with a limited number of selections
    • textarea --a large input window for free-form text input
    Select Options
    These input fields are only used for the columns that have specified as Edit field -> select (see previous item). In order to generate a pop-up list of allowed selectable input for data, the PHP code needs to know what the legal values are. You should enter in the list as comma separated entries.

    When ready, you only need to click the GO! button.

    Here is an image of an example session.

    Here is the generated interface for the example noted above.

    Here is the same interface with a hacked-up style sheet :o)