if/loop/array question

[Dax Arroway]

I'm trying to display an add or delete button dependent upon logged in user and
I can't figure it out. Can someone please help with the methodology?

I've got a table with users who log on to the site. I have a list of meetings
for people to sign up to. I'm trying to make it so if the logged on user isn't
signed up, an Add button is displayed and if the logged on user is all ready
signed up, a Delete button is displayed.

What I've got is:
<cfif UserID EQ Session.UserID>
Display Del Button Form which deletes the user from the meeting table.
<cfelse>
Display Add Button Form which adds the Session.UserID to the table.
</cfif>

This works fine if there is only one person signed up for the meeting. When
there are two or more people signed up for the meeting, the value that's
returned from UserID are the IDs for all the users attending. In which case,
UserID will never EQ Session.UserID. So what I've got is someone who can sign
up for a meeting but can't remove themselves.

I'm guessing that if I can get the cfif statement to validate each record
separately, then maybe this would work but everything I've tried doesn't. One
thought was to wrap the cfif statement in output tags, which works, however
this presents another problem. What I get is a button for each person coming.
So if 8 people are coming, I get 7 add buttons and one remove button. I tried
setting a value to yes if UserID EQ Session.UserID and no if it didn't, then
looping through the list and then running a cfif statement after it like this:
<cfoutput>
<cfloop list="#UserID#" index="i">
<cfif i = Session.UserID>
<cfset value="yes">
<cfelse>
<cfset value="no">
</cfif></cfloop></cfoutput>
<cfif value="yes">
Display Delete button Form
<cfelse>
Display Add button Form
</cfif>
but that didn't work either. It works, but I still get an add button for all
the members and one delete button.

I found the contains attribute and thought that was my answer but that doesn't
seem to work either. I could build an array but all the documentation that I
find (including the MM CFMX Web Application Construction Kit) doesn't show how
to populate an array with a variable, like:
<cfset something=NewArray(1)> <cfset something="#UserID#">
and I'm not even sure if that's valid or not but you see where I'm getting at.


I need some way of checking to see if the Session.UserID IS #UserID# and if
so, display ONE button and if not then display the other button only once and
I'm not quite getting at how to do that.

I'm giving you what I've tried because for one it explains my ways of thinking
and two I know readers here appreciate an effort to figure it out before
asking. I've run out of ideas so I'm seeking help. Any would be appreciated.

Thanks in advance,
Dax

PS: You can safely assume that I've got all my SQL statements in place, etc.
The code above is for example only.

[mxstu]

How are you populating the "UserID" list? Are you storing the list of users
signed up for each meeting in a single field in the database? If so, you
should really re-think the table design.

You said you already have a [User] and [Meeting] table that store the users
that can log in and the available meetings. You could add a third table
[Meeting_User]. This table would contain one record for each meeting a user
was signed up for. For example, "Adam Black" is signed up for two meetings
"Meeting A" and "Meeting B". The [Meeting_User] table would contain two
records:

[MeetingID] | [UserID]
1 (Meeting A) | 1 (Adam Black)
2 (Meeting B) | 1 (Adam Black)


Then you could just query the [Meeting_User] table to determine if a user was
signed up for a specific meeting:

SELECT MeetingID
FROM [Meeting_User] mu
WHERE MeetingID = (some meeting ID)
AND UserID = (your session.userID)

By the way, I think the reason your CFLOOP did not work is this statement:
< cfif i = Session.UserID >

Using "=" assigns a value. To test for equality (i.e. does x equal y?) use
the "EQ" operator. sessionID

[Dax Arroway]

I do all ready have the three tables. One containing all the members able to
log in, one of meetings, and the other of Meetings_X_Users. So in the
Meetings_X_Users table, I have a UniquieID, UserID, MeetingID, and some other
feilds used in different parts of the application. I'm running Access DBs.
Also, to make things worse, I'm also running a SQL Query with an Outer Join and
grouped data by Date. It goes something like this:

SELECT MeetingDate, MeetingID, Title, Description, UserID
FROM tbl_Meetings a
LEFT OUTER JOIN tbl_Meetings_X_User ua
ON ua.MeetingID = a.UserID
WHERE MeetingDate >= #TodayDate#
ORDER BY MeetingDate, ID

Of course I can't run another query and nest the output in the other output,
which is grouped.

I do get the data. It doesn't seem to be a problem with the query. It's more
how to munipulate the data once I've gotten it. If I put output tags around
the whole thing, it validates each MemberID and presents either an Add button
or Delete button for each memeber. So I need some way of getting it to say,
"Look at the list of participants. If any of them are the person logged in
right now, display a Delete button so the person can un-list themselves from
this meeting, otherwise, present this user with an Add button so they can sign
up for the meeting."

(Sorry for not including all the important information. I hope this helps
paint a clearer picture.)
- Dax

[mxstu]

