{"id":517,"date":"2011-03-26T19:09:36","date_gmt":"2011-03-26T18:09:36","guid":{"rendered":"https:\/\/www.hutsky.cz\/blog\/?p=517"},"modified":"2013-12-29T20:54:39","modified_gmt":"2013-12-29T19:54:39","slug":"mysql-get-names-of-columns-and-put-them-in-an-array","status":"publish","type":"post","link":"https:\/\/www.hutsky.cz\/blog\/2011\/03\/mysql-get-names-of-columns-and-put-them-in-an-array\/","title":{"rendered":"MySql – get names of columns and put them in an array"},"content":{"rendered":"

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:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Field<\/th>\nType<\/th>\nNull<\/th>\nKey<\/th>\nDefault<\/th>\nExtra<\/th>\n<\/tr>\n<\/thead>\n
comment_ID<\/td>\nbigint(20) unsigned<\/td>\nNO<\/td>\nPRI<\/td>\nNULL<\/i><\/td>\nauto_increment<\/td>\n<\/tr>\n
comment_post_ID<\/td>\nint(11)<\/td>\nNO<\/td>\nMUL<\/td>\n0<\/td>\n<\/td>\n<\/tr>\n
comment_author<\/td>\ntinytext<\/td>\nNO<\/td>\n<\/td>\nNULL<\/i><\/td>\n<\/td>\n<\/tr>\n
comment_author_email<\/td>\nvarchar(100)<\/td>\nNO<\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/tr>\n
comment_author_url<\/td>\nvarchar(200)<\/td>\nNO<\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/tr>\n
comment_author_IP<\/td>\nvarchar(100)<\/td>\nNO<\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/tr>\n
comment_date<\/td>\ndatetime<\/td>\nNO<\/td>\n<\/td>\n0000-00-00 00:00:00<\/td>\n<\/td>\n<\/tr>\n
comment_date_gmt<\/td>\ndatetime<\/td>\nNO<\/td>\nMUL<\/td>\n0000-00-00 00:00:00<\/td>\n<\/td>\n<\/tr>\n
comment_content<\/td>\ntext<\/td>\nNO<\/td>\n<\/td>\nNULL<\/i><\/td>\n<\/td>\n<\/tr>\n
comment_karma<\/td>\nint(11)<\/td>\nNO<\/td>\n<\/td>\n0<\/td>\n<\/td>\n<\/tr>\n
comment_approved<\/td>\nvarchar(20)<\/td>\nNO<\/td>\nMUL<\/td>\n1<\/td>\n<\/td>\n<\/tr>\n
comment_agent<\/td>\nvarchar(255)<\/td>\nNO<\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/tr>\n
comment_type<\/td>\nvarchar(20)<\/td>\nNO<\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/tr>\n
comment_parent<\/td>\nbigint(20)<\/td>\nNO<\/td>\n<\/td>\n0<\/td>\n<\/td>\n<\/tr>\n
user_id<\/td>\nbigint(20)<\/td>\nNO<\/td>\n<\/td>\n0<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

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:<\/p>\n

 <\/p>\n

$column_names = array();\r\n$query = \"SHOW COLUMNS FROM wp_comments\";\r\n$result = mysql_query($query); \r\n\r\nwhile($column = mysql_fetch_array($result)) {\r\n\t$column_names[] = $column[0];\r\n\t}\r\n$columns_string = implode(', $', $column_names);\r\nprint $columns_string;<\/pre>\n
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.<\/p>\n
list($comment_ID,\r\n        $comment_post_ID,\r\n        $comment_author,\r\n        $comment_author_email,\r\n        $comment_author_url,\r\n        $comment_author_IP,\r\n        $comment_date,\r\n        $comment_date_gmt,\r\n        $comment_content,\r\n        $comment_karma,\r\n        $comment_approved,\r\n        $comment_agent,\r\n        $comment_type,\r\n        $comment_parent,\r\n        $user_id) = $row;<\/pre>\n","protected":false},"excerpt":{"rendered":"
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 …<\/p>\n
  MySql – get names of columns and put them in an array<\/span> Read More »<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[32,22],"tags":[25,4],"class_list":["post-517","post","type-post","status-publish","format-standard","hentry","category-programming","category-web-related","tag-mysql","tag-php"],"_links":{"self":[{"href":"https:\/\/www.hutsky.cz\/blog\/wp-json\/wp\/v2\/posts\/517","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.hutsky.cz\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.hutsky.cz\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.hutsky.cz\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.hutsky.cz\/blog\/wp-json\/wp\/v2\/comments?post=517"}],"version-history":[{"count":25,"href":"https:\/\/www.hutsky.cz\/blog\/wp-json\/wp\/v2\/posts\/517\/revisions"}],"predecessor-version":[{"id":946,"href":"https:\/\/www.hutsky.cz\/blog\/wp-json\/wp\/v2\/posts\/517\/revisions\/946"}],"wp:attachment":[{"href":"https:\/\/www.hutsky.cz\/blog\/wp-json\/wp\/v2\/media?parent=517"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.hutsky.cz\/blog\/wp-json\/wp\/v2\/categories?post=517"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.hutsky.cz\/blog\/wp-json\/wp\/v2\/tags?post=517"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}