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

Look into:

mysql_query('DESCRIBE '.$table);

Answer 2

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

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

There's also this if you prefer:

mysql_query('SHOW COLUMNS FROM tableName');

Answer 5

Hi,

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 6

You can use DESCRIBE:

DESCRIBE my_table;

Or in newer versions you can use INFORMATION_SCHEMA:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA WHERE TABLE_NAME = 'my_table';

Or you can use SHOW COLUMNS:

SHOW COLUMNS FROM my_table;

Answer 7

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