Looping is making me loopy

[adrianle]

I've got what feels like a complex issue with what I'm sure for you experienced folks would seem simple, but I'm having trouble wrapping my head around it.

 

-I have a query that retrieves a general list of committees, each with a uniqueID

-I have a query retrieving records from another table where the records match the user ID, currently stored in a session variable (from a previous login process) and the uniqueID for the committees they serve on.

 

I have a WHILE loop populating the page with the committees. Works fine.   I have a link (potentially) next to the committee name, pointing to another page with protected committee data, which should only be visible to members of that committee.

 

What I *need* to do is having a conditional statement of some sort that only displays that link to the protected page IF the user is a member of that specific committee. Basically, IF they're on the committee, they see the link.. if they're NOT on that committee, they see nothing next to the committee name.

 

How would one of you wonderful people write that?

[Barand]

Assuming you have something like this

mysql> SELECT * FROM committee;
+--------------+----------------+
| committee_id | committee_name |
+--------------+----------------+
|            1 | Procurement    |
|            2 | Welfare        |
|            3 | Paper clips    |
+--------------+----------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM member;
+-----------+--------------+
| member_id | committee_id |
+-----------+--------------+
|         1 |            1 |
|         1 |            3 |
|         2 |            1 |
|         2 |            2 |
|         3 |            1 |
|         3 |            2 |
|         4 |            3 |
|         5 |            3 |
|         6 |            3 |
+-----------+--------------+

Then

SELECT 
    c.committee_id
   ,c.committee_name
   ,m.member_id
FROM committee c
LEFT JOIN member m
    ON c.committee_id = m.committee_id AND member_id = 1

Gives

+--------------+----------------+-----------+
| committee_id | committee_name | member_id |
+--------------+----------------+-----------+
|            1 | Procurement    |         1 |
|            2 | Welfare        |      NULL |
|            3 | Paper clips    |         1 |
+--------------+----------------+-----------+

You would provide a link where the member_id is NOT NULL

[adrianle]

So here's the challenge.. I'm looping through the list of all categories on the page as well.  I want to build the conditional statement that says "if the member_id is NOT null, show the link", except that even in your example, there are TWO instances where the membe_id is not null.  So how do I write my conditional statement so that it takes into consideration HOWEVER MANY different committee instances there may be?

 

Right now, the conditional statement ONLY looks at the "first" committee ID that comes through.

[Barand]

From the sample data I posted you can see that member id 1 is a member of two committees (Procurement and Paper Clips) so shouls have access to the links for those two committees.

 

The query lists all committees and shows which ones member 1 can view (non null member id in results), so

loop through results
    output committee name
    if (member_id is not null)
         output link to committee page
    endif
endloop

[adrianle]

Yeah this worked well, thanks so much for the guidance!