...

View Full Version : Can anyone tell me why this bit of code isn't working?



samuurai
03-04-2008, 12:41 AM
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

$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
03-04-2008, 01:27 AM
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
03-04-2008, 11:55 PM
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
03-05-2008, 03:51 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum