need in getting data from db

[doforumda]

Hi

i want to select data from db by comparing two things. First I want to select all the ids of currently logged in user's friends from friends table. then i want to select all the posts of currently logged in user's friends as well as his own posts from posts table. I am trying to achieve this task using following code but it does not work.

<?php 
session_start();
$connect = mysql_connect("localhost","user","pass");
mysql_select_db("test");



$friends = mysql_query("SELECT * FROM friend WHERE userid='$_SESSION[userid]'");

while($friend = mysql_fetch_assoc($friends)) {
$f = $friend['friendid'];
//echo $f."<br />";

$query = mysql_query("SELECT * FROM posts WHERE userid='$f' AND userid='$_SESSION[userid]'");

while($id = mysql_fetch_assoc($query)) {
	$userpost = $id['post'];
	$date = $id['date'];
	echo $userpost."<br />".$date."<br />";	
}

}
?>

[doforumda]

ok here are my friend and post tables

 

please help i am waiting for help

 

[attachment deleted by admin]

[doforumda]

I am still waiting for help

[jl5501]

It is possible that this could be done in one query, but, using the code you have, if you change the AND to OR in your second query then your results will probably be better.

 

currently you are expecting the field userid to have 2 different values at the same time.

[doforumda]

It is possible that this could be done in one query, but, using the code you have, if you change the AND to OR in your second query then your results will probably be better.

 

currently you are expecting the field userid to have 2 different values at the same time.

i tried with OR as well but still have not the same result which i wanted

[doforumda]

It is possible that this could be done in one query, but, using the code you have, if you change the AND to OR in your second query then your results will probably be better.

 

currently you are expecting the field userid to have 2 different values at the same time.

i tried with OR as well but still have not the same result which i wanted

 

now i use this code

<?php 

$connect = mysql_connect("localhost","user","pass");
mysql_select_db("test");



$friends = "SELECT * FROM friend WHERE userid='$_SESSION[userid]'";
$result = mysql_query($friends);
echo $friends."<br><br>";
while($friend = mysql_fetch_assoc($result)) {
$f = $friend['friendid'];
echo $f."<br />";

$query = "SELECT * FROM posts WHERE userid='$f' OR userid='$_SESSION[userid]'";
$result2 = mysql_query($query);
echo $query."<br><br>";
while($id = mysql_fetch_assoc($result2)) {
	$userid = $id['post'];
	$date = $id['date'];
	echo $userid."<br />".$date."<br />";	
}

}
?>

 

this is what it displays

SELECT * FROM friend WHERE userid='1'

10
SELECT * FROM posts WHERE userid='10' OR userid='1'

This is post of user 1
2010-02-02
This is second post of User 10
2010-02-01
11
SELECT * FROM posts WHERE userid='11' OR userid='1'

This is post of user 1
2010-02-02
This is third post of user 11
2010-02-03
20
SELECT * FROM posts WHERE userid='20' OR userid='1'

This is post of user 1
2010-02-02
This is last post of user 20
2010-02-05

[jl5501]

Yes ok the results are entirely what you would expect from what you are doing.

 

each time you call the second query you will get the results of the logged in user as well as the particular friend.

 

What you need to do, if using 2 queries, is to store all the ids you want plus the logged_in users id to , then query the posts against that list of ids

[jl5501]

I would think that a one query solution would work better for you, so try

select * from posts p,friend f where  (p.userid=f.userid or p.userid=f.friendid) and f.userid='.$_SESSION['userid']

[doforumda]

I would think that a one query solution would work better for you, so try

select * from posts p,friend f where  (p.userid=f.userid or p.userid=f.friendid) and f.userid='.$_SESSION['userid']

ok but i dont understand what are those p and f in this query?

[doforumda]

i am trying your query but i think i am putting it at wrong place can you please modify my abit?

[doforumda]

i am trying your query this way

$friends = "select * from posts p,friend f where (p.userid=f.userid or p.userid=f.friendid) and f.userid='$_SESSION[userid]'";
$result = mysql_query($friends);
echo $friends."<br><br>";
while($friend = mysql_fetch_assoc($result)) {

	$userid = $friend['post'];
	$date = $friend['date'];
	echo $userid."<br />".$date."<br />";	


}

 

it displays this

select * from posts p,friend f where (p.userid=f.userid or p.userid=f.friendid) and f.userid='1'

This is post of user 1
2010-02-02
This is second post of User 10
2010-02-01
This is post of user 1
2010-02-02
This is third post of user 11
2010-02-03
This is post of user 1
2010-02-02
This is last post of user 20
2010-02-05

[doforumda]

my problem is still unsolved