Hi friends what is the best way to show the mysql enum values while inserting and updating php page ?
name ENUM('small', 'medium', 'large')
edited: actually I was asking for this . I have a database field
`color` enum('red','blue','green','white') DEFAULT NULL,
and php form
<label for="color">Colors</label><br />
<input type="text" name="color" />
How do i display enum value on the php form from mysql database? Please help
If you have a enum field, the MySQL will return string values for it and you can set with integer and string values too. Simply doing this:
mysql_query("select name from tablename");
will give you full labels like small or medium or large
And you can similarly update them too using full labels like this:
mysql_query("insert into tablename (name) values ('small')");
or numeric values like this:
mysql_query("update tablename set name = 2");
You can get an array of all possible enum values using the following function:
function enum_values($table_name, $column_name) {
$sql = "
SELECT COLUMN_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '" . mysql_real_escape_string($table_name) . "'
AND COLUMN_NAME = '" . mysql_real_escape_string($column_name) . "'
";
$result = mysql_query($sql) or die (mysql_error());
$row = mysql_fetch_array($result);
$enum_list = explode(",", str_replace("'", "", substr($row['COLUMN_TYPE'], 5, (strlen($row['COLUMN_TYPE'])-6))));
return $enum_list;
}
You'd need to do a "SHOW COLUMNS FROM " to get the table schema. You could then go ahead and parse every line.
$field = "enumField"; // The field that contains the ENUM
$result=mysql_query('show columns from '.$table.';');
while($tuple=mysql_fetch_assoc($result))
{
if($tuple['Field'] == $field)
{
$types=$tuple['Type'];
$beginStr=strpos($types,"(")+1;
$endStr=strpos($types,")");
$types=substr($types,$beginStr,$endStr-$beginStr);
$types=str_replace("'","",$types);
$types=split(',',$types);
if($sorted)
sort($types);
break;
}
}
You now have an array containing the possible values of your ENUM in $types.
Note: That code is a quick-hack. Could be a little more tidy :)
Your question is not very clear. If you mean what input method should I use in the form on my php page, the answer below is relevant.
You can use a dropdown:
<select name="name">
<option value="small">small</option>
<option value="medium">medium</option>
<option value="large">large</option>
</select>
Radio buttons are another possibility:
<input type="radio" name="name" value="small"> small
<input type="radio" name="name" value="mediuim"> medium
<input type="radio" name="name" value="large"> large
In Codeigniter you can display enum possible values:
function gender_enums($table , $field){
$query = "SHOW COLUMNS FROM ".$table." LIKE '$field'";
$row = $this->db->query("SHOW COLUMNS FROM ".$table." LIKE '$field'")->row()->Type;
$regex = "/'(.*?)'/";
preg_match_all( $regex , $row, $enum_array );
$enum_fields = $enum_array[1];
foreach ($enum_fields as $key=>$value)
{
$enums[$value] = $value;
}
return $enums;
}
Without regular expressions, after obtaining the MYSQL ENUM string (e.g. ENUM('apples','oranges','pears') using "SHOW COLUMNS FROM table" to put the type into $field["Type"] you could use the following code based on the fact that only the odd numbered array elements after exploding with a single quote are useful:
$t = explode( "'", $field["Type"] );
for($i=1; $i < count($t); $i += 2)
echo "<br>Value $i: ".$t[$i];
It is possible that some really evil choices of ENUM value names could cause problems.
so, ENUM force database to accept one value from that enumerated in it, so only one value can be stored in that table. Then you extract it like any other value, you cant extract all values in web page, you must defice them manually in select tag and compare with value in DB and make active that option when you retreat it from Database.
<selec name="color">
<option value="red" selected="<php // here make
if( value from `$`_POST <<or `$`var_color>>== "red") {
echo 'red'; }
elseif(`$`_Postvariable <<or `$`var_color>> =='white) { echo "white"; }
or something similar , i do not remmber now all structure make active == field option to be selected comparing it
ex: 'color' enum('red','blue','green','white') database will store in color red (or blue or green or white);
when SELECT table name color field :
you will get only "red" value that is sored for that line in database
It worked for me this way:
$str = "enum('2','CLOSE', null, true, DEFAULT)"; //enum print pattern
$pattern = "/[^enum\(\'\,\)][\w]*[\d]*/i";
if(preg_match_all($pattern, $str, $matches)) {
echo implode(',', $matches[0]); // 2, CLOSE, null, true, DEFAULT
}
