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="c开发者_StackOverflow社区olor" />
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.
精彩评论