Query Results In Another Query

[slj90]

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>";


}
}

?>

[thara]

do you need to select the posts that one user have posted?

[Barand]

use a single query with a JOIN

 

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

[slj90]

use a single query with a JOIN

 

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

 

I don't quite understand how to do this...

 

I just read a tutorial on it and came up with this:

 

 

 

$query = "SELECT posts.post, friends.username,  ".
"FROM friends, posts ".
"WHERE posts.username = friends.friend";

 

I'm confused, where does it get the usernames friends from?

[thara]

I think Barand has missed something in his post.. I think query should something similar to this..

 

SELECT * FROM friends AS f 
INNER JOIN posts AS p ON p.username = f.follow
WHERE f.username = '$username'

 

This is not suit for you.. explain your 2 tables with columns...

[slj90]

Thanks Thara

 

Table one 'Posts' has the columns 'Username' and 'Post'.

Table two 'Friends' has the columns 'Username' and 'Friend' (friends username).

 

I want to display all the posts that all friends of the logged in users has made.

 

Thanks

[Barand]

Table one 'Posts' has the columns 'Username' and 'Post'.

Table two 'Friends' has the columns 'Username' and 'Friend' (friends username).

 

If those are your column names, where did this come from

 

$friend = $row['follow'];

[thara]

 

If those are your column names, where did this come from

 

$friend = $row['follow'];

 

 

yes what does mean

 

$friend = $row['follow'];

 

why you use 'username' column in both table?

Edited November 13, 2012 by thara

[Barand]

I think Barand has missed something in his post.. I think query should something similar to this..

 

SELECT * FROM friends AS f
INNER JOIN posts AS p ON p.username = f.follow
WHERE f.username = '$username'

 

This is not suit for you.. explain your 2 tables with columns...

 

@thara If you ever get around to reading the MySQL manual you will see the the AS is optional, denoted by [ .. ]

For each table specified, you can optionally specify an alias.

tbl_name [[AS] alias]

 

Can you please explain why a JOIN is not suitable here?

[slj90]

yes what does mean

 

$friend = $row['follow'];

 

why you use 'username' column in both table?

 

$row['follow'] should be $row['friend']

 

Username is used in the post table to save who posted it.

Username is used in the friends table so when a user is friends with someone it creates a row with their Username and their friends username.

 

Thanks

[Barand]

In that case

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