Organizational Research By

Surprising Reserch Topic

get table column names in mysql


get table column names in mysql  using -'php,sql,mysql'

asked Sep 9, 2015 by rajesh
0 votes
7 views



Related Hot Questions

9 Answers

0 votes

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;
answered Sep 9, 2015 by rajesh
0 votes

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.

answered Sep 9, 2015 by rajesh
0 votes

The following SQL statements are nearly equivalent:

SELECT COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE table_name = 'tbl_name'
  [AND table_schema = 'db_name']
  [AND column_name LIKE 'wild']

SHOW COLUMNS
FROM tbl_name
[FROM db_name]
[LIKE 'wild']

Reference: INFORMATION_SCHEMA COLUMNS

answered Sep 9, 2015 by rajesh
0 votes

You may also want to check out mysql_fetch_array(), as in:

$rs = mysql_query($sql);
while ($row = mysql_fetch_array($rs)) {
//$row[0] = 'First Field';
//$row['first_field'] = 'First Field';
}
answered Sep 9, 2015 by rajesh
0 votes

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;
answered Sep 9, 2015 by rajesh
0 votes

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-->';
answered Sep 9, 2015 by rajesh
0 votes

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. :)

answered Sep 9, 2015 by rajesh
0 votes

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

answered Sep 9, 2015 by rajesh
0 votes

The mysql_list_fields function might interest you ; but, as the manual states :

This function is deprecated. It is preferable to use mysql_query() to issue a SQL SHOW COLUMNS FROM table [LIKE 'name'] statement instead.

answered Sep 9, 2015 by rajesh

...