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

[samuurai]

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

[Fumigator]

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...

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:

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

[samuurai]

Originally Posted by Fumigator

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?

[Fumigator]

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.