How to get the available values ​​for the SET field?

Question

Is there a way to get the available values ​​for the SET field in the table?

Thanks.

Answer 1

You can get the possible values ​​for the 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 | | +-------+-------------------------------------------+------+-----+---------+-------+ 

Information article here , manual here .

Answer 2

 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 type, for example.

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

you still have to extract the setpoint on a textual basis, but there is less interference around it.

Answer 3

Full implementation from the @Andy 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); } 

Answer 4

Here's how to get the possible SET values ​​using the 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; } } 

[A source]

Answer 5

The above methods didn’t quite work for me, but I ended up with this and it works great. Posting in case this helps someone else in the same situation. You just need to trim the row based on your results, as it will contain the name of your column along with 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>"; } ?>