Page 1 of 1

How to find common members among multiple groups

Posted: Tue Apr 27, 2010 4:22 am
by khuzema
Hi all
I have written a php script that will send emails to all the members belonging to the groups to which i want to receive the email . Now in this case it is obvious that a user can belong to multiple groups , what i want is that the such user shall receive only one email . Now the problem is that my script process one group at a time means it will first take one group determine details like fullname & email id for every user of that group & ultimately send mails to them . Then it will take the next group & repeat the same process . The code section that is doing the mentioned process is as follows :
------------------------------------------------------------------------------------------------------------

Code: Select all

for($i=0;$i<count($dest_group);$i++)
	       {
	            $sel_grp=$dest_group[$i];
                $resultset=$modx->db->query("select tb1.fullname,tb1.email from modx_web_user_attributes  tb1,modx_webgroup_names tb2,modx_web_groups tb3 where tb2.name='".$sel_grp."'and tb3.webgroup=tb2.id and tb1.id=tb3.webuser");    		   
       	        $grpusers=$modx->db->makeArray($resultset);
                foreach($grpusers as $user) 
                $mail->AddAddress($user['email'],$user['fullname']);
               	$mail->Subject=$this->subject;
                $mail->Body=$this->message;   		
                if(!($mail->Send()))
                $senderror[$i]=1;
           }    
-------------------------------------------------------------------------------------------------------------------------
there are three database tables used in this code
(1) modx_web_user_attributes This table contains user's id , fullname & email id columns
(2)modx_webgroup_names This table contains group id & group name columns
(3)modx_web_groups This table contains userid & groupid to which this user belongs to

Any kind of help is really appreciable

Regards
Khuzema Dharwala

Re: How to find common members among multiple groups

Posted: Tue Apr 27, 2010 4:26 am
by Benjamin
:arrow: Use [syntax] tags when posting code in the forums.

Re: How to find common members among multiple groups

Posted: Tue Apr 27, 2010 6:48 am
by Eran
Remove the loop, use one query to get all the (unique) Emails -

Code: Select all

$names = array();
foreach($sel_grp as $name) {
     $names[] = "'" . $name . "'";
}
$query = "SELECT tb1.fullname,tb1.email FROM modx_web_user_attributes AS tb1
INNER JOIN modx_web_groups AS tb3 ON tb1.id=tb3.webuser
INNER JOIN modx_webgroup_names AS tb2 ON tb3.webgroup=tb2.id 
WHERE tb2.name IN (" . implode(',',$names) .")
GROUP BY tb1.id";

Re: How to find common members among multiple groups

Posted: Tue Apr 27, 2010 8:20 am
by khuzema
Thanks pytrin i must say u have very strong back-end knowledge

Regards
Khuzema Dharwala

Re: How to find common members among multiple groups

Posted: Tue Apr 27, 2010 8:37 am
by AbraCadaver
Instead of the loop I'd probably do this:

Code: Select all

$names = "'" . implode("','", $sel_grp) . "'";

$query = "SELECT tb1.fullname,tb1.email FROM modx_web_user_attributes AS tb1
INNER JOIN modx_web_groups AS tb3 ON tb1.id=tb3.webuser
INNER JOIN modx_webgroup_names AS tb2 ON tb3.webgroup=tb2.id
WHERE tb2.name IN ($names)
GROUP BY tb1.id";