Okay this sounds a little complex but I need help with a script that will do this: there is a MySQL database that has 3 tables, each table has between 5 and 9 columns is there a way to create a loop that will DESCRIBE each table and display the results? The reason I want to use a loop for this, is that although there are three tables at the moment there will be more added quite soon, and on top of this, there will eventually be a possible 60 - 80 tables. all with a similar number of columns but the names of the columns are likely to be different because of the nature of the data being stored. I know this sounds complex but I need to use this so that when they are displayed I can create something else based on the what the overall output is.. any help gratefully recieved Boro Jimmy
Are you just trying to display the results of a cfquery dynamically or do you actually need detailed information about the table structure? There are different approaches depending on the goal. Without knowing more about your task: 1) You can use MySQL's information schema views to get information about database objects http://dev.mysql.com/doc/refman/5.0/en/columns-table.html 2) You can cfquery a table use getMetadata to retrieve column names, data types etc. You can then use array notation to display the query results without hardcoding the column names (ie psuedo example <cfoutput query="..."> <cfloop array="#arrayOfColumnNames#" index="columnName"> #yourQueryName[columnName][rowNumber]# </cfloop> </cfoutput> ) But a word to the wise, scripts that expose your database structure (tables, etc..) tend to invite sql injection attacks.
Are you just trying to display the results of a cfquery dynamically or do you actually need detailed information about the table structure? There are different approaches depending on the goal. Without knowing more about your task: 1) You can use MySQL's information schema views to get information about database objects http://dev.mysql.com/doc/refman/5.0/en/columns-table.html 2) You can cfquery a table use getMetadata to retrieve column names, data types etc. You can then use array notation to display the query results without hardcoding the column names (ie psuedo example <cfoutput query="..."> <cfloop array="#arrayOfColumnNames#" index="columnName"> #yourQueryName[columnName][rowNumber]# </cfloop> </cfoutput> ) But a word to the wise, scripts that expose your database structure (tables, etc..) tend to invite sql injection attacks.
I don't have anything for mysql, but I do have a routine for SQL server that will do what you are describing. If you are interested in it, let me know.
Hi thanks for the idea, fortunately this script will only be used for development purposes, (i may need to view the tables in certain situations) the script will only be used within an administration environment, (and only locally I might add!) I am unsure if it can be done as when the db is working in the column names are changed depending on which table it is. its a kind of inventory system, but because of the various columns that will be used to describe each table some tables will include columns that others wont. I am still unsure whether it would create better db integrity to include ALL the columns allowing every possible combination of table. what I mean is this (sorry if I ramble but I tend to type as I think) SupplierA ONLY provides hardware so there would be no need to have columns decribing books (ie fiction. non fiction etc) SupplierB is an internet only business that provides services and does not need some of the columns that a non internet based business i.e warehouse address or extra phone numbers. SupplierC is a restraunt so there is no need for any hardware columns. In the end there may be only around 4 or 5 basic types of table but some suppliers may have products that don't fit within these basic types and thats why it needs to describe the columns of each table and not the content so that it can bring up a list of what each supplier can supply and leave the column content to show what the the inventory is. The final script needs to display something like this: Table 1 Fiction | Non-Fiction | Biography | Auto-Biography Table 2 Carrots | Potatoes | Broccoli | Cabbage Table 3 Memory | CPU's | HDD's | Zip Drives (? why would I think of them lol) as you can see each table can be totally different and in the end in may be that the column names would be too numerous to keep naming as the script will end up bigger than the database!
Hi thanks for the idea, fortunately this script will only be used for development purposes, (i may need to view the tables in certain situations) the script will only be used within an administration environment, (and only locally I might add!) I am unsure if it can be done as when the db is working in the column names are changed depending on which table it is. its a kind of inventory system, but because of the various columns that will be used to describe each table some tables will include columns that others wont. I am still unsure whether it would create better db integrity to include ALL the columns allowing every possible combination of table. what I mean is this (sorry if I ramble but I tend to type as I think) SupplierA ONLY provides hardware so there would be no need to have columns decribing books (ie fiction. non fiction etc) SupplierB is an internet only business that provides services and does not need some of the columns that a non internet based business i.e warehouse address or extra phone numbers. SupplierC is a restraunt so there is no need for any hardware columns. In the end there may be only around 4 or 5 basic types of table but some suppliers may have products that don't fit within these basic types and thats why it needs to describe the columns of each table and not the content so that it can bring up a list of what each supplier can supply and leave the column content to show what the the inventory is. The final script needs to display something like this: Table 1 Fiction | Non-Fiction | Biography | Auto-Biography Table 2 Carrots | Potatoes | Broccoli | Cabbage Table 3 Memory | CPU's | HDD's | Zip Drives (? why would I think of them lol) as you can see each table can be totally different and in the end in may be that the column names would be too numerous to keep naming as the script will end up bigger than the database!
I think this is what you want: <cfquery datasource="test" name="z"> select * from myTable </cfquery> <cfset qMetaData = getmetadata(z)> <!--- lets loop over the metaData and select the values from the query ---> <br /> <cfloop from="1" to="#arrayLen(qMetaData)#" index="loopCount"> <cfoutput>Field name: <strong>#qMetaData[loopcount].name#</strong> Field Type: <strong>#qMetaData[loopCount].typeName#</strong> <br /></cfoutput> </cfloop> Select * from a table you know nothing about. Loop the meta data extract the data type and the name of the column. If you add a new field to the table it will just work.