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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jonpaulduvall
Forum Newbie
Posts: 2
Joined: Wed Jun 05, 2002 8:02 am

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

Post 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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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;
jason
Site Admin
Posts: 1767
Joined: Thu Apr 18, 2002 3:14 pm
Location: Montreal, CA
Contact:

Post 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
Post Reply