Store and retrieve friends contact list using mysql

I want to create a database that can store the friends contact list as like social networking what is the best way to design the database structure and easy to retrieve the contacts of f开发者_开发百科riends using mysql.

I need solution for this, HELP ME

The best way to model heriarchical data depends on what operations you need to support. I would suggest that you read Bill Karwin's slides Models for heirarchical data for a comparison. See in particular slide 48 where there is a summary of the strengths and weaknesses of each approach.

However, I wouldn't regard friendship as a heirarchical structure. There will normally be loops: A is friends with B, B is friends with C, and C is friends with A. Instead you can create a contact table with two columns: user_id and friend_id which are foreign keys into the "users" table:

contact_list
------------------
user_id  friend_id
------------------
1        2
2        3
3        1

To retrieve the contact list for a specific user id run this query:

SELECT friend_id
FROM contact_list
WHERE user_id = 1

Here I'm assuming that A being on B's contact list does not imply that B is also on A's contact list.