Main menu

MySQL - Get a list of table columns separated by comma

Published by dnovikov on Tue, 11/12/2013 - 08:56

In every MVC framework sometimes you need to create a list of MySQL table columns in order to use them in your model. You can simply copy-paste them from phpMyAdmin one by one. But once I needed to create several models at a time. Here is a simply query which lists all columns in a basic format, which can be used in PHP code later.

At first do USE information_schema, or switch to this database in your GUI tool.

The actual query is

SELECT CONCAT( "'", GROUP_CONCAT( COLUMN_NAME SEPARATOR "','" ) , "'" )

FROM COLUMNS

WHERE TABLE_SCHEMA = 'YOUR_DATABASE'

AND TABLE_NAME = 'YOUR_TABLE'

 

To create output ready to insert to PHP, simply manipulate with separators:

SELECT CONCAT( "'", GROUP_CONCAT( COLUMN_NAME SEPARATOR "' => array(),\n'" ) , "' => array()," )

FROM COLUMNS

WHERE TABLE_SCHEMA = 'YOUR_DATABASE'

AND TABLE_NAME = 'YOUR_TABLE'

Add new comment

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.