Mysql get field names from table
I am currently developing a backend generator in PHP / MySQL. When the project is completed you will:
- Create the database in the schema builder from within the application
- Define the relationships between the fields (1-n relationship, M-N, etc)
- Define some attributes for the table (display name, privileges etc)
- Define some attributes each field (display name, privileges, callbacks, etc)
and you would have a full blown backend to manage your data, without needing to write a single piece of code. I am really excited about it and I believe it will be very useful to someone. In the meantime, at some point in order to make the magic happen I needed to get the field names for a table and insert them into a table of my own, in order to store the metadata that I need to create the application.
Now you are probably familiar with
SHOW COLUMNS FROM tablename
but unfortunately running this query you also a get a lot of other information. In order to get only the fieldnames for a table you can execute this query:
SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'database' AND TABLE_NAME='table'
This way you can select the columns that you want to select, in our case we only need the COLUMN_NAME, and you can even insert them into an other table like so
INSERT INTO meta_table (field_name) VALUES SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'database' AND TABLE_NAME='table'
which I think is very cool