Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Nov 2007
    Posts
    144
    Thanks
    64
    Thanked 0 Times in 0 Posts

    Arrow Can anyone tell me why this bit of code isn't working?

    I'm trying to make a contact list with group memberships. I'm a fairly novice programmer, so please can someone take a quick look and see if you can spot the error(s)!?

    Here's my code:

    PHP Code:
        <?php
        
            $sql 
    "SELECT `group` FROM groups";
            
    //echo ">" . $sql . "<br>";
            
    $result2 mysql_query($sql);
            
        
            
            
    $sql "SELECT `group_id` FROM groups";
            
    //echo ">" . $sql . "<br>";
            
    $result mysql_query($sql);
            
    $group_id mysql_fetch_array($result);

            while(
    $groups mysql_fetch_array($result2)) {
        
          
    ?><input type="checkbox" name="group[]" value="<? echo $groups['group']; ?><?php
            $sql 
    "SELECT `group_id` from groups WHERE `group`=" '"' $groups['group'] . '"'
            
    //echo ">" . $sql . "<br>";
            
    $result mysql_query($sql);
            
    $tmp mysql_fetch_array($result);
            
    $group_id $tmp['group_id'];
            
            
    $sql "SELECT `group_id` FROM groupmembership WHERE `contact_id`=" $_GET['id'];
            
    //echo ">" . $sql . "<br>";
            
    $result mysql_query($sql);
            
    $groupmem_ids mysql_fetch_array($result);
            
            if(
    in_array($group_id$groupmem_ids)) {
              echo 
    "checked";
            }
            
            echo 
    ">" $groups['group'] . "<br>";
        }
    ?>
    For some reason it produces this:

    [ ] Gaynors List
    [x]Regulars
    [ ]New Customer

    the only one ticked is Regulars, but in the groupmembership table, the contact is associated with all three of these group_id's.

    Also, is there a better way to do this? I kind've thin this would be a fairly heavy load on DB servers.

    Thanks a lot guys!

    Beren

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    You have 4 queries where you only need one. You want your query to return a list of all groups, plus a bonus column to indicate if the current user (represented by $_GET['id']) belongs to that group (represented by a row in the groupmembership table).

    So... try this...

    Code:
    SELECT group, gm.group_id
    FROM groups AS g
    LEFT JOIN groupmembership AS gm
    ON g.group_id = gm.group_id
    AND gm.contact_id = '{$_GET['id']}'
    ORDER BY group
    This query joins the group table with the groupmembership table based on group_id. But, since it's a LEFT join (also referred to as a LEFT OUTER join), it will bring back a result from the table on the "left" (the table first listed, in this case the group table) even if there's not a match on the groupmembership table. When that happens, then the value of the group_id from the gm table will be "null", so in your PHP "while" loop, check that column to see if it's null-- if it is, then don't check the checkbox. If it's not null, check the box.

    A fun way to let the query check for "null" for you is by way of the ifnull() function:

    Code:
    SELECT group, nullif(gm.group_id, "not checked", "checked")
    FROM groups AS g
    LEFT JOIN groupmembership AS gm
    ON g.group_id = gm.group_id
    AND gm.contact_id = '{$_GET['id']}'
    ORDER BY group

  • Users who have thanked Fumigator for this post:

    samuurai (03-05-2008)

  • #3
    Regular Coder
    Join Date
    Nov 2007
    Posts
    144
    Thanks
    64
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fumigator View Post
    Code:
    SELECT group, nullif(gm.group_id, "not checked", "checked")
    FROM groups AS g
    LEFT JOIN groupmembership AS gm
    ON g.group_id = gm.group_id
    AND gm.contact_id = '{$_GET['id']}'
    ORDER BY group
    Thanks for the very helpful response...

    The first query worked fine in PHPMyAdmin, but I tried getting the second one to work but It has issues with the nullif parameters. I tried finding some documentation for nullif to try to fix it myself, but I can only find examples with two arguments.

    How are you using nullif in this case?

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Oh, whoops, I meant IFNULL(), and yes you're right there are only two arguments. IFNULL() says "If the first argument is null, then return the second argument, otherwise return the firs argument". So I don't know how much value that would be for you to use in this situation-- the first query does the job, you just need to check the column selected from your groupmembership table to see if it's null.


  •  

    Posting Permissions

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