...

View Full Version : need in getting data from db



doforumda
02-09-2010, 10:39 AM
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 />";
}
}
?>

here are my friend and post tables

koko5
02-09-2010, 10:52 AM
Hi,
try this (single query instead 2) :


$query = "SELECT * FROM posts
WHERE userid = (SELECT friendid FROM friend
WHERE userid = {$_SESSION['userid']}) ";

Regards

doforumda
02-09-2010, 11:01 AM
Hi,
try this (single query instead 2) :


$query = "SELECT * FROM posts
WHERE userid = (SELECT friendid FROM friend
WHERE userid = {$_SESSION['userid']}) ";

Regards

i tried this way

$friends = "SELECT * FROM posts
WHERE userid = (SELECT friendid FROM friend
WHERE 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 is not working this way
is this right if not please tell me how can i write it in correct way

koko5
02-09-2010, 11:07 AM
$friends = "SELECT * FROM posts
WHERE userid = (SELECT friendid FROM friend
WHERE userid = {$_SESSION['userid']}) ";
$result = mysql_query($friends) or die('Error 1');
while($r = mysql_fetch_assoc($result)) echo "{$r['post']} {$r['date']}<br />";

is it ok?

doforumda
02-09-2010, 11:13 AM
$friends = "SELECT * FROM posts
WHERE userid = (SELECT friendid FROM friend
WHERE userid = {$_SESSION['userid']}) ";
$result = mysql_query($friends) or die('Error 1');
while($r = mysql_fetch_assoc($result)) echo "{$r['post']} {$r['date']}<br />";

is it ok?

no it is not working it says Error 1

koko5
02-09-2010, 11:17 AM
hmm try


$result = mysql_query($friends) or die(mysql_error());
to get the exact error code...

doforumda
02-09-2010, 11:23 AM
hmm try


$result = mysql_query($friends) or die(mysql_error());
to get the exact error code...

it displays subquery returns more than 1 row

koko5
02-09-2010, 11:26 AM
it displays subquery returns more than 1 row
This should work:


$friends = "SELECT * FROM posts
WHERE userid IN (SELECT friendid FROM friend
WHERE userid = {$_SESSION['userid']}) ";
$result = mysql_query($friends) or die(mysql_error());
while($r = mysql_fetch_assoc($result)) echo "{$r['post']} {$r['date']}<br />";

:)

doforumda
02-09-2010, 11:37 AM
This should work:


$friends = "SELECT * FROM posts
WHERE userid IN (SELECT friendid FROM friend
WHERE userid = {$_SESSION['userid']}) ";
$result = mysql_query($friends) or die(mysql_error());
while($r = mysql_fetch_assoc($result)) echo "{$r['post']} {$r['date']}<br />";

:)

it is still not working properly becoz it does not display posts of logged in user

koko5
02-09-2010, 12:08 PM
OK, replace $friends with:


$friends = "SELECT * FROM posts
WHERE userid={$_SESSION['userid']} OR userid IN (SELECT friendid FROM friend
WHERE userid = {$_SESSION['userid']})";

This will show currently logged-in user's posts+his friends posts.

doforumda
02-09-2010, 12:17 PM
OK, replace $friends with:


$friends = "SELECT * FROM posts
WHERE userid={$_SESSION['userid']} OR userid IN (SELECT friendid FROM friend
WHERE userid = {$_SESSION['userid']})";

This will show currently logged-in user's posts+his friends posts.

thanks man you are great

koko5
02-09-2010, 12:46 PM
My pleasure :)

I've PM-ed you extended example, based on this code: hope you'll like it and it works, because I've appended few things in the pm-form directly.I'm sure that you'll fix it if I've missed any quote or bracket.
Regards



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum