Get table column names in MySQL?

Question

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

Answer 1

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

Answer 2

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] => name
    [1] => parent
    [2] => number
    [3] => chart_id
    [4] => type
    [5] => id
)

Answer 3

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

Answer 4

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.

Answer 5

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';
}