Multi level referral system php and mysql

[nomirobin]

I am currently developing a MLM PHP script, where I need some suggestions OR code examples (would be better) about giving bonus to the all referrers.

Like:

username    referrer
--------    --------

A
B           A   // UPDATE USERNAME A WITH +5 POINTS //
C           B   // UPDATE USERNAME A WITH +3 POINTS AND USERNAME B WITH +5 POINTS //
D           C   // UPDATE USERNAME A WITH +3 POINTS AND USERNAME C WITH +5 POINTS //
And so on....

My current registered users table is something like

users (table)

- Ref_ID (referrer User ID)
- UserID
- Email
- password
- points
- created_at
- update_on

I want to know that how can I do the above thing to give points to all upper line users as I mentioned above, I want to do all this in PHP with mysql queries. I am a beginner so therefore I don't know the exact structure to do the similar things. Please guide me to get work on this.

[Dani]

Sorry for taking so long to see this post and respond to it. What is an upper line user? I guess you're saying that if a user referred someone directly, give them 5 points, and then additionally give them 3 points if someone they refer refers someone. Hmm, let me think of the pseudocode for this and how to translate it into a MySQL query or two:

UPDATE users
SET points = points + (5 * (SELECT COUNT(*) FROM users AS foo WHERE foo.Ref_ID = users.id))

I'm not sure if this will work, but what I'm trying to do is, for each user, update their points value by the sum of (5 multiplied by how many people they referred).