SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Wizard
    Join Date
    Oct 2001
    Posts
    2,686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select from many to many relationship

    I'm having trouble with a select statement with a many to many relationship.

    This code is from a site where I do som update. Each article have one og more authors. To assign the authors to the different articles I have the table (authorLookup). This means there is a many to many relationship between the table article and the table author.

    The problem is when I go to the update page to update the information about an article. There I want to list all the authors with an checkbox. What I want to do is that the authors that are already assigned to an article (trough the table authorLookup) are going to be checked when I list out all the autohrs.

    Here are my tables and my code so far:
    PHP Code:
    |----------------------------------|
    |              
    article             
    |----|------------|----------------|
    id |   name     |      url       |
    |----|------------|----------------|
    1  article 1  | /to/the/folder |
    2  article 2  | /to/the/folder |
    | .  |    .       |        .       |
    | .  |    .       |        .       |
    n  article n  | /to/the/folder |
    |----|------------|----------------|

    |----------------------|
    |     
    authorLookup     |
    |-----------|----------|
    articleId authorId |
    |-----------|----------|
    |     
    1     |     2    |
    |     
    1     |     3    |
    |     
    1     |     4    |
    |     
    2     |     1    |
    |     
    3     |     2    |
    |     
    3     |     4    |
    |     .     |     .    |
    |     .     |     .    |
    |     
    n     |     n    |
    |-----------|----------|

    |-------------- ------|
    |      
    author         |
    |---------------------|
    id name  email  |
    |----|-------|--------|
    1  Name1 email1 |
    2  Name2 email2 |
    3  Name3 email3 |
    | .  |   .   |   .    |
    | .  |   .   |   .    |
    n  Namen emailn |
    |----|-------|--------|

    echo 
    $editid//This value is passed from the site that link to this one. $editid is the id to which article to edit

    //Start listing all the authors
    $authors mysql_query("SELECT id, name FROM author ORDER by name");
    while (
    $author mysql_fetch_array($authors)) {
        
    $id $author["id"]);
        
    $name $author["name"]);

        
    $lookups mysql_query("SELECT articleId, authorId FROM authorLookup WHERE articleId ='$editid' GROUP BY authorId")
        while (
    $lookup mysql_fetch_array($lookups)) {
           
    $articleId $lookup["articleId"]);
           
    $authorId $lookup["authorId"]);
                if(
    $articleId == $id) {
                    
    $checked "checked";
                }
                else {
                    
    $checked "";
                }
                echo(
    "<input type='checkbox' name='author$id$checked>$name<br>\n\n\n");
         } 
    // End: while ($lookup = mysql_fetch_array($lookups))
    // End: while ($author = mysql_fetch_array($authors)) 
    This code list all the authors and set them checked correctly. The problem is that if I have assigned two authors to an article this code list all the autors two times, and three times if I have assigned three authors to an article.

    So what I want is of course just to list the authors once even if there are three autors assigned to an article.
    As you can see I have a while-loop inside another while-loop. This is a bad way of programming (I think). But my skills aren't good enough to do better. Is there a way to do all this in just one select statement?

    Hopefully I've explained myself good enough.
    Any help would be appriciated!

    -Helge

  2. #2
    SitePoint Member
    Join Date
    Jan 2002
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    From what I can see, it looks as though you want to list every author and if he/she is working on the specified article, the checkbox next to that author is checked. Right?

    What's this--

    if ($articleId == $id) ...



    Isn't $id the author's id? So shouldn't it be:

    if ($authorId == $id) ...



    And also--

    PHP Code:
    while ($author mysql_fetch_array($authors)) {
        
    $id $author["id"]);
        
    $name $author["name"]);

        
    $lookups mysql_query("SELECT articleId, authorId FROM authorLookup WHERE articleId ='$editid' GROUP BY authorId")
        while (
    $lookup mysql_fetch_array($lookups)) {
           
    $articleId $lookup["articleId"]);
           
    $authorId $lookup["authorId"]);
                if(
    $articleId == $id) {
                    
    $checked "checked";
                }
                else {
                    
    $checked "";
                }
                echo(
    "<input type='checkbox' name='author$id$checked>$name<br>\n\n\n");
         } 
    // End: while ($lookup = mysql_fetch_array($lookups))

    Take a look at under which while loop you put the checkbox under. It looks as though that's why the names are getting posted multiple times.


    Hopefully, I just randomly solved your problems.

    -Mike McT
    Novice PHP Programmer Extraordinaire.

  3. #3
    SitePoint Enthusiast tbag's Avatar
    Join Date
    Jan 2002
    Location
    ACT, Australia
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Is this using a form?

    Is this using a form to provide the variables??
    or not??
    - I am a PHP Guru
    - I am a Graphics Guru

  4. #4
    SitePoint Wizard
    Join Date
    Oct 2001
    Posts
    2,686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Mike McT:
    Isn't $id the author's id? So shouldn't it be:

    if ($authorId == $id) ...
    You're absolutly right. My code are in norwegian, so I have to translate it into english before I use it her in the forums. So thats when I made the mistake.
    Take a look at under which while loop you put the checkbox under. It looks as though that's why the names are getting posted multiple times.
    I've tried this as well, and put the checkbox under the first loop, but then it will only set the last person, that are going to be marked as 'checked', and not the other ones.


    tbag: I'm not sure if I understand what you're asking about. Which variables are you asking about?

    Helge
    Last edited by Helge; Jan 29, 2002 at 06:07.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •