MySQL query to get column names

Question

I d like to get all of a mysql table s col names into an array in php   Is there a query for this

User · Answer

You can use the following query for MYSQL:

SHOW `columns` FROM `your-table`;

Below is the example code which shows How to implement above syntax in php to list the names of columns:

$sql = "SHOW COLUMNS FROM your-table";
$result = mysqli_query($conn,$sql);
while($row = mysqli_fetch_array($result)){
    echo $row['Field']."<br>";
}

For Details about output of SHOW COLUMNS FROM TABLE visit: MySQL Refrence.

User · Answer

i no expert  but this works for me     sql    desc MyTable    result    mysql query  sql   while  row    mysql fetch array  result        echo  row 0    lt br gt       returns the first column of array  in this case Field           the below code will return a full array- gt  Field Type Null Key Default Extra              for   c 0  c lt sizeof  row   c    echo   row  c    lt br gt

User · Answer

Edit  Today I learned the better way of doing this  Please see ircmaxell s answer     Parse the output of SHOW COLUMNS FROM table   Here s more about it here  http   dev mysql com doc refman 5 0 en show-columns html

User · Answer

I have done this in the past   SELECT column name FROM information schema columns WHERE table name  insert table name here

User · Answer

Not sure if this is what you were looking for  but this worked for me    query   query  DESC YourTable       col names   array column  query   Field      That returns a simple array of the column names   variable names in your table or array as strings  which is what I needed to dynamically build MySQL queries  My frustration was that I simply don t know how to index arrays in PHP very well  so I wasn t sure what to do with the results from DESC or SHOW  Hope my answer is helpful to beginners like myself   To check result   print r  col names

User · Answer

This query fetches a list of all columns in a database without having to specify a table name  It returns a list of only column names    SELECT COLUMN NAME   FROM INFORMATION SCHEMA COLUMNS   WHERE table schema    db name    However  when I ran this query in phpmyadmin  it displayed a series of errors  Nonetheless  it worked  So use it with caution

User · Answer

Seems there are 2 ways   DESCRIBE  tablename    or  SHOW COLUMNS FROM  tablename    More on DESCRIBE here  http   dev mysql com doc refman 5 0 en describe html

User · Answer

SHOW COLUMNS in mysql 5 1  not 5 5  uses a temporary disk table    http   dev mysql com doc refman 5 1 en internal-temporary-tables html http   dev mysql com doc refman 5 1 en show-columns html   So it can be considered slow for some cases  At least  it can bump up your created tmp disk tables value  Imagine one temporary disk table per connection or per each page request   SHOW COLUMNS is not really so slow  possibly because it uses file system cache  Phpmyadmin says  0 5ms consistently  This is nothing compared to 500ms-1000ms of serving a wordpress page  But still  there are times it matters  There is a disk system involvement  you never know what happens when server is busy  cache is full  hdd is stalled etc   Retrieving column names through SELECT   FROM     LIMIT 1 was around  0 1ms  and it can use query cache as well   So here is my little optimized code to get column names from a table  without using show columns if possible   function db columns ar  table      returns Array  col1name   gt  col1name   col2name   gt  col2name       if   table  return Array    if  is string  table   return Array     global  db columns ar cache  if  empty  db columns ar cache  table        return  db columns ar cache  table       IMPORTANT show columns creates a temp disk table  cols Array     row db row ar  q1  SELECT   FROM   table  LIMIT 1    if  row            foreach  row as  name  gt  val           cols  name   name        else            coldata db rows  q2  SHOW COLUMNS FROM   table         if  coldata          foreach  coldata as  row               cols  row- gt Field   row- gt Field         db columns ar cache  table   cols    debugexit  q1  q2  row  coldata  cols   return  cols      Notes    As long as your tables first row does not contain megabyte range of data  it should work fine  The function names db rows and db row ar should be replaced with your specific database setup

User · Answer

Use mysql fetch field   to view all column data   See manual    query    select   from myfield    result   mysql query  query    i   0  while   i  lt  mysql num fields  result         fld   mysql fetch field  result   i       myarray    fld- gt name      i    i   1       Warning This extension is deprecated as of PHP 5 5 0  and will be removed in the future

User · Answer

The best way is to use the INFORMATION SCHEMA metadata virtual database   Specifically the INFORMATION SCHEMA COLUMNS table     SELECT  COLUMN NAME   FROM  INFORMATION SCHEMA   COLUMNS   WHERE  TABLE SCHEMA   yourdatabasename       AND  TABLE NAME   yourtablename     It s VERY powerful  and can give you TONS of information without need to parse text  Such as column type  whether the column is nullable  max column size  character set  etc      Oh  and it s standard SQL  Whereas SHOW     is a MySQL specific extension      For more information about the difference between SHOW    and using the INFORMATION SCHEMA tables  check out the MySQL Documentation on INFORMATION SCHEMA in general

User · Answer

if you use php  use this gist   it can get select fields full info with no result and all custom fields such as   SELECT a name aname  b name bname  b    FROM table1 a LEFT JOIN table2 b ON a id   b pid    if above sql return no data will also get the field names aname  bname  b s other field name  just two line    query info   mysqli query  link   data source    fetch fields result    query info- gt fetch fields

User · Answer

