Mapping mysql enum values ​​in php

Question

Hi, what is the best way to show mysql enum values ​​when inserting and updating php page?

name ENUM('small', 'medium', 'large') 

edited: I actually requested 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 to display enum value in php form from mysql database? Please, help

Answer 1

If you have an enumeration field, MySQL will return string values ​​for it, and you can also set values ​​with integer and string values. Just do the following:

 mysql_query("select name from tablename"); 

provide you with complete labels like small or medium or large

And you can also update them using complete labels:

 mysql_query("insert into tablename (name) values ('small')"); 

or numerical values:

 mysql_query("update tablename set name = 2"); 

Answer 2

You need to do "SHOW COLUMNS FROM" to get the table schema. You could continue and analyze each 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; } } 

Now you have an array containing the possible values ​​of your ENUM in $types .
Note. This code is a quick hack. Maybe a little more neat :)

Answer 3

You can get an array of all possible enumeration 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; } 

Answer 4

Your question is not very clear. If you mean which input method should be used in the form on my php page, then the corresponding answer matters.

You can use the dropdown menu:

 <select name="name"> <option value="small">small</option> <option value="medium">medium</option> <option value="large">large</option> </select> 

Radio buttons - this is another opportunity:

 <input type="radio" name="name" value="small"> small <input type="radio" name="name" value="mediuim"> medium <input type="radio" name="name" value="large"> large 

Answer 5

existing enum labels from enum column type, you can check possible enum values:

  $field_type = $wpdb->get_row("SHOW COLUMNS FROM {$this->table_name} LIKE 'field_name'")->Type; preg_match("/^enum\(\'(.*)\'\)$/", $field_type, $matches); $enum_string = (isset($matches[1])?$matches[1]:''); $enum_array = explode("','", $enum_string); echo (in_array('enumlabel', $enum_array)?'already exist':'not exist'); exit; 

Answer 6

In Codeigniter, you can display the possible enumeration 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; } 

Answer 7

Without regular expressions, after getting the MYSQL ENUM string (for example, ENUM ("apples", "oranges", "pears") using the "SHOW COLUMNS FROM table" to put the type in the $ field ["Type"], you can use The following code is based on the fact that only odd array elements are useful after a single-quote explosion:

 $t = explode( "'", $field["Type"] ); for($i=1; $i < count($t); $i += 2) echo "<br>Value $i: ".$t[$i]; 

It is possible that some really wrong variants of ENUM value names can cause problems.