If what you are looking for is a list of the events that the client
attended, you can do this in 1 query:
<cfquery name="getClients" datasource="myDSN">
SELECT event.*
FROM events, eventAttendance, client
where client.clientID = <cfqueryparam value="#cookie.userid#"
cfsqltype="cf_sql_integer">
and client.clientID = eventAttendance.clientID
and eventAttendance.eventID = events.ID
</cfquery>
If
-----Original Message-----
From: Adam Parker [mailto:[email protected]]
Sent: Wednesday, February 25, 2009 7:18 AM
To: cf-newbie
Subject: Re: Translate from Classic ASP to CF
So now, maybe I have confused myself with this situation. I tried using the
same flow control as the previous developer, but I don't think it works very
well. In the big picture of things, I am associating clients with the
events they have attended. There are three MS Access tables involved with
the data:
EventAttendance
-clientID
-eventID
Client
-clientID
-firstname
-lastname
Events
-id
Events relates to EventAttendance through events.id=eventAttendace.eventID
Client relates to EventAttendance through
client.clientID=eventAttendance.clientID
The user logs in and his userid is captured and set with a cookie. Next, I
use a query to find all events that this user has attended:
<cfset cookie.userid="8443">
<cfquery name="getClients" datasource="myDSN">
SELECT eventAttendance.*, client.*
FROM eventAttendance INNER JOIN client ON eventAttendance.clientID =
client.clientID
where client.clientID=#cookie.userid#
</cfquery>
As suggested, I have used an array to collect and make a list of all the
events attended associated with the userid:
<cfif getClients.recordCount>
<cfset clientPreReg = valueList(getClients.eventID)>
<cfset clientPreRegArray = listToArray(clientPreReg)>
<cfelse>
<cfset clientPreReg = ",">
</cfif>
Now, I scratch my head on creating a query that gets the events based on the
event id. I have tried:
<cfloop index="i" array="clientPreReg">
<cfoutput>#i#<br /></cfoutput>
</cfloop>
<cfquery name="getEvents" datasource="myDSN">
SELECT * from events
where id= #i#
</cfquery>
ClientPreReg is collecting the eventID in a list, now I need to use the
individual elements in the list to compare its value to events.id
I thought looping through the array and getting each element was the way. I
feel like I'm on the right track, but, obviously, doing something wrong.
I also thought querying all the data in a massive query would solve the
problem too, but I need to tackle this one step at a time.
Thanks, again, for your wisdom. (As a sidenote, I am querying all records
and omitting cfqueryparam at this time just for testing.)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:4402
Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
