How to get available values for SET field?

Is there any way to get availab开发者_Python百科le values for SET field in table?

Thank you.

You can retrieve the possible values for a SET field using DESCRIBE myTableName mySetColumn or SHOW COLUMNS FROM myTableName LIKE mySetColumn:

  mysql> DESCRIBE myTableName mySetColumn;
  +-------+-------------------------------------------+------+-----+---------+-------+
  | Field | Type                                      | Null | Key | Default | Extra |
  +-------+-------------------------------------------+------+-----+---------+-------+
  | myset | set('Travel','Sports','Dancing','Dining') | YES  |     | NULL    |       |
  +-------+-------------------------------------------+------+-----+---------+-------+

Informative article here, manual here.

SELECT `COLUMN_TYPE` FROM `information_schema`.`COLUMNS`
 WHERE `TABLE_SCHEMA` = 'my_database_name'
   AND `TABLE_NAME`   = 'my_table_name'
   AND `COLUMN_NAME`  = 'my_set_column';

gives you only the type, e.g.

set('Travel','Sports','Dancing','Dining')

you'll still have to extract the set value on a textual base, but there's less clutter around it this way.

Full implementation from @Andy's link to the documentation:

/**
 * @return array
 * @param table DB table
 * @param column Column name
 * @desc Return an array of the possible values for a SET
 */
function get_set($table,$column)
{
    $sql = "SHOW COLUMNS FROM $table LIKE '$column'";
    if (!($ret = mysql_query($sql)))
        die("Error: Could not show columns");

    $line = mysql_fetch_assoc($ret);
    $set  = $line['Type'];
    // Remove "set(" at start and ");" at end.
    $set  = substr($set,5,strlen($set)-7);
    // Split into an array.
    return preg_split("/','/",$set); 
}

The above methods weren't exactly working for me, but I ended up with this and it works great. Posting in case this helps anyone else in the same situation. You'll just have to trim the string based on your results, as it will include your column name alongside the word 'set'.

    <?php>
    include ('database.php');
    $query = "SHOW COLUMNS FROM Products LIKE 'Genre'";

    $stmt = $con->prepare( $query );

    $result = $stmt -> execute();
    if ($result) {
    $row = $stmt -> fetch(PDO::FETCH_ASSOC);
        $genres = implode($row);

        $genres  = substr($genres,10,strlen($genres)-14);
        //echo $genres;
        $genres = preg_split("/','/",$genres);

    //this is to populate my select box options with set values
        echo "<select name = 'genre'>";
        echo "<option>Select...</option>";
        foreach ($genres as $key=>$value) {
            echo "<option name = '$value' value = '$value'>$value</option>";
        };
        echo "</select>";
    }


    ?>

Here is how to get the possible values of SET using PDO extension.

function get_set($table, $column)
{

    global $db; //PDO DB Handler

    $sql = "SHOW COLUMNS FROM $table LIKE :column";
    $stmt = $db -> prepare($sql);

    $stmt -> bindParam(":column", $column, PDO::PARAM_STR, 50);

    try {
        $result = $stmt -> execute();
        $row = $stmt -> fetch(PDO::FETCH_ASSOC);
        $set = $row['Type'];
        $set  = substr($set,5,strlen($set)-7);
        // Split into an array.
        return preg_split("/','/",$set);
    } catch (PDOException $e) {
        echo $e -> getMessage();
        return false;
    }

}

[Source]