i only know of one way which is very resource-intensive.

for eg. for 3 levels down, to find if a person is linked to you,

1) search mysql db for friends who are directly linked to you.

2) if not found, search for your friends' friends for a match

3) if not found, search for your friends' friends' friends for a match

as you can see, the above code is very resource intensive.

imagine if you have 200 friends and your friends have 200 friends each and your friends' friends' friends have 200 friends too.

the loop will totally crash the server.

what is a more efficient way of searching for a match?

I can't really say what you would define as "too resource intensive", but you could probably do this with a dynamic query and a bunch of joins.

That is, chances are you could do it in a single query, with multiple joins on the same table. No loops required.

Originally posted by krumms
I can't really say what you would define as "too resource intensive", but you could probably do this with a dynamic query and a bunch of joins.

That is, chances are you could do it in a single query, with multiple joins on the same table. No loops required.

huh? joins? how do u do it with joins? eg?

You are underestimating MySQL.

Try doing your loops and time the execution duration.
I doubt that it is going to crash your server unless you have all the 3 levels of friends all accessing the server simultaneously.

precisely. that is the prob. imagine you have 100 or 1000 ppl running the script simultaneously.

server will be crashed.

I doubt that your mysql is going to crash.
WHT has 904 simultaneous users at the time Im typing this msg and it seems to be handling it pretty well.

DO not worry too much. If you worry too much, you will not get anything accomplished.

JUST DO IT
-Nike

Originally posted by Roy@ENHOST
I doubt that your mysql is going to crash.
WHT has 904 simultaneous users at the time Im typing this msg and it seems to be handling it pretty well.

DO not worry too much. If you worry too much, you will not get anything accomplished.

JUST DO IT
-Nike

haha i've tried it. It's painstaking slow for even 1 person (me).

took 3 seconds to process it.

anyway to do it with joins in one query?

Lets say you have a relations table with the fields person and friend.

create table relations(
person char
, friend char
, primary key (person, friend)
);

create index idx_relations_person on relations (person);
create index idx_relations_friend on relations (friend);

insert into relations (person, friend) values ('a', 'b');
insert into relations (person, friend) values ('a', 'c');
insert into relations (person, friend) values ('a', 'd');
insert into relations (person, friend) values ('a', 'e');
insert into relations (person, friend) values ('b', 'f');
insert into relations (person, friend) values ('b', 'g');
insert into relations (person, friend) values ('b', 'h');
insert into relations (person, friend) values ('b', 'a');
insert into relations (person, friend) values ('c', 'b');
insert into relations (person, friend) values ('f', 'r');


SELECT r0.friend
, r1.friend
, r2.friend
, r3.friend
FROM relations r0
LEFT JOIN relations r1 ON r0.friend = r1.person
LEFT JOIN relations r2 ON r1.friend = r2.person
LEFT JOIN relations r3 ON r2.friend = r3.person
WHERE r0.person = 'a'
ORDER BY 1, 2, 3, 4

You will get somethign like:

b a b a
b a b f
b a b g
b a b h
b a c b
b a d
b a e
b f r
b g
b h
c b a b
c b a c
c b a d
c b a e
c b f r
c b g
c b h
d
e


This can be extended to whatever depth that you want. It still may be slow as molasses.

what is r1, r2, r3 and r4?

temp tables?

Originally posted by lobaloba9
what is r1, r2, r3 and r4?

temp tables?

Nah . . . they are just aliases to the same table. Do a google search for self join, and you will find a lot of info about it.