CODEWITHSUNDEEP
phpmysql
ktm
ktm

Reputation: 6085

displaying mysql enum values in php

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

Upvotes: 5

Views: 30900

Answers (8)

Marcelo Perim
Marcelo Perim

Reputation: 1

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
}

Upvotes: 0

user2322332
user2322332

Reputation: 1

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

Upvotes: 0

David Crowe
David Crowe

Reputation: 143

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.

Upvotes: 1

Faisal
Faisal

Reputation: 1925

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;
}

Upvotes: 1

Andrew
Andrew

Reputation: 3381

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;
}

Upvotes: 5

Mischa
Mischa

Reputation: 43298

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

Upvotes: 2

halfdan
halfdan

Reputation: 34214

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 :)

Upvotes: 4

shamittomar
shamittomar

Reputation: 46692

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");

Upvotes: 8

Related Questions