What is the syntax in mysql to get the column names of a table?

Question

Can somebody please enlighten me on the use of SHOW COLUMNS specifically in regards to PHP/MySQL because I have not found anything of much use through search.

What I want to do is get all the column names out of a table, then use those to get the correct data from session to do an update.

what i have so far is:

function get_Columns($con, $table){

    $sql_statement = "SHOW COLUMNS FROM " . NAME_TABLE_BASE . $table ;

    $temp = mysql_query($sql_statement) or die(mysql(error)) ;

    return mysql_fetch_array($temp) ;
}
?>

The problem is this is only returning the first column and not all of them. What do I need to change? Also is there anything I can add to $sql_statement to only get the fields and not all the other info like key type etc?

Marc B · Accepted Answer · 2011-04-28 22:34:29Z

3

SHOW COLUMNS ... is the oldschool method. For MySQL v5.x installs you can query the database's INFORMATION_SCHEMA database directly for anything related to the databases/tables/fields it's hosting:

select COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where (TABLE_SCHEMA = 'your database') AND (TABLE_NAME = 'your table')

There's many more fields with specific field data in that table as well, documented here.

answered Apr 28, 2011 at 22:34

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Magic Lasso Over a year ago

I think I still have a 4.x vers :(

Marc B Over a year ago

select version(); will tell you

Halcyon · Accepted Answer · 2011-04-28 21:34:44Z

2

Put it in a while loop to fetch the other column names

$sql_statement = "SHOW COLUMNS FROM " . NAME_TABLE_BASE . $table;
$temp = mysql_query($sql_statement) or die(mysql(error));

while($row = mysql_fetch_array($temp)) {
    print_r($row);
}

answered Apr 28, 2011 at 21:34

Halcyon

57.7k10 gold badges93 silver badges131 bronze badges

1 Comment

Magic Lasso Over a year ago

Interesting. I tried getting the array and using a foreach but got nothing but the first column. Ill give this a go. Thanks.

Collectives™ on Stack Overflow

What is the syntax in mysql to get the column names of a table?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related