Get Enum Values Into Array With PHP

So, you have a MySQL database and one of the tables has an enum field. First you want to kill the person who created it, then you want to kill yourself because your application requires you to do something with the values stored in the enum.

Unlike a normal table lookup, enums are like an array value for a table field, so extracting the data is not quite so simple, but far from impossible. This example shows how to extract the enum fields from a database, and get them into an array. Once you have the values into an array, you are free to do whatever you like with them. Put them into a HTML drop down, create a menu, impress your friend.

Table Definition

CREATE TABLE my_table( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(25) NOT NULL COMMENT 'User name', response enum( 'Yes', 'No', 'Maybe') COMMENT 'List of possible responses' );

Code



<?php

    $db_name = 'phpro';
    $db_host = '127.0.0.1';
    $db_user = 'my_username';
    $db_pass = 'my_password';
    $db_port = 3306;

        $dsn = "mysql:host=$db_host;dbname=$db_name";
        $db = new PDO( $dsn, $db_user, $db_pass );

    $sql = "SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='my_table' AND COLUMN_NAME='response'";
    $stmt = $db->prepare( $sql );
    $stmt->execute();
    $result = $stmt->fetchColumn();

    preg_match_all( '\/(["\'])([^"\']+)\1/', $result, $matches );

    foreach( $matches[2] as $enum )
    {
        echo "$enum\n";
    }
?>