Page 1 of 1

for MySQL fields with type 'set' can i get at the choices?

Posted: Wed Jun 05, 2002 8:02 am
by jonpaulduvall
Perhaps this should be in the Database forum, but it's more of a question about getting php to do something with MySQL than about MySQL directly.

I am creating a generic include php script which selects a db and table, grabs all the table's fields and their names, lengths, types, and flags, and shoves them into a two-dimensional array. I then use this array to set up a form, and then use the types and flags values to validate the fields before shoving them into my database. Straight forward, i got that figured out.

mysql_field_flags gives me a string easy enough to parse out for things, such as 'set' but I want at the values that make up the "set"...

Here is the essence of my question:

When i create a field in a MySQL table the GUI i use lets me make the TYPE = "set" and i can give it a list of values for the "set." Can php get at that list?

I would use this list to generate a pull down menu field in a form. So for instance in a field which stores a state, i have a "set" like: AZ, CA, MA, WI, WA, etc. all the two letter state postal codes. I want php to get at that list. Is this possible?

I'm new to MySQL so maybe i'm using the "set" type wrong, indeed my MySQL GUI leads me to believe that "set" is a TYPE, but php's mysql_list_fields calls it a TYPE of "string" and a FLAG of "set". Am I using the "set" type/flag correctly? Is it a way to store only specific string values in a field?

I could always hard code it, but i am trying to make a generic include script so that i can use it to generate a form for inputing data into ANY table, so getting at the value choices for a "set" field and being able to stick those in an array or something would be most helpful.

Any ideas are greatly appreciated. I am a novice php programmer, the language seems easy and straightforward, but none of my reference books have a solution for this particular delima (and maybe it's impossible). Any libraries out there that might extend php-mysql functionality that might help me get at the "set" lists?

Thanks for any help. Three cheers for open-source languages!

--Jon Paul Duvall

Posted: Wed Jun 05, 2002 9:08 pm
by volka

Code: Select all

$query = "SHOW COLUMNS from member LIKE 'status'";
$gresult = mysql_query($query, $dbconn);
$gstate = mysql_fetch_array($gresult);
$gstate = $gstateї'Type'];
if (preg_match_all("/'(.*?(?='ї,\)]))'/x", $gstate, $match)) 
{
   foreach ($matchї1] as $value)
      print('<option>'.$value.'</option>');
&#125;

Posted: Thu Jun 06, 2002 6:40 am
by jason
The difference between SET and ENUM:
ENUM('value1','value2',...)
An enumeration. A string object that can have only one value, chosen from the list of values 'value1', 'value2', ..., NULL or the special "" error value. An ENUM can have a maximum of 65535 distinct values. See section 6.2.3.3 The ENUM Type.
SET('value1','value2',...)
A set. A string object that can have zero or more values, each of which must be chosen from the list of values 'value1', 'value2', ... A SET can have a maximum of 64 members. See section 6.2.3.4 The SET Type.
Basically, what that means, is that ENUM and SET are the same thing, a list of choices. However, ENUM allows you to only choose 1 thing, SET allows you to choose zero to many.

If you want to get the different choices you have for a SET or ENUM, take a look at this code here for this function:

mysql_get_enum_values