CODEWITHSUNDEEP
phpmysql
Mustafa M Jalal
Mustafa M Jalal

Reputation: 421

Simple Design of friends list database

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

<?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%

Upvotes: 1

Views: 3120

Answers (5)

scorpio1441
scorpio1441

Reputation: 3088

$result = mysql_query("SELECT * FROM friends WHERE friend='wetube'");
$row = mysql_fetch_array($result);

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

Upvotes: 3

user1269636
user1269636

Reputation:

Not a direct answer to your question, but a completely normalised database would have two tables for this system.

Since this is a many-to-many relationship, I would do something like this:

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 normalised database, it will be much more easy to manage and create your queries.

Upvotes: 2

Yogurt The Wise
Yogurt The Wise

Reputation: 4489

Maybe not understanding this exactly.

Select * from Friends f where f.you = 'wetube' or f.friend = 'wetube'

Guess your looking for user info as well, reason for inner joins.

Maybe removing the approval column, and have 2 records. The 2nd record is when the friend approves as a friend. Then you can easily see who YOU are friends with, and they in return are friends with.

wetube --> youtube
facebook --> youtube
youtube --> wetube  wetube would approve a friendship request for youtube, adding a record
wetube --> facebook

Then you could much easily ask who is friends of wetube. Just an idea, probably not the answer you were looking for.

Upvotes: 1

Richard Barraclough
Richard Barraclough

Reputation: 2964

All of the wetube's friends are:

SELECT * FROM friends WHERE (friend = 'wetube' OR you = 'wetube') AND approve = 1

I would suggest removing the column approve and instead keeping a table of requests. This will mean that all approved requests are in the friends table and all pending approvals are in the friend_request (or whatever) table.

This two table design is more efficient because you do not always have to check that approve = 1 when you want to show friends (which is probably pretty often).

Upvotes: 1

Russell Fox
Russell Fox

Reputation: 5427

You mean just the rows that have wetube in the [friend] column? You might just be over-thinking the joins:

SELECT * FROM [friends] WHERE [friend] = 'wetube'

If you want where wetube is in either 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'

Upvotes: 1

Related Questions