...

View Full Version : Query within Query



penny94
11-13-2012, 01:25 PM
I have a table 'friends' which has username and friend columns so if a user is friends with another user it is saved in this table. I also have another table with posts users have made. I am trying to make it only show posts from the users friends. However it is only running the query for the first 'friend' in the table. What's wrong?




<?php


$query = "SELECT * FROM friends WHERE username='$username'";
$result = mysql_query($query) or die (mysql_error());
while($row = mysql_fetch_array($result)) {


$friend = $row['follow'];



$query = "SELECT * FROM posts WHERE username='$friend'";
$result = mysql_query($query) or die (mysql_error());
while($row = mysql_fetch_array($result)) {


$id=$row['id'];
$post=$row['post'];


echo $post;
echo " posted by ";
echo $friend;
echo "<br><br><br>";


}
}

?>

Fou-Lu
11-13-2012, 02:39 PM
Your overwriting $row.
In any case, this isn't the way to do this. You never query in a loop unless there is no other option. This should be joined.


SELECT p.id, p.post, p.username
FROM posts p
INNER JOIN friends f ON f.follow = p.username
WHERE f.username = '$username'

penny94
11-13-2012, 02:58 PM
Your overwriting $row.
In any case, this isn't the way to do this. You never query in a loop unless there is no other option. This should be joined.


SELECT p.id, p.post, p.username
FROM posts p
INNER JOIN friends f ON f.follow = p.username
WHERE f.username = '$username'


Thanks this is working!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum