在MYSQL中查询WHERE

Question

I have two MySQL tables. One is called members and has a column called users. The other is called sex and has two columns: user_s and sex. I am trying to create an array comprised of all users from the members table who are 1) in the sex table and 2) have "men" selected in the sex column.

When I print_r this array nothing is displayed even though I can see that there are users who met these criteria. Any thoughts?

<?php 
 // test.php
include_once("header.php");
$iaminterestedin = "men"; 
$result = queryMysql("SELECT `user` FROM `members`
                WHERE `user` IN(SELECT `user_s` FROM `sex` WHERE sex='$iaminterestedin')"); 

$combination = array();
while(($row = mysql_fetch_assoc($result)))
{    
$combination = $row['user'];
}
print_r($combination);
?>

Answer 1

You should add values to combination using $combination[] = $row['user'] or you will always overwrite value on each loop iteration

You also ought to read something about JOIN tables, this will help you a lot with queries like that. For example you will use:

SELECT user FROM members INNER JOIN sex ON sex.user_s = members.user
WHERE sex '$iamintererstedin'

If you add a foreign key (http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html) from sex to users this will improve query performance when it gets bigger and time becomes a problem