[SOLVED] Is there a good way to do this?

[chantown]

Hi,

 

On my website, users will subscribe to "groups".  And each group has their own "News"

 

Is there anyway to make a good SELECT statement for this?

 

For example: I'm subscribed to groups 44,22,41, and 66

 

I want to have the news of all 4 groups.  Is there any easy method to do this with PHP+mysql?

 

SELECT * FROM news WHERE group = 44 AND group = 22 AND group = 41...is this the only way? (do i generate this Query through PHP)?

 

thanks!

[marcus]

SELECT * FROM `news` WHERE `group` IN ('22','41','44')

[chantown]

thanks!

 

will i have to have some sort of LOOP to generate the query?

 

Because some people might subscribe to many groups

[marcus]

Could do something like:

 

$sql = "SELECT * FROM `users_groups` WHERE `user_id`='1234'";
$res = mysql_query($sql) or die(mysql_error());
$num = mysql_num_rows($res);

$x=1;
while($row = mysql_fetch_assoc($res)){
   if($x == $num){
   $c = "";
   }else {
   $c = ",";
   }
$group .= "'" . $row['group_id'] . "'" . $c;
}

$sql2 = "SELECT * FROM `news` WHERE `group` IN($group)";
$res2 = mysql_query($sql2) or die(mysql_error());

[trq]

Or better still...

 

<?php

  $sql = "SELECT group FROM users_groups WHERE user_id = '1234'";
  if ($result = mysql_query($sql)) {
    if (mysql_num_rows($res)) {
      while ($row = mysql_fetch_assoc($res)) {
        $tmp[] = $row['group'];
      }
    }
  }

  $sql = "SELECT * FROM `news` WHERE `group` IN('" . implode("','",$tmp) . "')";

?>

[trq]

Even better still now that Ive thought about it. Get it all done in one query, something like....

 

SELECT * FROM news WHERE group_id IN(SELECT group_id FROM user_groups WHERE user_id = 1);

[chantown]

wowww Thank you everybody! Great help here