Get enum values from MySQL

Mysql reference manual on the ENUM field type. In the comments you can find different solutions for parsing MySQL output. What follows is the best practice with some explanation.

Basically this is a Regular Expressions problem.

MySQL returns something like this:

Field Type Null Key Default Extra
field_name enum('value1','value2','value3','value4') type

We need to parse the second column of the dataset. To do this we will use PHP's preg_match_all() function. This functions returns multidimensional array with results which looks like this:

Array (

[0] => Array (
[0] => 'value1'
[1] => 'value2'
[2] => 'value3'
[3] => 'value4' )

[1] => Array (
[0] => value1
[1] => value2
[2] => value3
[3] => value4 )

)

Here is the sample code:

function enum_select( $table , $field ){
$query = " SHOW COLUMNS FROM `$table` LIKE '$field' ";
$result = mysql_query( $query ) or die( 'error getting enum field ' . mysql_error() );
$row = mysql_fetch_array( $result , MYSQL_NUM );
#extract the values
#the values are enclosed in single quotes
#and separated by commas
$regex = "/'(.*?)'/";
preg_match_all( $regex , $row[1], $enum_array );
$enum_fields = $enum_array[1];
return( $enum_fields );
}

Tagged:
php
mysql

Comments

  • Gravatar
  • Warren Vail
  • 2007-12-18 16:23:02
MySQL PDO slightly different Essentially the same technique works for mysql PDO, except that PDO doesn't like the table name to be quoted, unquote the table name and parse $row["type"], Cheers
  • Gravatar
  • Adam Scott
  • 2007-03-10 01:29:52
SQL-only parsing Hunting for a way to get those values using only SQL. The goal is to be able to add or remove an ENUM value using an SQL query without having to drop back to PHP or equivalent to build said query. Anyone?

© 2003 — 2014 Akinas
All rights reserved