How can I get the type, length and comment about columns in MySQL database using PDO?

Question

I have created this function to get information about the tables, and their corresponding columns, in my MySQL database:

class SQL extends DB
{
    public static function showTables($alias='', $table_name='')
    {
        if(empty($alias) || $alias===true){  //  empty = show all tables from all available connections, true = show columns aswell
            foreach(self::get() as $con){  //  get() keeps, among other things, the aliases of all available connections
                $html .= '"'.$con['alias'].'": '.$con['db'];  //  the alias for the connection, and databasename, for the following table(s)
                $tables = self::con($con['alias'])->query('SHOW TABLES')->fetchAll(PDO::FETCH_COLUMN);  //  fetch an array of all tables available through this connection
                foreach($tables as $table){
                    $html .= $table;  //  table name
                    if($alias===true){  //  show columns aswell when this is set to true
                        $columns = self::con($con['alias'])->query('SHOW COLUMNS FROM '.$table)->fetchAll(PDO::FETCH_COLUMN);  //  fetch an array of all column in this table
                        foreach($columns as $column){
                            $html .= $column;  //  column name
                        }
                    }
                }
            }
        } else {
            /*  basically the same code, but limits the result to a defined connection. And table if that is defined  */
        }
    }
}

It's important to know that this function is tailored to the way I set and use the PDO connections. Basically each connection has an alias, and the connection is accessed trough the given alias. But that has nothing to do with my issue.

Here's how I use the function, and what it produces:

<?=sql::showTables(true)?>

(I've removed all the css-styling from the code above)

This is ok. But I would also like to get, at least, the type, lenght and comment (if any).

I just tried, when I was writing this question, to put the getColumnMeta() inside the column foreach-loop, but that didn't work quite as expected:

    $columns = self::con($con['alias'])->query('SHOW COLUMNS FROM '.$table)->getColumnMeta(0);
    foreach($columns as $column){
        $meta = self::con($con['alias'])->query('SELECT `'.$column.'` FROM '.$table)->getColumnMeta(0);
        $html .= $meta['name'].' '.$meta['native_type'].'('.$meta['len'].')';
    }

You can see the difference..
Does anyone know of another method for this? getting the type, lenght and comment.

Thanks in advance.

Answer 1

If you want to select the comment column info on that particular table you can use:

SHOW FULL COLUMNS FROM table_test // with FULL option

Simple example:

$db = new PDO('mysql:host=localhost;dbname=DB_NAME;charset=utf8', 'username', 'password');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$query = $db->query('SHOW FULL COLUMNS FROM table_test');
                      //  ^
$results = $query->fetchAll(PDO::FETCH_ASSOC);
echo '<pre>';
print_r($results);