CODEWITHSUNDEEP
phpmysql
Haroldo
Haroldo

Reputation: 37377

MySQL query to get column names?

I'd like to get all of a MySQL table's column names into an array in PHP.

Is there a query for this?

Upvotes: 372

Views: 751301

Answers (23)

Gavin Simpson
Gavin Simpson

Reputation: 2832

Use mysql_fetch_field() to view all column data. See manual.

$query = 'select * from myfield LIMIT 1';
$result = mysql_query($query);
$i = 0;
while ($i < mysql_num_fields($result))
{
   $fld = mysql_fetch_field($result, $i);
   $myarray[]=$fld->name;
   $i = $i + 1;
}

"Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future."

Upvotes: 6

Mar&#231;al Torroella
Mar&#231;al Torroella

Reputation: 142

Extending to ircmaxell answer, If you ever want to run a select/insert sentence using all the columns from a table (it may be the reason your are browsing this topic, like me) you can use the group_concat function like this:

SELECT group_concat(`COLUMN_NAME`)
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='databaseName' 
    AND `TABLE_NAME`='tableName';

It will return "field1,field2,field3,fieldN" which is handy for this kind of operations.

Upvotes: 1

bcosca
bcosca

Reputation: 17555

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.

Upvotes: 274

Surreal Dreams
Surreal Dreams

Reputation: 26380

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

Upvotes: 70

sam kihonge
sam kihonge

Reputation: 221

this generates a string of column names with a comma delimiter:

    SELECT CONCAT('(',GROUP_CONCAT(`COLUMN_NAME`),')') 
    FROM `INFORMATION_SCHEMA`.`COLUMNS` 
    WHERE `TABLE_SCHEMA`='database_name' 
    AND `TABLE_NAME`='table_name';

Upvotes: 8

Meloman
Meloman

Reputation: 3722

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->query("DESCRIBE `$db`.`$table`")) {
        if($cols = $cols->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 = "SHOW COLUMNS FROM `$db`.`$table` WHERE `Key` = 'PRI'";
    if($cols = $pdo->query($q)) {
        if($cols = $cols->fetchAll(PDO::FETCH_ASSOC)) {
            return $cols[0];
        }
    }
    
    return false;
}

Upvotes: 0

Purvi Barot
Purvi Barot

Reputation: 269

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')

Upvotes: -1

Amir Hajiha
Amir Hajiha

Reputation: 935

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

Upvotes: 2

Shafiqul Islam
Shafiqul Islam

Reputation: 5690

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;

enter image description here

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;

enter image description here

you want to check more then thik link also help you.

https://dev.mysql.com/doc/refman/5.7/en/columns-table.html

Upvotes: 5

Gil Baggio
Gil Baggio

Reputation: 14003

The simplest solution out of all Answers:

DESC `table name`

or

DESCRIBE `table name`

or

SHOW COLUMNS FROM `table name`

Upvotes: 7

user6275623
user6275623

Reputation:

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.

Upvotes: 2

Ad Kahn
Ad Kahn

Reputation: 579

i no expert, but this works for me..

$sql = "desc MyTable";
$result = @mysql_query($sql);
while($row = @mysql_fetch_array($result)){
    echo $row[0]."<br>"; // returns the first column of array. in this case Field

      // the below code will return a full array-> Field,Type,Null,Key,Default,Extra    
      // for ($c=0;$c<sizeof($row);$c++){echo @$row[$c]."<br>";}    

}

Upvotes: 1

zhi.yang
zhi.yang

Reputation: 435

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->fetch_fields();

Upvotes: 2

El Gucs
El Gucs

Reputation: 967

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)>0)
    {
        //We start with header. >>>Here we retrieve the field names<<<
        echo "<table width=\"100%\" border=\"0\" cellspacing=\"2\" cellpadding=\"0\"><tr align=\"center\" bgcolor=\"#CCCCCC\">";
        $i = 0;
        while ($i < mysqli_num_fields($rsResult)){
           $field = mysqli_fetch_field_direct($rsResult, $i);
           $fieldName=$field->name;
           echo "<td><strong>$fieldName</strong></td>";
           $i = $i + 1;
        }
        echo "</tr>"; 
        //>>>Field names retrieved<<<

        //We dump info
        $bolWhite=true;
        while ($row = mysqli_fetch_assoc($rsResult)) {
            echo $bolWhite ? "<tr bgcolor=\"#CCCCCC\">" : "<tr bgcolor=\"#FFF\">";
            $bolWhite=!$bolWhite;
            foreach($row as $data) {
                echo "<td>$data</td>";
            }
            echo "</tr>";
        }
        echo "</table>";
    }
}

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.

Upvotes: 2

mohsin
mohsin

Reputation: 41

Try this one out I personally use it:

SHOW COLUMNS FROM $table where field REGEXP 'stock_id|drug_name'

Upvotes: 3

ucb
ucb

Reputation: 147

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);

Upvotes: 3

T.Todua
T.Todua

Reputation: 56557

IN WORDPRESS:

global $wpdb;   $table_name=$wpdb->prefix.'posts';
foreach ( $wpdb->get_col( "DESC " . $table_name, 0 ) as $column_name ) {
  var_dump( $column_name );
}

Upvotes: 3

Johan
Johan

Reputation: 647

SHOW COLUMNS in mysql 5.1 (not 5.5) uses a temporary disk table.

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'=>'col1name','col2name'=>'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=>$val)
        $cols[$name]=$name;
    }
else
    {
    $coldata=db_rows($q2="SHOW COLUMNS FROM `$table`");
    if($coldata)
        foreach($coldata as $row)
            $cols[$row->Field]=$row->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.

Upvotes: 3

Abdul Gaffar Shah
Abdul Gaffar Shah

Reputation: 45

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();

Upvotes: 4

user2558588
user2558588

Reputation: 77

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 "<br>Field name: ".$f; }

Upvotes: 5

Jeff
Jeff

Reputation: 433

I have done this in the past.

SELECT column_name
FROM information_schema.columns
WHERE table_name='insert table name here';

Upvotes: 28

The Pixel Developer
The Pixel Developer

Reputation: 13430

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

Upvotes: 16

ircmaxell
ircmaxell

Reputation: 165271

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

Upvotes: 622

Related Questions