From: Philip Mak <pmak@...>
Date: 2002-08-12T16:40:26+09:00
Subject: Handling forms on database driven websites

Ever since I learned Perl, Ruby and MySQL, I've built several database
driven websites. Again and again, I have coded HTML forms, and Perl or
Ruby code to process the data submitted from those HTML forms and
insert them into the MySQL database.

I've always felt that the process is overly tedious, and looked for
ways to improve it. For example, suppose I have a table in my database
that contains a person's name, e-mail address, and phone number. My
first attempt at writing a script to process the form would look like
this:

name = $cgi['name'][0].to_s
email = $cgi['email'][0].to_s
phone = $cgi['phone'][0].to_s
if name.length == 0
    puts "ERROR: Name cannot be blank. Please press BACK."
    exit
end
unless email =~ /^\w+@\w+\.\w+$/
    puts "ERROR: E-mail appears to be invalid. Please press BACK."
    exit
end
unless phone =~ /^\d\d\d-\d\d\d-\d\d\d\d$/
    puts "ERROR: Phone must be in ###-###-#### format. Please press BACK."
    exit
end
# Everything checks out ok
mysql.query("INSERT INTO persons SET name='#{Mysql.quote(name)}', email='#{Mysql.quote(email)}', phone='#{Mysql.quote(phone)}'")
puts "Submission successful!"

After gaining more experience, my current approach is to have an
object-oriented Form object that performs HTML generation of a form
and validation of the submitted form for me, as well as actually
redisplaying the form with the previous values filled in, along with
error messages on the offending fields rather than just asking the
user to press BACK:

class PersonForm < Form
    @@fields = {
        # key => [label, type, extra arguments]
        'name' => ['Name', Forms::Text, {'maxlength' => 20}],
        'email' => ['E-mail Address', Forms::Email],
        'phone' => ['Phone Number', Forms::Phone]
    }
end
form = PersonForm.new
form.values = $cgi.params
form.validate
if form.valid?
    Db.insert_hash('persons', form.values)
    Response.Redirect('person.rhtml', {'id' => Db.insert_id()})
else
    puts "<p>There were errors in your form. Please fix them.</p>"
    form.display
end

which looks considerably nicer, but still feels too tedious when I'm
building a website that has many of these forms to make. I also find
myself having to duplicate my data definitions; Ruby sees this:

        'name' => ['Name', Forms::Text, {'maxlength' => 20}],
        'email' => ['E-mail Address', Forms::Email],
        'phone' => ['Phone Number', Forms::Phone]

And the MySQL sees another data definition for the same data:

        name        VARCHAR(20)
        email       VARCHAR(80)
        phone       DECIMAL(10, 0)

So, I am still in search of the perfect, minimal-effort way to
generate HTML for, and process the input of forms on a database-driven
website.

Does anyone have any pointers to offer?

P.S. I've heard people here say that PostgreSQL is better than MySQL.
How would it help in my situation?