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 " ";
}
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 \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;
