Get table column names in MySQL

Question

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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

The easy way  if loading results using assoc is to do this   sql    quot SELECT p   FROM  SELECT 1  as dummy LEFT JOIN  product table  p on null quot    q    this- gt db- gt query  sql    column names   array keys  q- gt row    This you load a single result using this query  you get an array with the table column names as keys and null as value  E g  Array   product id    gt  null   sku    gt  null   price    gt  null         after which you can easily get the table column names using the php function array keys  result

User · Answer

you can get the entire table structure using following simple command   DESC TableName   or you can use following query   SHOW COLUMNS FROM TableName

User · Answer

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   Or to get column names with comma in a line  SELECT group concat COLUMN NAME    FROM INFORMATION SCHEMA COLUMNS   WHERE TABLE SCHEMA    my database  AND TABLE NAME    my table

User · Answer

in mysql to get columns details and table structure by following keywords or queries  1 DESC table name  2 DESCRIBE table name  3 SHOW COLUMNS FROM table name  4 SHOW create table table name   5 EXPLAIN table name

User · Answer

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

User · Answer

this worked for me     sql    desc MyTableName    result    mysql query  sql   while  row    mysql fetch array  result        echo  row 0    lt br gt

User · Answer

col    db- gt query  SHOW COLUMNS FROM category     while   fildss    col- gt fetch array                        filds           fildss  Field              values       rows- gt    fildss  Field          if  type     value         return  values   implode       values     else        return  filds   implode       filds

User · Answer

Look into   mysql query  DESCRIBE    table

User · Answer

I have write a simple php script to fetch table columns through PHP   Show table columns php   lt  php  db    Database     Database name  host    Database host     Hostname or Server ip  user    USER     Database user  pass    Password     Database user password  con   mysql connect  host   user   pass   if   con         link   mysql select db  db  or die  no database     mysql error         count   0      if   link             sql                 SELECT column name             FROM   information schema columns             WHERE  table schema     db                     AND table name    table name       Change the table name your own table name          result   mysql query  sql   con           if  mysql query  sql   con                 echo  sql     lt br gt   lt br gt                while   row   mysql fetch row  result                     echo  COLUMN        count        row 0   lt br gt                     table name    row 0                             echo   lt br gt Total No  of COLUMNS       count            else               echo  Error in query                    else           echo  Database not found            else       echo  Connection Failed        gt    Enjoy

User · Answer

I needed column names as a flat array  while the other answers returned associative arrays  so I used    con   mysqli connect  localhost   db user  db pw  db name    table    people          Get the column names for a mysql table      function get column names  con   table       sql    DESCRIBE    table     result   mysqli query  con   sql       rows   array      while  row   mysqli fetch assoc  result          rows      row  Field           return  rows      col names   function get column names  con   table      col names now equals          0    gt  name      1    gt  parent      2    gt  number      3    gt  chart id      4    gt  type      5    gt  id

User · Answer

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

User · Answer

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

User · Answer

There s also this if you prefer   mysql query  SHOW COLUMNS FROM tableName

User · Answer

mysqli fetch field   worked for me   if   result    mysqli - gt  query  sql          Get field information for all fields   while   fieldinfo    result - gt  fetch field          printf  Name   s n    fieldinfo - gt  name       printf  Table   s n    fieldinfo - gt  table       printf  Max  Len   d n    fieldinfo - gt  max length          result - gt  free result        Source  https   www w3schools com pHP func mysqli fetch field asp

User · Answer

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

User · Answer

This solution is from command line mysql  mysql gt USE information schema    In below query just change  lt --DATABASE NAME--  to your database and  lt --TABLENAME--  to your table name where you just want Field values of DESCRIBE statement  mysql gt  SELECT COLUMN NAME FROM COLUMNS WHERE TABLE SCHEMA     lt --DATABASE NAME-- gt   AND   TABLE NAME   lt --TABLENAME-- gt

[php] Get table column names in MySQL?

Examples related to php

Examples related to sql

Examples related to mysql