48 users online (0 members and 48 guests)  


  Results 1 to 2 of 2

Related

  1. PHP Code Formatting and Design    Forum: PHP Forum
    Replies: 4
  2. formatting text with PHP    Forum: HTML Forum
    Replies: 1
  3. Formatting in an IFRAME    Forum: CSS Forum
    Replies: 2
  4. Formatting Dates    Forum: HTML Forum
    Replies: 2
  5. Formatting with Html    Forum: HTML Forum
    Replies: 4
  1. #1
    Jasongr's Avatar
    New User

    Status
    Offline
    Join Date
    May 2005
    Posts
    4

    help in formatting a query

    Hello

    I need some help in formatting a query using 'case when' conditionals

    I have 2 tables:
    * Table article stores information on an article such as: ID, subject, submittter, submission date and audience scope.
    * Table audience_members stores information about specific members who are entitled to access articles

    Here are the two table definitions:
    Code:
    CREATE TABLE `article` (
      `article_ID` int(10) unsigned not null auto_increment,
      `subject` varchar(40) not null default '',
      `submitter_id` int(10) unsigned not null,
      `submissionDate` datetime,
       `AudienceScope` tinyint(1) unsigned not null,
      PRIMARY KEY (`article_ID`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    
    CREATE TABLE `audience_members ` (
      `article_ID` int(10) unsigned not null,
      `Member_ID` int(10) unsigned not null,
      PRIMARY KEY (`article_ID`, `Member_ID`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    Field `AudienceScope` in table article defines what is the audience scope level of the article. It has 2 possible values:
    1 - Only the current article submitter is allowed to see it
    2 - Only set of members with IDs 2, 4, or 6 allow to see it

    When forming the query, I have access to the ID of the current user.
    I use PHP, so for those who know PHP, you can assume that the current user ID is stored in
    variable $currentUserID. That parameter needs to be used in the query
    For those you don't know PHP, simply assume that the current user ID is the constant 4

    I need to form a query that will return all the fields of the article (article.*) that adhere to the `AudienceScope` value.
    That means that I need to iterate over all the articles and for each one to test the following: if the `AudienceScope` is 1, then include it in the result set, only if the submitter_id if 10, else if `AudienceScope` is 2, then include it if members 2, 4 or 6 are found in table `audience_members` for that article note that this table is ONLY used if `AudienceScope` is 2).

    I hope that I made this clear

    any help would be appreciated

  2. #2
    Jasongr's Avatar
    New User

    Status
    Offline
    Join Date
    May 2005
    Posts
    4

    Re: help in formatting a query

    Hi

    After I read my post again, it occurred to me that indeed I had a probelm in the definition

    Let me give a few examples that will clarify what I want to achieve:

    assume I have the following entries in the article table:
    Code:
    article_ID subjec submitter_id submissionDate AudienceScope
    1          AAA    5            2005-05-30     1
    2          BBB    6            2005-05-30     1
    3          CCC    5            2005-05-30     2
    4          DDD    7            2005-05-30     2
    and the following entries in the audience_members table:
    Code:
    article_ID Member_ID
    3          5    
    3          7
    4          7
    4          8
    I need to return the set of articles that the current user is allowed to see

    Example 1: current user is #3:
    - Article 1 is NOT included because it has AudienceScope of 1 and the submitter is not 3
    - Article 2 is NOT included because it has AudienceScope of 1 and the submitter is not 3
    - Article 3 is NOT included because it has AudienceScope of 2 and member 3 is not in the list of members listed in table audience_members for article 3
    - Article 4 is NOT included because it has AudienceScope of 2 and member 3 is not in the list of members listed in table audience_members for article 4

    So in this case I get an empty result set (meaning that member 3 sees nothing)

    Example 2: current user is #5:
    - Article 1 IS included because it has AudienceScope of 1 and the submitter IS 5
    - Article 2 is NOT included because it has AudienceScope of 1 and the submitter is not 5
    - Article 3 IS included because it has AudienceScope of 2 and member 5 IS in the list of members listed in table audience_members for article 3
    - Article 4 is NOT included because it has AudienceScope of 2 and member 5 is not in the list of members listed in table audience_members for article 4

    Example 3: current user is #6:
    - Article 1 is NOT included because it has AudienceScope of 1 and the submitter is not 6
    - Article 2 IS included because it has AudienceScope of 1 and the submitter IS 6
    - Article 3 is NOT included because it has AudienceScope of 2 and member 6 is not in the list of members listed in table audience_members for article 3
    - Article 4 is NOT included because it has AudienceScope of 2 and member 6 is not in the list of members listed in table audience_members for article 4

    Example 4: current user is #7:
    - Article 1 is NOT included because it has AudienceScope of 1 and the submitter is not 7
    - Article 2 is NOT included because it has AudienceScope of 1 and the submitter is not 7
    - Article 3 IS included because it has AudienceScope of 2 and member 7 IS in the list of members listed in table audience_members for article 3
    - Article 4 IS included because it has AudienceScope of 2 and member 7 IS in the list of members listed in table audience_members for article 4

    Example 5: current user is #8:
    - Article 1 is NOT included because it has AudienceScope of 1 and the submitter is not 8
    - Article 2 is NOT included because it has AudienceScope of 1 and the submitter is not 8
    - Article 3 is NOT included because it has AudienceScope of 2 and member 8 is not in the list of members listed in table audience_members for article 3
    - Article 4 IS included because it has AudienceScope of 2 and member 8 IS in the list of members listed in table audience_members for article 4


    I hope this makes more sense
    As I mentioned, what I have is the ID of the current member as parameter which can be used in the query

    regards



Tags for this Thread