I have a MySQL database containing 16 tables of data. Each table has a different number of columns and there are few common field names accross tables. I do have one master table with which I connect the tables together with various queries using a unique id. My task is to generate reports from these tables dynamically. The user will select the data they want to see and the script will export a spreadsheet containing this data. I've got the front end completed (eg. the GUI they will use to select the data from the tables). I've got the export part completed (eg. the part that formats the data appropriately and exports it to a spreadsheet). I've got most of the rest of the script ready to go as far as sanitizing the input, building the queries, and pulling the data. The part I'm stuck on is this:
I do not know ahead of time what tables or columns will be selected. In order to build my queries, I need to get a list of the available columns from the selected tables. I've only found one way of accomplishing this task and that is to use "SHOW COLUMNS FROM table_name" on all the selected tables and push them into an array. This is horribly inefficient and I am hoping that someone may have a better way.
What I'm looking for is a way to combine these two statements:
DEFINE (SELECT * FROM table1,table2,table3,etc.)
and
SHOW COLUMNS FROM table_name
I've tried to join and union the SHOW COLUMNS but that produces an error and so does the same thing using DEFINE table_name.
Is there a way to select all the field names from multiple tables in a single query?