MySql – get names of columns and put them in an array

Sometimes I need to get all the names of the columns in an sql table, most often to use the names of the columns as names of variables as it’s easier to use than simple $row[1], $row[2], etc. Let’s take this WordPress table ‘wp_comments’ as an example:

Field Type Null Key Default Extra
comment_ID bigint(20) unsigned NO PRI NULL auto_increment
comment_post_ID int(11) NO MUL 0
comment_author tinytext NO NULL
comment_author_email varchar(100) NO
comment_author_url varchar(200) NO
comment_author_IP varchar(100) NO
comment_date datetime NO 0000-00-00 00:00:00
comment_date_gmt datetime NO MUL 0000-00-00 00:00:00
comment_content text NO NULL
comment_karma int(11) NO 0
comment_approved varchar(20) NO MUL 1
comment_agent varchar(255) NO
comment_type varchar(20) NO
comment_parent bigint(20) NO 0
user_id bigint(20) NO 0

If you want to use the names of the columns as the names of variables returned by a database query, you’d have to copy&paste them from the table one by one, which is time consuming and very inconvenient, of course. This simple code:


$column_names = array();
$query = "SHOW COLUMNS FROM wp_comments";
$result = mysql_query($query); 

while($column = mysql_fetch_array($result)) {
	$column_names[] = $column[0];
$columns_string = implode(', $', $column_names);
print $columns_string;

will print out a list of variables based on the column names of the given table. You can take all the names at once now and paste the list here instead of doing it one by one.

        $user_id) = $row;

Leave a Reply