List:General Discussion« Previous MessageNext Message »
From:Thimble Smith Date:March 12 1999 5:56pm
Subject:Re: field types needed from mysql database
View as plain text  
On Fri, Mar 12, 1999 at 11:25:41AM +0100, Jiri Cuypers wrote:
> I'm writing a dynamic form handler, which dumps the form data into the
> correct fields of a database.

Jiri, I wrote something similar.  This was one of my first DBI programs
so it might not be that great, but it might give you some ideas.  It
doesn't know about all the different SQL types - you might have to
add some parsing/validation checks for different types.

Tim


#!/usr/local/bin/perl -w

use strict;

use CGI::Carp;
use CGI qw/:cgi/;

use DBI;

my $dbname = 'database'; # param('dbname') will override this
my ($dbuser, $dbpasswd) = ('user', 'password');

$| = 1;

print header();

if (param('dbname')) { $dbname = param('dbname') }
my $table = param('table');

# connect to the database
my $dbh = DBI->connect("DBI:mysql:$dbname", $dbuser, $dbpasswd)
    or die "database connect failed: $!\n";

print <<"EOS";
<html>
<head>
<title>database explorer: $dbname</title>
</head>

<body>
EOS


my @table;
my %table;

if ($table) {
    my $sth = $dbh->prepare("DESCRIBE $table");
    if ($sth and $sth->execute()) {
        while (my ($name, $type, $null, $key, $default, $extra)
                    = $sth->fetchrow())
        {
            for ($type) {
                /^(?:tiny|small|medium|big)?int(?:eger)?\((\d+)\)/ and do {
                    # integer field

                    my $size = $1;
                    push @table, [
                        $name,  # column name
                        'integer',      # column classification
                        $size,          # size
                        $type,  # MySQL column type
                        $key,   # KEY
                        $extra =~ /auto_increment/ ? 1 : 0,
                    ];
                    $table{$name} = $#table;

                    last;
                };

                /^(?:var)?char\((\d+)\)/ and do {
                    # character field

                    my $size = $1;
                    push @table, [
                        $name,  # column name
                        'character',    # column classification
                        $size,          # size
                        $type,  # MySQL column type
                        $key,   # KEY
                        $extra =~ /auto_increment/ ? 1 : 0,
                    ];
                    $table{$name} = $#table;

                    last;
                };

                /^(?:tiny|medium|long)?text/ and do {
                    # text field

                    push @table, [
                        $name,  # column name
                        'text', # column classification
                        0,      # size
                        $type,  # MySQL column type
                        $key,   # KEY
                        $extra =~ /auto_increment/ ? 1 : 0,
                    ];
                    $table{$name} = $#table;

                    last;
                };

                /^date$/ and do {
                    # date field

                    push @table, [
                        $name,  # column name
                        'date', # column classification
                        14,     # size
                        $type,  # MySQL column type
                        $key,   # KEY
                        $extra =~ /auto_increment/ ? 1 : 0,
                    ];
                    $table{$name} = $#table;

                    last;
                };

                /^enum\((.*)\)/ and do {
                    my $values = $1;
                    $values =~ s/^'//;
                    $values =~ s/'$//;
                    my @values = split /','/, $values;

                    push @table, [
                        $name,  # column name
                        'enum',         # column classification
                        [@values],      # enumeration values
                        $type,  # MySQL column type
                        $key,   # KEY
                        $extra =~ /auto_increment/ ? 1 : 0,
                    ];
                    $table{$name} = $#table;

                    last;
                };

                # default
                print "UNKNOWN field type: $_<br>\n";
            }
        }
    }
    else {
        print "<big>can't run DESCRIBE $table query: ",
            $dbh->errstr(), "</big>\n";

        goto BOTTOM;
    }
}

my @cells;
my $error = 0;

if (param('cells_passed')) {
    unless (@table) {
        print "program error: no table information for $table\n";

        goto BOTTOM;
    }

    foreach my $field (@table) {
        my ($name, $type, $size) = @$field;

        unless (defined param("field_$name") or $type eq 'enum') {
            ++$error;
            print "Error: no information passed for field ``$name''<br>\n";
            next;
        }

        my $value = param("field_$name");

        next unless defined $value and $value =~ /\S/;


        if ($type eq 'integer') {
            if ($value !~ /^\d+$/) {
                ++$error;
                print "Error: field ``$name'' takes an integer,",
                    " not ``$value''<br>\n";
                next;
            }

            push @cells, [$name => $value];
        }
        elsif ($type eq 'character' or $type eq 'enum' or $type eq 'date') {
            push @cells, [$name => $value];
        }
        elsif ($type eq 'text') {
            $value =~ s/\r\n/\n/g;
            push @cells, [$name => $value];
        }
        else {
            ++$error;
            print "Error: unknown field type ``$type''<br>\n";
            next;
        }
    }

    #print "Cells:<br>\n", map({"$_->[0] == $_->[1]<br>\n"} @cells),
"\n";
}

