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?

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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.

Answer 6

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

$query = 'select * from myfield';
$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."