I think I understand now. I was afraid you were storing comma-delimited lists
in a database table. (Shudder)

I'm sure there are methods, but two options are:
1) Use the ListFind() function to search the list of meeting participant ID's
to determine which button to display
-OR-
2) Include the ID of the current user in the query (assuming you don't
actually need the list of meeting participants for something else). This way
you only retrieve the UserID information that you need.

My preference would be method (2) because it doesn't pull excess information
from the database. See the attached examples. They're crude but they should
illustrate the options.



<!----------- METHOD 1 ------------------>

<!--- set test user ID --->
<cfset session.UserID = 3>
<cfset TodayDate = "05/01/2005">
<cfquery name="getMeetings" datasource="myAccessDSN">
SELECT m.MeetingDate, m.MeetingID, m.Title, m.Description, ua.UserID
FROM tbl_Meetings m LEFT OUTER JOIN tbl_Meetings_X_User ua ON m.MeetingID =
ua.MeetingID
WHERE m.MeetingDate >= #CreateODBCDate(TodayDate)#
ORDER BY m.MeetingDate, m.MeetingID
</cfquery>

<table border="1">
<tr>
<th>Meeting ID</th>
<th>Meeting Date</th>
<th>Meeting Title</th>
<th>Option</th>
</tr>
<cfoutput query="getMeetings" group="MeetingID">
<tr>
<td>#MeetingID#</td>
<td>#MeetingDate#</td>
<td>#Title#</td>
<cfset ParticipantList = "">
<cfoutput>
<cfset ParticipantList = ListAppend(ParticipantList, UserID)>
</cfoutput>
<td>
<form action="someActionPage.cfm">
<input type="hidden" name="meetingID" value="#MeetingID#">
<cfif ListFind(ParticipantList, Session.UserID) gt 0>
<input type="submit" value="DELETE">
<cfelse>
<input type="submit" value=" ADD ">
</cfif>
</form>
</td>
</tr>
</cfoutput>
</table>

<!----------- METHOD 2 ------------------>
<cfquery name="getMeetings" datasource="myAccessDSN">
SELECT m.MeetingDate, m.MeetingID, m.Title, m.Description, ua.UserID
FROM tbl_Meetings m LEFT OUTER JOIN tbl_Meetings_X_User ua
ON ( m.MeetingID = ua.MeetingID AND ua.UserID = #session.UserID# )
WHERE m.MeetingDate >= #CreateODBCDate(TodayDate)#
ORDER BY m.MeetingDate, m.MeetingID
</cfquery>

<table border="1">
<tr>
<th>Meeting ID</th>
<th>Meeting Date</th>
<th>Meeting Title</th>
<th>Option</th>
</tr>
<cfoutput query="getMeetings" group="MeetingID">
<tr>
<td>#MeetingID#</td>
<td>#MeetingDate#</td>
<td>#Title#</td>
<td>
<form action="someActionPage.cfm">
<input type="hidden" name="meetingID" value="#MeetingID#">
<cfif UserID eq Session.UserID>
<input type="submit" value="DELETE">
<cfelse>
<input type="submit" value=" ADD ">
</cfif>
</form>
</td>
</tr>
</cfoutput>
</table>

[Dax Arroway]

That's it. I'll have to tweak it a bit because after it sees the first
instance of the presence of the logged on user, it from that point on displays
the Delete button. I'll keep working on it though. Thanks for the help and
the nudge in the right direction!
- Dax

[mxstu]

Hmm, it seemed to work correctly for me. I did have to change the SQL you
posted ...

--- original ...
FROM tbl_Meetings a LEFT OUTER JOIN tbl_Meetings_X_User ua
ON ua.MeetingID = a.UserID

--- was changed to .....

FROM tbl_Meetings a LEFT OUTER JOIN tbl_Meetings_X_User ua
ON ua.MeetingID = a.MeetingID

[OldCFer]

Just use:
<cfset Participants = ValueList(queryname.SessionID)>

<cfif ListFind(Participants,Session.UserID)>
Display Del Button Form which deletes the user from the meeting table.
<cfelse>
Display Add Button Form which adds the Session.UserID to the table.
</cfif>

[mxstu]

I don't think he can use the ValueList() function because he's outputting the
results for multiple meetings, so I think this ...

<cfset Participants = ValueList(queryname.SessionID)>
<cfif ListFind(Participants,Session.UserID)> ...

would essentially return true if the current user was a participant of any
meeting, not just the current meeting record in the output loop

[Dax Arroway]

<cfset Participants = ValueList(queryname.SessionID)>
<cfif ListFind(Participants,Session.UserID)> ...
does give me a Delete button in the meeting the currently signed on user is in
but it also gives me one for every meeting following that one as well.

I used the original help Mxstu gave but I'd placed the cfset list function
later on in the code and that's why it didn't work. I put in just under the
first cfoutput tag in a series of nested groups and it worked like a charm!
Thanks.