multiple queries / level

I am making a referral开发者_开发技巧 system, and it needs to have multiple (up to 10) levels. So basically when someone signs up, it will set them at a referral id. So I need to be able to add up to 10 levels in the levels table, then display all the referrals for those levels. If you dont understand, ask and I'll try explaining it better.

If I understand you correctly, you want 2 tables, users and referrals.

users has userid (PK) and other user data, like name and stuff.

referrals has refid (PK), userid (FK), useridref (FK) and referral data.

When you need to see what referrals a user has, you do something like SELECT * FROM referrals WHERE userid=?.

edit: OK so I finally get the levels now. In the general case you'd need a recursive query, but if you only want to test for one level at a time, you can do it like this:

select usersreferred.* 
from referrals r1                                   -- level 1
  inner join referrals r2 on r1.useridref=r2.userid -- level 2
  inner join referrals r3 on r2.useridref=r3.userid -- level 3
  inner join users usersreferred on usersreferred.userid=r3.useridref
where r1.userid=?

You have to manually build the join query string, but that's the general idea. Given a user id, that query will show you all the level 3 users that got referenced to the site by the selected user.