Re: Get all the enum values from a Mysql column?

Addressed to: John Guynn <John.Guynn@telescan.com> php-db@lists.php.net ** Reply to note from John Guynn <John.Guynn@telescan.com> Tue, 21 Nov 2000 10:32:43 -0600 > > Is there an easy way to get all the enum values from a column? > > For example I have an enum column with the values > "Astro","Camaro","Firehawk","Neon" and I would like to > pull those values > out to put in as <option>s in a html form with <select>. > > I've read the mysql maunal about pulling all the values from and enum > column, "If you want to get all possible values for an ENUM column, you > should use: SHOW COLUMNS FROM table_name LIKE enum_column_name and parse > the ENUM definition in the second column." For some reason I can't make > heads or tails of this. Do you have access to command line MySQL? If so, the first thing to do is fire it up and try a query or two till you get something that works. For example, I have a table Types, that includes an enum field Free. I would get the possible values for Free like this: SHOW COLUMNS FROM Types LIKE 'Free'; This returns: +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | Free | enum('No','Yes') | Yes | | Null | | +-------+------------------+------+-----+---------+-------+ As you can see, the second column contains the vaules, and some extra stuff. The trick is to parse the values out of the junk... Once you get a query that returns the desired results in the MySQL interpreter, you can convert it to PHP. $Result = mysql_query( "SHOW COLUMNS FROM Types LIKE 'Free'" ); while( list( $Field, $Type, $Null, $Key, $Default, $Extra ) = mysql_fetch_row( $Result )) { list( $junk, $Type ) = explode( '(', $Type ); # Type is now 'No','Yes') list( $Type ) = explode( ')', $Type ); # Type is now 'No','Yes' $Type = str_replace( "'", '', $Type ); # Type is now No,Yes $Types = explode( ',', $Type ); # Types is now an array containing the values allowed for the enum. # $Types[0] = No $Types[1] = 'Yes' } mysql_free_result( $Result ); This may not be the the easiest or best way to parse the data, but it should work. I have not actually tried it... Using a while may seem unusual when I already know there should only be one row returned, but I use it because it will not throw an error message or warning if no rows are returned. Rick Widmer Internet Marketing Specialists www.developersdesk.com