Simple friend list database design
I have a little problem creating 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 request code to pull the friends of a 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 is exactly how to get wetting piers and show it to him on my page.
fixed:
Finally, I fixed the problem. so this is a 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 php code for fetching user's friends
<?php
$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']."<br/>";
} elseif($username == $friends_row['friend']) {
echo $friends_row['you']."<br/>";
}
}
?>
try it yourself, it works 100%
source to share
Not a direct answer to your question, but a fully normalized database will have two tables for this system.
Since this is a many-to-many relationship, I would do the following:
UsersTable
------------------
id: int, serial
name: varchar
email: varchar
...
and
RelationshipTable
------------------
id: int, serial
user1_id: int, foreign key on UsersTable.id
user2_id: int, foreign key on UsersTable.id
approved: boolean
since: date
With a properly designed and normalized database, it will be much easier to manage and build your queries.
source to share
Do you mean only rows containing wetting in column [friend]? You can just change your mind what is joining:
SELECT * FROM [friends] WHERE [friend] = 'wetube'
If you want grease on any column:
SELECT * FROM [friends] WHERE [friend] = 'wetube' OR [you] = 'wetube'
Or:
SELECT * FROM [friends] WHERE [friend] = 'wetube'
UNION ALL
SELECT * FROM [friends] WHERE [you] = 'wetube'
source to share
All Smiley Friends:
SELECT * FROM friends WHERE (friend = 'wetube' OR you = 'wetube') AND approve = 1
I would like to suggest removing the column approval and keeping the queries table instead. This will mean that all approved requests are in the friend table and all pending approvals are in the friend_request table (or whatever).
This two table design is more efficient because you don't always have to check that approve = 1 when you want to show friends (which is probably quite common).
source to share
Perhaps this is not entirely clear.
Select * from friends f where f.you = 'wetube' or f.friend = 'wetube'
Guess you are looking for user information, reason for internal connections.
It is possible to delete the approval column and have 2 entries. The second entry is when a friend approves of a friend. Then you can easily see who you are friends with, and they in turn are friends.
wetube --> youtube
facebook --> youtube
youtube --> wetube wetube would approve a friendship request for youtube, adding a record
wetube --> facebook
Then you could easily ask who is wetting's friend. Just an idea, maybe not the answer you were looking for.
source to share