if (param('do_query')) {
    if ($error) {
        print "\n<p>\nErrors prevent your query from being performed.\n";
    }
    else {
        my $query = "SELECT * FROM $table";
        my @restrictions = map {
            "$_->[0] = " .
            ($table[$table{$_->[0]}][1] eq 'integer'
            ?
            $_->[1]
            :
            $dbh->quote($_->[1]))
        } @cells;
        if (@restrictions) {
            $query .= "\nWHERE " . join("\n    AND ", @restrictions);
        }
        print "Your query
is:<br>\n<pre><b>$query</b>;</pre>\n";

        my $sth = $dbh->prepare($query);
        if ($sth and $sth->execute()) {
            print "<table border=0 bgcolor=#ff9900>\n";
            print "<tr>\n", map({"<th>$_->[0]</th>"} @table),
"\n</tr>\n";

            while (my @row = $sth->fetchrow()) {
                print "<tr>\n";
                foreach (@row) {
                    print "<td>";
                    if (/\S/) {
                        print $_;
                    }
                    else {
                        print "&#160;";
                    }
                    print "</td>";
                }
                print "\n</tr>\n";
            }
            print "</table>\n";
        }
        else {
            print "Error: can't execute the query: ", $dbh->errstr(), "<br>\n";
            print CGI::dump();
        }
    }
}

if (param('add_entry')) {
    if ($error) {
        print "\n<p>\nErrors prevent your INSERT from being performed.\n";
    }
    else {
        my $query = "INSERT INTO $table (";
        $query .= join(", ", map {$_->[0]} @cells);
        $query .= ")\nVALUES (";
        $query .= join(", ", map {
            $table[$table{$_->[0]}][1] eq 'integer'
            ?
            $_->[1]
            :
            $dbh->quote($_->[1])
        } @cells);
        $query .= ")";

        #print "<pre>", CGI::dump(), "</pre>\n";
        print "Your query
is:<br>\n<pre><b>$query</b>;</pre>\n";

        my $sth = $dbh->prepare($query);
        if ($sth and $sth->execute()) {
            print "Insert successful<br>\n";
        }
        else {
            print "Error: can't execute the query: ", $dbh->errstr(), "<br>\n";
            print CGI::dump();
        }
    }
}

if (@table) {
        print <<"EOS";
<form method=get action="$ENV{'SCRIPT_NAME'}">
<input type=hidden name=dbname value="$dbname">
<input type=hidden name=table value="$table">

<table>
EOS

        foreach my $field (@table) {
            my ($name, $class, $size, $type) = @$field;
            print
"<tr><td><tt>$name</tt></td>\n<td>\n";
            #print "<small>", join('; ', @$field), "</small><p>\n";
            if ($class eq 'integer') {
                print qq(<input type=text name="field_$name"),
                    " size=", $size + 2, ">\n";
            }
            elsif ($class eq 'character') {
                print qq(<input type=text name="field_$name"),
                    " size=", ($size > 40 ? '40' : $size + 2), 
                    " maxlength=$size", ">\n";
            }
            elsif ($class eq 'text') {
                print qq(<textarea name="field_$name"),
                    " cols=50 rows=4></textarea>\n";
            }
            elsif ($class eq 'enum') {
                foreach (@$size) {
                    print ' <input type=radio name="field_', $name,
                        '" value="', $_, "\">$_\n";
                }
            }
            elsif ($class eq 'date') {
                print qq(<input type=text name="field_$name"),
                    " size=", ($size > 40 ? '40' : $size + 2), ">\n";
            }
            else {
                print "How do I display [$class : $name, $size, $type]?<br>\n";
            }

            print
"</td>\n<td><small>[$type]</small></td></tr>\n";
        }

        print <<"EOS";
</table>

<input type=hidden name=cells_passed value=1>
<input type=reset>
<input type=submit name=do_query value="Perform Query">
<input type=submit name=add_entry value="Add Entry">

</form>
EOS
}


# get list of tables, and have user select one
my @tables = $dbh->func('_ListTables');

if (@tables) {
    print join "\n&#160;\n",
            map {
    qq(<a href="$ENV{'SCRIPT_NAME'}?dbname=$dbname&table=$_">$_</a>\n)
            } @tables;
}
else {
    print "Sorry, no tables found in the $dbname database.\n";
}

BOTTOM:
$dbh->disconnect();

print <<"EOS";
</body>
</html>
EOS

exit 0;
Thread
field types needed from mysql databaseJiri Cuypers12 Mar
  • Re: field types needed from mysql databaseThimble Smith12 Mar
  • Re: field types needed from mysql databaseChristian Mack13 Mar
RE: field types needed from mysql databaseJiri Cuypers13 Mar