By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,229 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

how to get field names from multiple tables in a single query

P: 13
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?
Jul 27 '07 #1
Share this Question
Share on Google+
4 Replies


mwasif
Expert 100+
P: 801
Moved from MySQL Articles to MySQL Forum. Kindly ask your MySQL related questions in MySQL Forum.
Jul 28 '07 #2

mwasif
Expert 100+
P: 801
If you have MySQL 5 try INFORMATION_SCHEMA.
Jul 28 '07 #3

P: 13
Unfortunately, I'm stuck with MySQL 4.1.14 and cannot upgrade (shared hosting account). Otherwise, your suggestion would easily work. Thanks for taking the time to respond though.

ps. Oops...didn't realize I was putting this in as an article. Will pay more attention next time.
* img.lightbulb { cursor: pointer; }
img.lightbulb { cursor: pointer; }
Aug 4 '07 #4

P: 1
If anyone like me is still looking for a solutions to this problem, what I did was: once I did the query (any) I look up through the result record as an array. The key values are the columns names. That works in PHP :)
Aug 1 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.