PHP + mySQL math question

visiontrip · #1 (**permalink**) 04-17-04, 04:17

Okay, so I set up a field in mySQL called "ref_num". It's the amount of times a person referred another to the site. The problem I'm having is that there is another auto_increment field in the same table, so I can't have this be an auto_increment as well.

The solution I've thought of, is simply asking mySQL what number is currently there and then adding +1 to it and replacing with the result.

Now, here is my current sql query.

PHP Code:


			
$query2 = "update resellers set active='1' ref_num ='CODEHERE' where id='$item_number'";

What do I add to this query, to make this solution work????

Thanks!

visiontrip · #2 (**permalink**) 04-17-04, 04:38

Okay, just figured out a second problem.

That above query would not work correctly.

Here's actually what should happen. If BOB signs up, the system first adds his info to the database and knows who referred him by adding their id number to the field "refferer". Then after they pay, it activates their account by setting the "active" field to 1.

Now, after paying through paypal, the current sql query just activates them. But I want to increase the "ref_num" field by 1 for the person that referred them. So the query should look like this:

PHP Code:


			


$query2 = "update resellers set active='1' where id='$item_number' set ref_num=ref_num+1" where id='xy(refferer field in same row as BOB)';

So, what's the correct syntax for this??? updating the active field for the current row (where id=$item_number), then updating the ref_num field by 1 for the person that referred them. We know who referred them because the refferer field tells us the id number of the person that referred them.

r937 · #3 (**permalink**) 04-17-04, 08:05

the correct syntax is

UPDATE tablename
SET column1 = value
[ , column2 = value ] ...
WHERE condition

you cannot have different conditions for different rows to update in the same single statement

visiontrip · #4 (**permalink**) 04-17-04, 14:18

Actually I figured it out, I just made three different queries and that took care of the problem.

Here's my code if anyone is interested:

PHP Code:


			
<?



$date = date(Y.'-'.m.'-'.d);







require("/home/thexyfac/config.php");



$db = mysql_connect("localhost", "$dbUser", "$dbPass");



mysql_select_db("$database",$db)



  or die ("Could Not Open $db: ".mysql_error() );





/* Activate user in database */



$query2 = "UPDATE resellers SET active='1' WHERE id='$item_number'";



$result2 = mysql_query( $query2, $db )



or die ("Could not add values to table:" .mysql_error() );



/* Select referral id of referrer */



$referralselectquery = "SELECT refferer FROM resellers WHERE id='$item_number'"; 



$referralselectexecute = @mysql_query($referralselectquery) or die("could not pull referral id");



$referralselectresult = mysql_fetch_assoc($referralselectexecute);



$referralidnum = "$referralselectresult[refferer]";



/* Increase referral count by 1 */



$referralupdatequery = "UPDATE resellers SET ref_num = ref_num +1 WHERE id='$referralidnum'";



$referralupdateresult = mysql_query( $referralupdatequery, $db )



or die ("Could not increase referral count by 1" .mysql_error() );





?>

Internet Services

Web Development

Digital Marketing

Consumer Tech