Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004

    Unanswered: PHP + mySQL math question

    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????


  2. #2
    Join Date
    Apr 2004
    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:

    "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.

  3. #3
    Join Date
    Apr 2002
    Toronto, Canada
    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 | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2004
    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:



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


      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() );


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts