Get table column names in MySQL?

sql

column-names

wildcards

php

Instantly retrieve MySQL table column names with:

SELECT `COLUMN_NAME`
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'dbName' AND `TABLE_NAME` = 'tableName';

Replace dbName and tableName with your actual database and table names. This hands you the column names on a silver platter.

Quick and dirty alternatives

For a fast overview of column names, you can:

Use SHOW COLUMNS FROM tableName;. This one is like saying "Show me what you got!" to your database.
Or, you can also try DESCRIBE tableName;. This one is like saying "Tell me everything about yourself!" to the table.

Remember, DESCRIBE will get you more than just column names. It is indeed TMI (Too much information)!

Sometimes, you might want a comma-separated list of column names. We got you:

SELECT GROUP_CONCAT(`COLUMN_NAME`)
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'your_schema' AND `TABLE_NAME` = 'your_table';

Substitute your_schema and your_table to fit your needs. Borrowing from Lord of the Rings, let's call this: "One string to rule them all!"

You might also find these tips helpful:

When searching for columns with similar names, wildcards got your back: AND COLUMN_NAME LIKE 'pattern%';
To list columns belonging to a specific database only, you just say: SHOW COLUMNS FROM your_db_name.your_table_name;

For repetitive tasks, consider the following:

Wildcard searches lets you get more Scooby Snacks with fewer commands.
A PHP function for column name extraction protects you from the SQL injection monster.
MySQL command-line becomes your secret detective weapon when you prefix information_schema.COLUMNS.

When writing PHP scripts that interact with MySQL databases:

Use PDO (PHP Data Objects) for a cleaner, safer and simpler database experience.
Fetch your result sets using PDO::FETCH_ASSOC to get a neat associative array.