Jump to content

Looping is making me loopy


adrianle

Recommended Posts

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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
Link to comment
Share on other sites

  • 2 weeks later...
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.