From: Thimble Smith
Date: March 12 1999 5:56pm
Subject: Re: field types needed from mysql database
List-Archive: http://lists.mysql.com/mysql/116
Message-Id: <19990312105600.Q28088@desert.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
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";
database explorer: $dbname
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: $_
\n";
}
}
}
else {
print "can't run DESCRIBE $table query: ",
$dbh->errstr(), "\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''
\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''
\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''
\n";
next;
}
}
#print "Cells:
\n", map({"$_->[0] == $_->[1]
\n"} @cells), "\n";
}
if (param('do_query')) {
if ($error) {
print "\n\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:
\n
$query;
\n";
my $sth = $dbh->prepare($query);
if ($sth and $sth->execute()) {
print "\n";
print "\n", map({"| $_->[0] | "} @table), "\n
\n";
while (my @row = $sth->fetchrow()) {
print "\n";
foreach (@row) {
print "| ";
if (/\S/) {
print $_;
}
else {
print " ";
}
print " | ";
}
print "\n
\n";
}
print "
\n";
}
else {
print "Error: can't execute the query: ", $dbh->errstr(), "
\n";
print CGI::dump();
}
}
}
if (param('add_entry')) {
if ($error) {
print "\n\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 "
", CGI::dump(), "
\n";
print "Your query is:
\n$query;
\n";
my $sth = $dbh->prepare($query);
if ($sth and $sth->execute()) {
print "Insert successful
\n";
}
else {
print "Error: can't execute the query: ", $dbh->errstr(), "
\n";
print CGI::dump();
}
}
}
if (@table) {
print <<"EOS";
EOS
}
# get list of tables, and have user select one
my @tables = $dbh->func('_ListTables');
if (@tables) {
print join "\n \n",
map {
qq($_\n)
} @tables;
}
else {
print "Sorry, no tables found in the $dbname database.\n";
}
BOTTOM:
$dbh->disconnect();
print <<"EOS";
EOS
exit 0;