RE: [PHP-DB] retrieve enum values

Melanie,

Here's a function that will parse the SHOW COLUMNS query output and will
return an array of ENUM values, after you have the array it should be
easy to generate the html code you need.

The first two parameters are self explanatory (the table name and the
column name).

Hope this helps,

--------------------

define(G_ENUM_COLUMN,4);  // set this to 4 if you are using "SET" column
type, and 4 for "ENUM" column type.

/*
Return the SET or ENUM set of values.

$p_start can be 3 for SET...  or 4 for ENUM...
*/
function get_set_column_values ( $p_table, $p_colname ) {
$result =3D mysql_query($query);

$arr =3D  mysql_fetch_array(=20
doQuery( "SHOW COLUMNS FROM $p_table LIKE '$p_colname';"
)=20
);
$value =3D substr( $arr[1], G_ENUM_COLUMN );

eval( "\$the_array =3D array $value;" ); // let eval do all the
parsing work.
return $the_array;
}

--------------------

- Aman Patel, Sys Admin / Database / Web Developer, International
Outreach x4076

> -----Original Message-----
> From: mel list_php [mailto:list_php@hotmail.co.uk]
> Sent: Thursday, April 14, 2005 7:54 AM
> To: php-db@lists.php.net
> Subject: [PHP-DB] retrieve enum values
>=20
>=20
> Hi!
>=20
> I have a column type enum in mysql.
> At the moment the possible values are 1,2 and 3.
>=20
> I make a form for my user to modify that value, something
> like: <select> <option value=3D'1'>1</option> <option=20
> value=3D'2'>2</option> <option value=3D'3'>3</option> </select>
>=20
> I may need to add a value 4 to the enum, and in that case I
> would like to=20
> avoid modifying the code.
>=20
> I would like to know it it is possible to have something
> like: select possible_enum_values from table ... and then=20
> having a loop through these values. I can't select distinct=20
> values in that column because one value may not=20
> exist yet and I don't want to have fake records.
>=20
> I found that in the mysql doc and was wondering if there is
> an already=20
> implemented php function intsead of having to parse:
> " If you want to determine all possible values for an ENUM=20
> column, use SHOW=20
> COLUMNS FROM tbl_name LIKE enum_col and parse the ENUM=20
> definition in the=20
> second column of the output."
>=20
> Any idea?
>=20
> Melanie
>=20
>  ________________________________________
_________________________
> It's fast, it's easy and it's free. Get MSN Messenger today!
> http://www.msn.co.uk/messenger
>=20
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>=20
>=20

A little correction in the code:

----------------

define(G_ENUM_COLUMN,4);  // set this to 4 if you are using "SET" column
type, and 4 for "ENUM" column type.

/*
Return the SET or ENUM set of values.
$p_start can be 3 for SET...  or 4 for ENUM...
*/
function get_set_column_values ( $p_table, $p_colname )
{
$query = "SHOW COLUMNS FROM {$p_table} LIKE '{$p_colname}';";
$result = mysql_query($query);
$arr =  mysql_fetch_array( $result );
$value = substr( $arr[1], G_ENUM_COLUMN );
eval("\$the_array = array $value;"); // let eval do all the parsing
return $the_array;
}

----------------


Patel, Aman wrote:
> Melanie,
>
> Here's a function that will parse the SHOW COLUMNS query output and will
> return an array of ENUM values, after you have the array it should be
> easy to generate the html code you need.
>
> The first two parameters are self explanatory (the table name and the
> column name).
>
> Hope this helps,
>
> --------------------
>
> define(G_ENUM_COLUMN,4);  // set this to 4 if you are using "SET" column
> type, and 4 for "ENUM" column type.
>
> /*
> 	Return the SET or ENUM set of values.
>
> 	$p_start can be 3 for SET...  or 4 for ENUM...
> */
> function get_set_column_values ( $p_table, $p_colname ) {
> 	$result = mysql_query($query);
>
> 	$arr =  mysql_fetch_array(
> 		doQuery( "SHOW COLUMNS FROM $p_table LIKE '$p_colname';"
> )
> 	);
> 	$value = substr( $arr[1], G_ENUM_COLUMN );
>
> 	eval( "\$the_array = array $value;" ); // let eval do all the
> parsing work.
> 	return $the_array;
> }
>
> --------------------
>
> - Aman Patel, Sys Admin / Database / Web Developer, International
> Outreach x4076
>
> 
>
>