Team members

[megalith]

I am developing an application that displays activities of a user in php using mySQL, the query works fine when relating to a single user or for the entire userbase but when referring to a team i am lost. Firstly i am using mySQL 4.4, I apologise in advance for using php in the code blocks but it was the only way to explain my issue as i understand it. The method i used is unreliable and a purely mySQL solution would be adventagous.

 

here is the code for a single user

$res = $db->Execute("SELECT user_id,location,team FROM $dbtables[user] WHERE email='$username'");
$userinfo = $res->fields;
$result3 = $db->Execute("SELECT distinct $dbtables[activity].user_id,action FROM $dbtables[activity],$dbtables[universe] WHERE user_id = $userinfo[user_id] AND $dbtables[activity].action=$dbtables[universe].action order by sector_id ASC ");
}

 

a simple alteration of this is used to obtain the entire list is

$res = $db->Execute("SELECT user_id,location,team FROM $dbtables[user] WHERE email='$username'");
$userinfo = $res->fields;
$result3 = $db->Execute("SELECT distinct $dbtables[activity].user_id,action FROM $dbtables[activity],$dbtables[universe] WHERE $dbtables[activity].action=$dbtables[universe].action order by sector_id ASC ");
}

 

but to do all members of the team needs some additional programming

 

$res = $db->Execute("SELECT user_id,location,team FROM $dbtables[user] WHERE email='$username'");
$userinfo = $res->fields;
$resultT = $db->Execute("SELECT user_id FROM $dbtables[user] WHERE team = $userinfo[team]");
$team_user = $resultT->fields; // This is a list of user id's of the users team
$users="";
foreach($team_user as $value){
$users .= "user_id = " . $value . "OR ";
}
$users = rtrim($users, " OR");// this routine above constructs a string like "user_id = user1 OR user_id = user2 
$result3 = $db->Execute("SELECT distinct $dbtables[activity].user_id,action FROM $dbtables[activity],$dbtables[universe] WHERE ($users) AND $dbtables[activity].action=$dbtables[universe].action order by sector_id ASC ");
}

 

Is there a better way to achieve this result? i'm struggling with some aspects of mySQL.

the tables used are activity, universe and user

[fenway]

Sounds like you need to learn about JOINs... that way you can do most/all of this as a single statement.