genealogy in MLM, update by adding 1 to the value of existing rows with mysql

[francis Ebhonaiye]

Hello! can anyone help please!!!!!

I have a mysql table called sponsor_info like this...

 

user_id,  sponsor_id,  num_rate,  qualify,

 

when members filled the form and submit, the 'user id' and 'sponsor id' submit to the respecfull field, but as this happen! num_rate becomes 1 and qualify becomes 1, if another user filled the form with an 'existing user id', num_rate becomes 1 and qualify increment to 2 and all the other upline are incemented by 1

 

just like a geneology in MLM network marketing.

 

please! i really need a mysql querey that can better work for this, this is what i am using now!

 

$num_rate=$row_getcolname['num_rate']+1;

 

$insert2 = mysql_query("UPDATE sponsors_info SET num_rate='$num_rate', qualify='$num_rate' WHERE user_id='$sponsor_id'")or die("Could not insert data because ".mysql_error());

 

which is incrementing but not upgrading the upline. 

 

[kickstart]

Hi

 

Not quite sure what you want.

 

What do you mean by "upline". Are there parent records that need to be updated? And then parents of those parents?

 

Or do you need to do an insert if no record already exists?

 

All the best

 

Keith

[Maq]

You can update these fields directly in MySQL:

 

UPDATE sponsors_info SET num_rate=num_rate+1, qualify=qualify+1 WHERE user_id='$sponsor_id'

[francis Ebhonaiye]

thanks Maq for your response, but you see! thats only update 1 row,

The issue here is that i am bulding a network marketing site, i need a Hierarchical mysql query that can update sponsors by adding one to thier initial value in qualify, in that case whenever a sponsor brought somebody and the form is filled online it cheks out the sponsor id on user_id if the sponsor exist it then uprade all the upline by1

[Maq]

Oh, sorry about that.  Like kickstart already asked, what exactly do you mean by "upline"?

[kickstart]

Hi

 

It seems that what you have is a tree structure, and when you update a branch by adding to a count you want to update all the parent branches

 

Ie, in a pyramid selling scheme where person 1 has person 2 and 3 under them, and person 3 has person 4 and 5 under them. If person 5 sells something them those in line above them get something added to their account.

 

2 main ways to do this. Either recursively with multiple UPDATEs (simple to understand but not that efficient) or using sets, which makes inserts slower and is harder to understand but should be more efficient for reads and updates. This link might explain it for you:-

 

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

 

All the best

 

Keith