----- Original Message ----- From: "Daren Cotter" <[EMAIL PROTECTED]> To: "MySQL Mailing List" <[EMAIL PROTECTED]> Sent: Wednesday, September 25, 2002 7:34 PM Subject: Multiple Referral Levels
> I have a question regarding tracking multiple referral > levels in a database. The number of referral levels > tracked needs to be at least 4, but should be able to > be expanded later (without modifying the database). > > The first design I considered was: > > table: > id int(8) unsigned not null auto_increment, > referer int(8) unsigned null, > primary key (id), > key tbl_referer(referer)); > > What I need to be able to do is give a breakdown of > the # of members referred (on each level) for a > specific member...say, member 10. Getting the # of > referrals on level 1 is no problem: > > SELECT COUNT(*) FROM table WHERE id = 10 > > The second level isn't too tough either, using a > simple join. But what about when I get down to level > 4? Is it even possible to get this info in one query? > How about level 10? > Yup, you can but you have to generate your query at runtime. I've done this with PHP. To be honest I worked for a company that supported site with referral system up-to 8 levels. So the trick is to generate query at runtime. Just LEFT JOIN the table to itself. Some kind of "for" loop however you must name your table every time differently. So: Select * from user_table as tb1 left join user_table as tb2 on (tb2.referer = tb1.id) left join user_table as tb3 on (tb3.referer = tb2.user_id).... and so on. AFAIK the you can join up to 32 tables in a join. Andrey --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
