How can I get enum possible values in a MySQL database using PHP?

You can get the enum possible values in a MySQL database using PHP by querying the INFORMATION_SCHEMA.COLUMNS table. This approach allows you to extract enum values programmatically without hardcoding them in your application.

Database Setup

First, let's create a sample table with an ENUM column ?

CREATE TABLE EnumDemo (
    Id int,
    Color ENUM('RED','GREEN','BLUE','BLACK','ORANGE')
);

Method 1: Basic Query to Get Enum Values

Use the INFORMATION_SCHEMA.COLUMNS table to retrieve the enum definition ?

<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "sample";

try {
    $pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $sql = "SELECT COLUMN_TYPE as EnumValues 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = :dbname 
            AND TABLE_NAME = :table_name 
            AND COLUMN_NAME = :column_name";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute([
        ':dbname' => 'sample',
        ':table_name' => 'EnumDemo',
        ':column_name' => 'Color'
    ]);
    
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    echo $result['EnumValues'];
    
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

enum('RED','GREEN','BLUE','BLACK','ORANGE')

Method 2: Extract Individual Enum Values

Parse the enum string to get individual values as an array ?

<?php
function getEnumValues($pdo, $database, $table, $column) {
    $sql = "SELECT COLUMN_TYPE 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = :dbname 
            AND TABLE_NAME = :table_name 
            AND COLUMN_NAME = :column_name";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute([
        ':dbname' => $database,
        ':table_name' => $table,
        ':column_name' => $column
    ]);
    
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    $enumString = $result['COLUMN_TYPE'];
    
    // Extract values using regex
    preg_match_all("/'([^']+)'/", $enumString, $matches);
    
    return $matches[1];
}

// Usage
$enumValues = getEnumValues($pdo, 'sample', 'EnumDemo', 'Color');
print_r($enumValues);
?>

Array
(
    [0] => RED
    [1] => GREEN
    [2] => BLUE
    [3] => BLACK
    [4] => ORANGE
)

Method 3: Using SHOW COLUMNS

Alternative approach using the SHOW COLUMNS command ?

<?php
$sql = "SHOW COLUMNS FROM EnumDemo WHERE Field = 'Color'";
$stmt = $pdo->query($sql);
$result = $stmt->fetch(PDO::FETCH_ASSOC);

echo "Column Type: " . $result['Type'];

// Extract enum values
preg_match_all("/'([^']+)'/", $result['Type'], $matches);
$enumValues = $matches[1];

foreach($enumValues as $value) {
    echo "
- " . $value;
}
?>

Column Type: enum('RED','GREEN','BLUE','BLACK','ORANGE')
- RED
- GREEN
- BLUE
- BLACK
- ORANGE

Conclusion

Use INFORMATION_SCHEMA.COLUMNS for cross-database compatibility or SHOW COLUMNS for MySQL-specific applications. Both methods allow dynamic enum value extraction without hardcoding values in your PHP application.