Rows, Cols, Queries and Arrays

[Krogman]

Please help..... :( I have been working on a page that displays data from a
database query. The query returns data like this: Herbicide1, Weed1,
Weed1Rating Herbicide1, Weed2, Weed2Rating Herbicide2, Weed1, Weed1Rating
Herbicide2, Weed2, Weed2Rating The way the database is designed, 1 Herbicide
can have ratings for multiple weeds: Herbicide1 - Weed1, Weed2, Weed3 Here is
the problem. I want to display this information in table, like this:
Herbicide1, Weed1, Weed1Rating, Weed2, Weed2Rating Herbicide2, Weed1,
Weed1Rating, Weed2, Weed2Rating To add a bit of a change, not all herbicides
have ratings for all weeds. So, a possible result set from a query could look
like this: Herbicide1, Weed1, Weed1Rating Herbicide2, Weed2, Weed2Rating
Herbicide3, Weed1, Weed1Rating Herbicide3, Weed3, Weed3Rating To get around
this problem, I have put the query into an array. I have tried to manipulate
the output page so the herbicides are displayed down the left side of the
table, and all the weeds are displayed in columns across the top of the page
with the weed rating values in the corresponding row and column. I have one
problem...... I can't figure out how to output the page based on a dynamic
number of weeds or herbicides returned from the database, or more simply put, I
can't figure it out :( Any help, references, links, tutorials, comments, words
of encouragement.... anything.... would be welcome :) Thank You... JK

[Kronin555]

So you want a table with all weeds across the top, and all herbicides down the
side, with the appropriate ratings being placed in the appropriate fields of
the table?

How's your database structured? what does your query look like? What does the
array look like that you're pushing it into?

These answers will greatly increase your chances of getting a beneficial
answer to your original question.

[Krogman]

Kronin555, thank you for your reply. Yes, an output table with all weeds
across the top, and all herbicides down the side, with the appropriate ratings
being placed in the appropriate fields of the table. Database Structure:
Herbicide Table with a one to many relationship to a ratings table. The ratings
table has a many to one relationship to the weeds table. Although this is how
the database is currently structured, it could be changed. Query is something
like this: SELECT herbicde.name, weed.name, ratings.rating, weed.ID FROM weed
RIGHT JOIN (herbicde RIGHT JOIN ratings ON herbicde.id = ratings.herbicde) ON
weed.ID = ratings.ratingID WHERE ((weed.ID =1) OR (weed.ID =2)) ORDER BY
herbicde.name ASC; In the query above, I have selected weed ratings for 2
weeds ( ID's 1 & 2) From this point I have put the query results into a 1D
array. <cfset WeedRating=ArrayNew(1)> <cfoutput query='qryGetRatings'>
<cfscript> { WeedRating[#qryGetRatings.CurrentRow#][1]=#name#; // herbicide
name WeedRating[#qryGetRatings.CurrentRow#][3]=#weed#; // weed name
WeedRating[#qryGetRatings.CurrentRow#][4]=#rating#; // herbicide weed rating }
</cfscript> </cfoutput> I have tried using a 2D array and a Structure, but I
just can't get my head around it. Just some hotes: I redesigned the database
to allow more flexibility in adding more herbicides and weeds to the database
as well as a few more parameters (I removed them for simplicity in the code
above). The first design worked, but everytime a change was made to a table or
feild name, the CFM would have to be modified. I guess you could say it isn't
really dynamic in terms of scalabilty. A working version of this application
can be viewed here (using the old DB design)
[url]http://www.ridgetownc.com/services/weeds_control.cfm[/url] Thanks Kronin555, JK