Page 1 of 1

Generic form handler for static data files

Posted: Fri Nov 10, 2006 5:01 am
by obiron
Guys,

I sure this must have been answered before but I can't find it.

I have a number of tables in my MySQL database that I need to maintain.

Mostly these tables are static data (VAT codes, Exchange Rates, Shipping methods etc..) and have not referential integrity to other tables (occassionaly though they will - eg. Campaign Codes are assigned to Media codes in in a 1:many relationship)

I can build a generic form builder by grabbing "Show fields from $table" and parsing the field name and data type/length to build the correct form field type and where necessary pop ups for date selectors etc.

How would you control additional validation required

Not Null is easy enough if the database has been set up correctly
How about

Must be positive value
although a string field on the form and in the database, convention says it should be (0-9) only
Value relates to another table/field and needs a SELECT OPTION drop down list (my current provider does not allow me to turn on referential integrity)

The two thoughts I have had are an XML file holding the additional valiation details or a table in the database to hold exceptions rules

Am I missing a trick that would make life easier?

Obiron

P.S. If it makes any difference I am using LAMP and will be doing lots of work with Ajax for real-time validation of data.

Posted: Fri Nov 10, 2006 3:10 pm
by feyd
Generally some will use a chosen naming convention to denote certain attributes to the field's content such as using a specific prefix and/or suffix mixed with a table name to signal a reference to the primary key of that table.

Another method I've seen is a meta-data table that contains internalized descriptions that can be parsed and handled accordingly.

"Only Positive numbers" is simple: UNSIGNED. :)

Posted: Sat Nov 11, 2006 3:32 am
by Ollie Saunders
Two options.
  • Output the contents of the table as XML, add all the necessary extra data as XML; the beauty of XML is that anything you add shouldn't interfere with what you get from the db directly.
  • Make use of the COMMENT clause and store extra information about fields there.