[RESOLVED] SQL subquery problems; programming a friend's application

[dxdolar]

I'm trying to return multiple values so I can get the first and last name of everyone that's your "friend"

here's a look at the new code that I was trying to use.


if (isset ($_POST['submitted'])) { //check if form is submitted

$id = $_POST['mid'];

$q = "SELECT first_name, last_name FROM users WHERE user_id=(SELECT f_id FROM friends_junction WHERE m_id='$id');"; //subselect query
$r = mysqli_query ($dbc, $q);
while ($row = mysqli_fetch_array ($r, MYSQLI_ASSOC)) { //as long as there are records
echo '' . $row['first_name'] . ' ' . $row['last_name'] . ' is your friend!';
}//end of while
} //end of if submitted


echo '<div>
<form action ="test_friends.php" method="post">
<input type="text" size="15" maxlength="20" name="mid" value="" />
<input type="submit" name="submit" value="SUBMIT" />
<input type="hidden" name="submitted" value="TRUE" />
</form>
</div>';

include ('includes/footer.php');
?>

here's the structure of the friends_junction table, it's just a 1 to 1.
m_id | f_id
3 | 4
3 | 5
3 | 6

Any help would be awesome, thanks!

[Phill Pafford]

is the sub select friend Id equal the user Id?


WHERE user_id=(SELECT f_id

[Phill Pafford]

Looks backwards to me or maybe a join


SELECT first_name, last_name FROM users WHERE user_id=(SELECT f_id FROM friends_junction WHERE m_id='$id');


maybe try


SELECT t1.first_name, t1.last_name FROM users AS t1
LEFT JOIN friends_junction AS t2 ON (t1.common_filed = t2.common_field)
WHERE user_id='$id';

[dxdolar]

holy moly! a week of trying figuring out how to do it and all it took was a left join!?

you are a saint and a fine human being. Thank you VERY much