cfoutput grouping

[MorganGreylock]

Hey gang... got a bit of a problem. I have 3 tables (sites, students and requests). Students put in requests for particular sites, and the director will assign each student to a site for a particular month. In the process of making requests, they make one called 'gottahave' which is their
number 1 primary I-have-to-work-with-this-person request.

I am supposed to take each site and fill in the first month (starts in August) with all the students who picked that person as a gottahave. Then he can go through and reassign them based on certain criteria.

For example:

Heres what some students might request:

Student # gottahave
1 58
2 388
3 45
12 230
36 58

Etc. That means that student # 1 wants to work with preceptor # 58, etc. (unique IDs)

I have that part down fine. Now, the director wants to
look at the requests and make the assignments on the same
page. I used two scrollable layers for this, and so far that
paradigm seems to work. I'll use URL variables so he can just click on a student and then click on a site and make the assignment. The problem is when I want to have all the students who picked preceptor #58 to show in August. Using previous example, it should look like this:

Site August

45 Student 3
58 Student 1
Student 36
230 Student 12

Does that make sense? If not, I can probably elaborate more...

Basically I want to do this:

List all sites
For each site, list all students who picked
this site as its 'gottahave' under 'August'


I've tried using groups and sorting, sql joins, and lots of other hair-pulling methods, and I'm just not really sure where I should be concentrating, rather in the query or in the cfoutput, or what.

Please help.. =)

MG

 

[philhege]

When you say "fill in the first month", do you mean that you will have fields named after months? What happens when the new year comes? Do you keep more than one year of data? Not clear on this piece.

I assume your basic structure is students ->one to many-><-many to many<-requests->many to many-><-one to many<-sites.

so you have a "table in the middle" (requests) that's like

ID StudentFK SiteFK
1 58 136
2 35 45
...

Forgetting about months for a moment: issue a query

select siteID, site_name,student_name
FROM Sites JOIN Requests on siteFK = siteID
JOIN Students on studentID = StudentFK
ORDER BY siteID (or site name), student_name

For display, you can use the GROUP attribute of the CFOUTPUT tag:

<CFOUTPUT query="SiteRequests" GROUP="siteID">
Site:#site_name#
Requestors:<br>
<cfoutput>
#student_name#<br>
</cfoutput>
</cfoutput>

It's a short leap from there to your "month" assignment.

HTH,




Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....