Use data from one row for another?

[ffxpwns]

Okay, my table structure has columns called 'nick' (username) 'refer' (Who referred this person) and 'balance'  There are a lot of other ones, but these are the ones that matter.  So when someone signs up, they put in their referrers username and it does nothing.  And when the referred user buys something, the referrer gets 5$.  Here is my question.  How would I write an UPDATE where the name in the refer field would award the referrer with +5 to balance, then it would clear the 'refer' field.  I got the set refer to blank.  But I'm not very good with MySQL commands.  Heres what I have

 

$query = "UPDATE " . $DBPrefix . "users SET refer=' ' 

[Barand]

+------+-------+------------+

| nick | refer | balance    |

+------+-------+------------+

|  A  |  B    |  0.00    |

+------+-------+------------+

|  B  |  C    |  0.00    |

+------+-------+------------+

 

A buys so B gets $5 added to his balance and the referrer is cleared from A, giving this after the update

 

+------+-------+------------+

| nick | refer | balance    |

+------+-------+------------+

|  A  |      |  0.00    |

+------+-------+------------+

|  B  |  C    |  5.00    |

+------+-------+------------+

 

Is this scenario correct?

[ffxpwns]

+------+-------+------------+

| nick | refer | balance    |

+------+-------+------------+

|  A  |  B    |  0.00    |

+------+-------+------------+

|  B  |  C    |  0.00    |

+------+-------+------------+

 

A buys so B gets $5 added to his balance and the referrer is cleared from A, giving this after the update

 

+------+-------+------------+

| nick | refer | balance    |

+------+-------+------------+

|  A  |      |  0.00    |

+------+-------+------------+

|  B  |  C    |  5.00    |

+------+-------+------------+

 

Is this scenario correct?

 

That looks prefect to me!

[Barand]

in which case

 

$user = 'A';


UPDATE users a
INNER JOIN users b ON a.refer = b.nick
SET a.refer = '', b.balance = b.balance + 5
WHERE a.nick = '$user'

[ffxpwns]

in which case

 

$user = 'A';


UPDATE users a
INNER JOIN users b ON a.refer = b.nick
SET a.refer = '', b.balance = b.balance + 5
WHERE a.nick = '$user'

 

Sorry, I'm really new to PHP, How would I call the users A and B?  I have my code here http://pastebin.com/rwcN7FeJ.  You can see on line 241 is where I was trying to do this.  This is an open source auction script called WeBid.

[Barand]

in my example, user A is the one who bought the goods. B is A's referrer and gets the $5 commission

[ffxpwns]

in my example, user A is the one who bought the goods. B is A's referrer and gets the $5 commission

 

Yes, but you have

$user = 'A';


UPDATE users a
INNER JOIN users b ON a.refer = b.nick
SET a.refer = '', b.balance = b.balance + 5
WHERE a.nick = '$user'

 

And I do not understand how I would define user A and B.  Could you please look at a little snippet of the code and tell me how to do this?  I'm so lost.

[Barand]

I changed my query slightly as you are coming at it using "refer" rather than "nick".

 

$referName = mysql_real_escape_string($gateway_data['refer']);
$query = "UPDATE users a
        INNER JOIN users b ON a.refer = b.nick
        SET a.refer = '', b.balance = b.balance + 5
        WHERE b.refer = '$referName'";
mysql_query($query);

[ffxpwns]

I changed my query slightly as you are coming at it using "refer" rather than "nick".

 

$referName = mysql_real_escape_string($gateway_data['refer']);
$query = "UPDATE users a
        INNER JOIN users b ON a.refer = b.nick
        SET a.refer = '', b.balance = b.balance + 5
        WHERE b.refer = '$referName'";
mysql_query($query);

 

I'm sorry, sir, but I still don't understand how I would select who user A and B were?  http://pastebin.com/rwcN7FeJ at line 22, I have add to account.  Maybe it could be incorporated there?

[smoseley]

You only have to supply user A - the DB meta-data already defines the A-B relationship.

[ffxpwns]

You only have to supply user A - the DB meta-data already defines the A-B relationship.

 

http://farm5.staticflickr.com/4119/4876696414_d3f62d18be_z.jpg 

 

Now that that is out of the way, seriously?  So if I have something that says $user = ['ffxpwns'] would that suffice?  Or would I have to put like 'A' => $user or something.

[ffxpwns]

You only have to supply user A - the DB meta-data already defines the A-B relationship.

 

I mean, I know this sounds lame.  But that actually blew my mind in a small, nerdy way.