field update help

[robinsnest]

Hope someone can help me.

Trying to make it so when a member gets a refferal a field in a table gets updated.

Example :
A member earns a refferal when someone joins my site.
When the new refferal joins a field gets updated by 0.10 cents
Now if that member gets another refferal that 0.10 cents get updated to 0.20 cents and so on...

not sure how to make the update statment using decimals

Also not sure what to set the field " referralmount " to in mysql
Do i use decimals or what.. Below is where i am so far..

if($affid != ""){
	 	if(!e(q("select id from members where id='$affid'")))
		{
		q("update referralamount set amount=amount+'$refamount' where user_id='$affid'");
        }

Not sure how to increment by a set amount each time a member gets a refferal

[jim_keller]

I'm a bit confused. Are you saying that the 2nd referrall would be worth 20 cents in addition to the first 10? Then the third would be worth 30 cents in addition to the 30 cents from the first two?

In either case, I would probably just make an entire table for referalls. This way you have all the data, and you can just use PHP to calculate whatever you need. Even if you want to change the values later, you can just change them in the script and the DB will be fine. It's not the most efficient way of doing it, but it will save you headaches if you want to change something later.

create table referalls (
referallID int unsigned auto_increment not null,
memberID int unsigned not null,
referallDate dateTime not null,
primary key(referallID, memberID)
);

this way, just insert VALUES ( 0, memberID, NOW() ) into that table when someone signs up (where memberID is the person who gave the referall). To get the referall amount, you could do something like

"SELECT * FROM referalls WHERE memberID={$some_member_id}";

and just do the calculations in a loop, like

$referall_amount = 0.0;
$increment = 0.10;
$count = 0;

while ( $row = mysql_fetch_array($db_result) ) {

    $referall_amount += ( $increment * $count );
    $count++;
}

then $referall_amount will have the proper amount.

Jim Keller
http://jim.centerfuse.net/projects/

[robinsnest]

I guess i need to be more clear sorry about that.

It would increment by a set value like 0.10 each time the member gets a referral...

My other problem is this..
If this is his first refferral then it needs to insert affiliate id plus 0.00

then add 0.10 to that

I dont want it to keep adding a row each time he gets a refferal

WIthout having a referral there is no row for that member.

$query = "select * from referralamount where user_id='$affid'";
	    if($affid=='') {
		  query = "INSERT INTO referralamount (`user_id`, `amount`)values('$affid','0.00')";
    }else{
	    query = "update referralamount set amount=amount+'0.10' where user_id='$affid'";
};

The above code doesnt work right, if the member has a row with his info in it, that members amount still increases and he gets another row, which is what i dont want...

What am i doing wrong..

Here is the table

CREATE TABLE referralamount (
  id int(11) NOT NULL auto_increment,
  user_id varchar(255) NOT NULL default '',
  amount float(14,2) NOT NULL default '0.00',
  PRIMARY KEY  (id)
) TYPE=MyISAM;

I need it to check to see if it has a row, and if it does add the increment and if it doesnt then add a row and update it same time

Geeze i hope this makes sence to someone..