[thelist] CF - multiple groups, and SQL too perhaps

isaac,

  Are you sure you can't nest multiple groups?  I thought you could.

  Probably a typo, but in your code you are grouping by auctionid, but that
field is not in your ORDER BY clause.  All groupings must be in your ORDER
BY statement.

Chris Evans
chris@fuseware.com
http://www.fuseware.com


-----Original Message-----
From: thelist-admin@lists.evolt.org
[mailto:thelist-admin@lists.evolt.org]On Behalf Of isaac forman
Sent: Wednesday, March 22, 2000 12:56 AM
To: thelist
Subject: [thelist] CF - multiple groups, and SQL too perhaps



hi,

i'm trying to do multiple groups within a CF application, but not having
much
luck. the solution might be something simple in CF or something within my
query
that i should try.

anyway, here's the relevant query:

	<cfquery name="getreviews" datasource="#db#">
	select *
	from review, auction, location, member
	where locationactive = 1
	and auctionactive = 1
	and reviewapproved = 1
	and review.auctionid = auction.auctionid
	and auction.locationid = location.locationid
	and review.memberid = member.memberid
	order by locationname, auctiontitle, reviewdate;
	</cfquery>


(rudy, how bad is my SQL?)

im grabbing some stuff from the member table (names of the review author,
etc).
the other tables are the important ones though.

the situation is this: i have multiple locations. each location has multiple
auctions. each auction has multiple reviews. i'm hoping to display the
resulting
data like so:

	 - location1
	     - auction1
	        - review1
	     - auction2
	        - review2
	        - review3
	        - review4
	 - location2
	     - auction3
	        - review5
	        - review6
		    etc

essentially, it's a 3 level <ul>

now, 2 level is easy:

	<ul>
	<cfoutput query=getreviews group=auctionid>
	<li><b>#auctiontitle#</b></li>
	<ul>

	<cfoutput>
	<li><a href="reviewdetails.cfm?reviewid=#reviewid#">#reviewtitle#</a>
	(by <cfif #len("#reviewnameoverride#")# is 0>
	#memberfirstname# #memberlastname#<cfelse>#reviewnameoverride#</cfif>
	on #dateformat("#reviewdate#","dd/mm/yyyy")#)</li>
	</cfoutput>

	</ul>
	</cfoutput>
	</ul>

but what about 3 level?

i can't put a <cfoutput group=locationid> around the above code because CF
only
allows the one group, right? and i can't nest <cfoutput>'s unless the outer
one
has a group or query attribute. and then a <cfoutput> with a query attribute
cannot be enclosed by another, etc.

any ideas?

should i be using "group by location.locationid" within my sql? something
like
that?


tia,


isaac



---------------------------------------
For unsubscribe and other options, including
the Tip Harvester and archive of TheList go to:
http://lists.evolt.org Workers of the Web, evolt !