Harsh one: How to code 3rd level friends network

[php_guest]

I am building a business networking site. Each user will have own network of contacts, like on LinkedIn. So there will be his direct contacts (1st level), contacts of his contacts (2nd level) and 3rd level contacts.

 

I am not sure what would be the best way to get information about entire user network to 3rd degree level.

 

I have tried many things:

-I created for each friend connection a new row in mysql database (id | userid | friendid). Than did query SELECT * FROM network WHERE userid=$userid, than with PHP while loop and query for each friend to get all contacts of user friends and than again the same to get 3rd level.

-all 1st level friends in 1 field as text (separate them with : and than use PHP explode). Than do similar same to get 2nd and 3rd level.

-again for each 1st level friend connection a separated row but all 2nd and 3rd contacts into 1 field as text (id | userid | friendid | networkoffriend (separate 2nd level with ":" and 3rd level with ";" ).

 

All options are far too slow. I have no idea how LinkedIn, Xing and other business social networking sites are doing this because if each user has 100 friends than there is 1mio users in 3rd level.

 

Has anybody any idea?

 

Thanks a lot!

[fenway]

As soon as you mentioned "explode" and "while loops", I knew you were in trouble.

 

You'd have to handle this all in sql -- and you don't have to do it in real-time.