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

The MySQL function describe table should get you where you want to go (put your table name in for "table"). You'll have to parse the output some, but it's pretty easy. As I recall, if you execute that query, the PHP query result accessing functions that would normally give you a key-value pair will have the column names as the keys. But it's been a while since I used PHP so don't hold me to that. :)

Answer 2

This solution is from command line mysql

mysql>USE information_schema;

In below query just change <--DATABASE_NAME--> to your database and <--TABLENAME--> to your table name where you just want Field values of DESCRIBE statement

mysql> SELECT COLUMN_NAME FROM COLUMNS WHERE TABLE_SCHEMA = '<--DATABASE_NAME-->' AND   TABLE_NAME='<--TABLENAME-->';

Answer 3

I have write a simple php script to fetch table columns through PHP: Show_table_columns.php

Enjoy!

Answer 4

There's also this if you prefer:

mysql_query('SHOW COLUMNS FROM tableName'); 

Answer 5

this worked for me..

$sql = "desc MyTableName";
$result = @mysql_query($sql);
while($row = @mysql_fetch_array($result)){
    echo $row[0]."<br>";
}

Answer 6

I made a PDO function which returns all the column names in an simple array.

public function getColumnNames($table){
    $sql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = :table";
    try {
        $core = Core::getInstance();
        $stmt = $core->dbh->prepare($sql);
        $stmt->bindValue(':table', $table, PDO::PARAM_STR);
        $stmt->execute();
        $output = array();
        while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
            $output[] = $row['COLUMN_NAME'];                
        }
        return $output; 
    }

    catch(PDOException $pe) {
        trigger_error('Could not connect to MySQL database. ' . $pe->getMessage() , E_USER_ERROR);
    }
}

The output will be an array:

Array (
[0] => id
[1] => name
[2] => email
[3] => shoe_size
[4] => likes
... )

Sorry for the necro but I like my function ;)

P.S. I have not included the class Core but you can use your own class.. D.S.