December 9, 2013
How to get available values for SET field?
Kirzilla’s Question:
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 | |
+-------+-------------------------------------------+------+-----+---------+-------+
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;
}
}
