mysql 好友列表设计_好友列表数据库的简单设计

I have a small issue with making a friendship system database.

now I have a table called friends

let's say:

table friends:

you friend approve since

_________________________________________________

wetube youtube 1 4-12-2012

facebook wetube 1 4-12-2012

and i have this query code to fetch the friends of user called wetube.

mysql_query("SELECT f.* FROM friends f inner join users u on u.username =

f.you WHERE f.friend = 'wetube' UNION ALL SELECT f.* FROM friends f inner join users u on

u.username = f.friend WHERE f.you = 'wetube'");

now what I want exactly is how to fetch the friens of wetube and show it to him on his page.

fixed:

Finally I fixed the problem.

so this is the table:

CREATE TABLE IF NOT EXISTS `friends` (

`id` int(20) NOT NULL AUTO_INCREMENT,

`you` varchar(255) NOT NULL,

`friend` varchar(255) NOT NULL,

`type` varchar(255) NOT NULL,

`since` date NOT NULL,

`message` text NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `id` (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

and this is the php code to fetch the user friends

$username = $_SESSION['username'];

$friends_sql = mysql_query("SELECT * FROM friends WHERE (friend = '$username' OR you = '$username') ");

while($friends_row = mysql_fetch_assoc($friends_sql)){

if ($username == $friends_row['you']) {

echo $friends_row['friend']."
";

} elseif($username == $friends_row['friend']) {

echo $friends_row['you']."
";

}

}

?>

try it yourself it works 100%

解决方案$result = mysql_query("SELECT * FROM friends WHERE friend='wetube'");

$row = mysql_fetch_array($result);

echo $row['friend'], ' - ', $row['since'];