At 3:35 PM +0100 12/21/01, Henning Sprang wrote:
>Hy there,
>I would like to know if there is a possibility to get all allowed values
>of an enum field out of the database.
>
>I know there are methods to get the field type and those give me back
>something like "enum('value1', 'value2', 'value3')", and I can parse my
>possible values out of this with regexp's in the Programming language of
>my Choice, but I am interested to know if there isn't a more "beautiful"
>possibility where Mysql gives me back the result direct.
Nope. You have to use the metadata. Here's an example in Perl.
It actually gets more than the list of values, and it works for
SET columns, too.
# Take a database connection, a table name, and a column name.
# Return a reference to a hash with name, type, values, default,
# and nullable keys.
sub get_enumorset_info
{
my ($dbh, $tbl_name, $col_name) = @_;
my $info = {};
$info->{name} = $col_name;
# escape any SQL pattern characters in column name
$col_name =~ s/([_%])/\\$1/g;
my @row = $dbh->selectrow_array (
"SHOW COLUMNS FROM $tbl_name LIKE '$col_name'"
);
return undef unless @row;
return undef unless $row[1] =~ /^(enum|set)\((.*)\)$/;
$info->{type} = $1;
# split value list on commas, trim quotes from end of each word
my @val = split (",", $2);
s/^'(.*)'$/$1/ foreach (@val);
$info->{values} = [ @val ];
# determine whether or not column can contain NULL values
$info->{nullable} = ($row[2] eq "YES");
# get default value (undef represents NULL)
$info->{default} = $row[4];
return $info;
}
>
>
>greets,
>henning
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