This question is old  but I got here looking for a way to find a given query its field names in a dynamic way  not necessarily only the fields of a table   And since people keep pointing this as the answer for that given task in other related questions  I m sharing the way I found it can be done  using Gavin Simpson s tips     Function to generate a HTML table from a SQL query function myTable  obConn  sql         rsResult   mysqli query  obConn   sql  or die mysqli error  obConn        if mysqli num rows  rsResult  gt 0                  We start with header   gt  gt  gt Here we retrieve the field names lt  lt  lt          echo   lt table width   100    border   0   cellspacing   2   cellpadding   0   gt  lt tr align   center   bgcolor    CCCCCC   gt             i   0          while   i  lt  mysqli num fields  rsResult                field   mysqli fetch field direct  rsResult   i               fieldName  field- gt name             echo   lt td gt  lt strong gt  fieldName lt  strong gt  lt  td gt                i    i   1                    echo   lt  tr gt                gt  gt  gt Field names retrieved lt  lt  lt             We dump info          bolWhite true          while   row   mysqli fetch assoc  rsResult                 echo  bolWhite     lt tr bgcolor    CCCCCC   gt       lt tr bgcolor    FFF   gt                 bolWhite   bolWhite              foreach  row as  data                    echo   lt td gt  data lt  td gt                              echo   lt  tr gt                      echo   lt  table gt              This can be easily modded to insert the field names in an array   Using a simple   sql  SELECT   FROM myTable LIMIT 1  can give you the fields of any table  without needing to use SHOW COLUMNS or any extra php module  if needed  removing the data dump part    Hopefully this helps someone else

User · Answer

The simplest solution out of all Answers   DESC  table name    or  DESCRIBE  table name    or  SHOW COLUMNS FROM  table name

User · Answer

when you want to check your all table structure with some filed then use this code  In this query i select column name column type and table name for more details   I use order by column type so i can see it easily    SELECT  COLUMN NAME  COLUMN TYPE TABLE NAME  FROM  INFORMATION SCHEMA   COLUMNS   WHERE  TABLE SCHEMA   yourdatabasename  order by DATA TYPE    If you want to check only double type filed then you can do it easily   SELECT  COLUMN NAME  COLUMN TYPE TABLE NAME DATA TYPE  FROM  INFORMATION SCHEMA   COLUMNS   WHERE  TABLE SCHEMA   yourdatabasename  AND DATA TYPE like   bigint    order by DATA TYPE      if you want to check which field allow null type etc then you can use this   SELECT  COLUMN NAME  COLUMN TYPE TABLE NAME IS NULLABLE DATA TYPE  FROM  INFORMATION SCHEMA   COLUMNS   WHERE  TABLE SCHEMA   yourdatabasename  and DATA TYPE like   bigint   and IS NULLABLE   NO  order by COLUMN TYPE      you want to check more then thik link also help you   https   dev mysql com doc refman 5 7 en columns-table html

User · Answer

IN WORDPRESS   global  wpdb     table name  wpdb- gt prefix  posts   foreach    wpdb- gt get col   DESC      table name  0   as  column name       var dump   column name

User · Answer

if you only need the field names and types  perhaps for easy copy-pasting into Excel    SELECT COLUMN NAME  DATA TYPE FROM INFORMATION SCHEMA COLUMNS WHERE TABLE SCHEMA  databasenamegoeshere  AND DATA TYPE  decimal  and TABLE NAME    tablenamegoeshere    remove   DATA TYPE  decimal    if you want all data types

User · Answer

Try this one out I personally use it   SHOW COLUMNS FROM  table where field REGEXP  stock id drug name

User · Answer

The call of DESCRIBE is working fine to get all columns of a table but if you need to filter on it  you need to use the SHOW COLUMNS FROM instead  Example of PHP function to get all info of a table      get table columns  or return false if table not found  function get table columns  db   table             global  pdo       if  cols    pdo- gt query  quot DESCRIBE   db    table  quot              if  cols    cols- gt fetchAll PDO  FETCH ASSOC                 return  cols                           return false     In my case  I had to find the primary key of a table  So  I used   SHOW COLUMNS FROM  table  WHERE  Key   PRI    Here is my PHP function      get table Primary Key function get table pk  db   table         global  pdo                    q    quot SHOW COLUMNS FROM   db    table  WHERE  Key     PRI  quot       if  cols    pdo- gt query  q             if  cols    cols- gt fetchAll PDO  FETCH ASSOC                 return  cols 0                            return false

User · Answer

I have tried this query in SQL Server and this worked for me    SELECT name FROM sys columns WHERE OBJECT ID   OBJECT ID  table name

User · Answer

function get col names           sql    SHOW COLUMNS FROM tableName          result   mysql query  sql            while  record   mysql fetch array  result            fields      record  0                foreach   fields as  value           echo  column name is      value  -               return get col names

User · Answer

An old PHP function  mysql list fields    is deprecated  So  today the best way to get names of fields is a query  SHOW COLUMNS FROM table name  LIKE  name     So  here is a little example    fields   array     res mysql query  SHOW COLUMNS FROM mytable    while   x   mysql fetch assoc  res       fields      x  Field      foreach   fields as  f    echo   lt br gt Field name     f

[php] MySQL query to get column names?

Examples related to php

Examples related to mysql