php - MySQL query WHERE statement which is a query itself? -

so i'll try explain goal first:

first, want query 1 table in database list of usernames.

second, want take usernames, , query table in database , return rows in these usernames appear in username field.

finally, want take output (in json array form right now), , return requesting client.

my query looks right now:

$query = mysql_query("select * tagusers username =      (select usera friendtable userb = '$username')"); 

this works when statement yields 1 result. if 1 returned usera, works fine. if multiple, bunch of errors.

here code in entirety:

if (isset($_post['username'])) { $username = $_post['username'];   $connect = mysql_connect("localhost", "root", ""); mysql_select_db("tagdb");  $query = mysql_query("select * tagusers username =      (select usera friendtable userb = '$username')");      }  while ($e = mysql_fetch_assoc($query)) {     $output[] = $e; }  $output = json_encode($output);  print $output; 

i following error on query line:

*warning: mysql_query() [function.mysql-query]: unable save result set in c:\wamp\www\tag\appgetfriendinfo.php on line 21*

so need know is, how write query in mysql returned array of rows?

you don't need subquery @ all, you'll better performance out of join. make sure have indexes defined on tagusers.username, friendtable.usera , friendtable.userb

select   tagusers.*   tagusers inner join   friendtable   on   tagusers.username = friendtable.usera ,   friendtable.userb = '$username'