How to find common members among multiple groups

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
khuzema
Forum Newbie
Posts: 10
Joined: Thu Mar 18, 2010 3:32 pm

How to find common members among multiple groups

Post 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
Last edited by Benjamin on Tue Apr 27, 2010 4:26 am, edited 1 time in total.
Reason: Added [syntax=php] tags.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: How to find common members among multiple groups

Post by Benjamin »

:arrow: Use [syntax] tags when posting code in the forums.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: How to find common members among multiple groups

Post 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";
khuzema
Forum Newbie
Posts: 10
Joined: Thu Mar 18, 2010 3:32 pm

Re: How to find common members among multiple groups

Post by khuzema »

Thanks pytrin i must say u have very strong back-end knowledge

Regards
Khuzema Dharwala
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: How to find common members among multiple groups

Post 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";
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Post Reply