Get table column names in MySQL?

Question

Is there a way to grab the columns name of a table in mysql? using php

Answer 1

It's also interesting to note that you can use
EXPLAIN table_name which is synonymous with DESCRIBE table_name and SHOW COLUMNS FROM table_name although EXPLAIN is more commonly used to obtain information about the query execution plan.

Answer 2

$col = $db->query("SHOW COLUMNS FROM category");

while ($fildss = $col->fetch_array())
{             
    $filds[] = '"{'.$fildss['Field'].'}"';
    $values[] = '$rows->'.$fildss['Field'].'';
}

if($type == 'value')
{
    return $values = implode(',', $values);
}
else {
     return $filds = implode(',', $filds);
}

Answer 3

Look into:

mysql_query('DESCRIBE '.$table);

Answer 4

You can use DESCRIBE:

DESCRIBE my_table;

Or in newer versions you can use INFORMATION_SCHEMA:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'my_database' AND TABLE_NAME = 'my_table';

Or you can use SHOW COLUMNS:

SHOW COLUMNS FROM my_table;

Answer 5

you can get the entire table structure using following simple command.

DESC TableName

or you can use following query.

SHOW COLUMNS FROM TableName

Answer 6

How about this:

SELECT @cCommand := GROUP_CONCAT( COLUMN_NAME ORDER BY column_name SEPARATOR ',\n')
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'my_database' AND TABLE_NAME = 'my_table';

SET @cCommand = CONCAT( 'SELECT ', @cCommand, ' from my_database.my_table;');
PREPARE xCommand from @cCommand;
EXECUTE xCommand;