Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13

Thread: query = UPDATE

  1. #1
    New to the CF scene
    Join Date
    Oct 2009
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts

    query = UPDATE

    I am developing a script which will take a number from the url divide it by the number of members in a table add it with the balance already on the table and save the total. Then update the table with the new total.
    All is working fine only the table wont update as I want it to .
    The update is acting as if its updating one record , so if I have 5 records each with a different starting balance they all end up the same after the up date , hope this makes sense .

    Code:
    if(isset($_POST['lotto_id'])){$lotto_id = $_POST['lotto_id'];}
    if(isset($_POST['income'])){$income = $_POST['income'];}
    
    $query = "SELECT balance FROM lotto_member WHERE lotto_id =$lotto_id";
    
    $result = mysql_query($query) or die;
    $num_res = mysql_num_rows($result);
    $num_rows = mysql_num_rows($result);
    for ($i=0; $i<$num_res; $i++){
    $row = mysql_fetch_array($result);
    
    // existing Balance in table
    
    $balance = $row["balance"];
    
    // Divide url income by the number of members
    
    $divide_it = $income / $num_rows;
    
    // add the two together
    
    $new_total = $divide_it + $balance;
    
    // test it
    echo "Total = $new_total<br>";
    
    }
    
    // update the DB
    $query = "UPDATE lotto_member SET balance ='$new_total' WHERE lotto_id='$lotto_id'";
    $result = mysql_query($query)

  • #2
    Regular Coder
    Join Date
    Nov 2007
    Location
    Leeds, UK
    Posts
    514
    Thanks
    24
    Thanked 19 Times in 19 Posts
    Quote Originally Posted by geryatric View Post
    I am developing a script which will take a number from the url divide it by the number of members in a table add it with the balance already on the table and save the total. Then update the table with the new total.
    All is working fine only the table wont update as I want it to .
    The update is acting as if its updating one record , so if I have 5 records each with a different starting balance they all end up the same after the up date , hope this makes sense .

    Code:
    if(isset($_POST['lotto_id'])){$lotto_id = $_POST['lotto_id'];}
    if(isset($_POST['income'])){$income = $_POST['income'];}
    
    $query = "SELECT balance FROM lotto_member WHERE lotto_id =$lotto_id";
    
    $result = mysql_query($query) or die;
    $num_res = mysql_num_rows($result);
    $num_rows = mysql_num_rows($result);
    for ($i=0; $i<$num_res; $i++){
    $row = mysql_fetch_array($result);
    
    // existing Balance in table
    
    $balance = $row["balance"];
    
    // Divide url income by the number of members
    
    $divide_it = $income / $num_rows;
    
    // add the two together
    
    $new_total = $divide_it + $balance;
    
    // test it
    echo "Total = $new_total<br>";
    
    }
    
    // update the DB
    $query = "UPDATE lotto_member SET balance ='$new_total' WHERE lotto_id='$lotto_id'";
    $result = mysql_query($query)
    just run a SELECT * FROM lotto_member WHERE lotto_id='$lotto_id' in phpmyadmin and see how menny results you getting
    Working towards a Internet where we don't have website just browser applications Kill the Hyper-link and say hello to 3D Games in the browser :)

  • #3
    Senior Coder tomws's Avatar
    Join Date
    Nov 2007
    Location
    Arkansas
    Posts
    2,644
    Thanks
    29
    Thanked 330 Times in 326 Posts
    $new_total used in the query is the last value calculated. The loop overwrites it on each pass.
    Are you a Help Vampire?

  • #4
    New to the CF scene
    Join Date
    Oct 2009
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks for the replies sorry its taken me so long to get back .

    I have echoed out the variables and every thing is working , I have 3 affected rows and this is the return ,

    Balance = 1
    Add This = 10
    New Total = 11
    Balance = 2
    Add This = 10
    New Total = 12
    Balance = 3
    Add This = 10
    New Total = 13

    So how would I structure this to update the individual records in the DB

    Thanks again

  • #5
    Senior Coder tomws's Avatar
    Join Date
    Nov 2007
    Location
    Arkansas
    Posts
    2,644
    Thanks
    29
    Thanked 330 Times in 326 Posts
    Move the query into the loop.
    Are you a Help Vampire?

  • #6
    New to the CF scene
    Join Date
    Oct 2009
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks for the Reply Tomws
    I have tried that still no joy here is the latest

    PHP Code:
    if(isset($_POST['lotto_id'])){$lotto_id $_POST['lotto_id'];}
    if(isset(
    $_POST['income'])){$income $_POST['income'];}
    $result mysql_query"SELECT balance FROM lotto_member WHERE lotto_id ='$lotto_id'");
    while(
    $row=mysql_fetch_array($result)){
    $num_rows mysql_num_rows($result);
     
    // existing Balance in table
    $balance $row["balance"];
     
    // divide Income by the number of members
    $divide_it $income $num_rows;
     
    // add the two together
    $new_total $divide_it $balance;
     
    //Update the DB
    $sql_update "UPDATE lotto_member SET balance ='$new_total' WHERE lotto_id= $lotto_id";
    mysql_query($sql_update) or die(mysql_error());


  • #7
    Senior Coder tomws's Avatar
    Join Date
    Nov 2007
    Location
    Arkansas
    Posts
    2,644
    Thanks
    29
    Thanked 330 Times in 326 Posts
    1) I don't know what you're trying to accomplish, but $num_rows may need to be outside the loop.
    2) What's it doing that's wrong?
    Are you a Help Vampire?

  • #8
    New to the CF scene
    Join Date
    Oct 2009
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Tomws
    The script should retrive the balance from each members account ,
    It should then take the income figure from post ,
    Divide the income by the number of members,
    Add the balance and the divided income together .
    this gives me a new total for each member .

    Each members new total may be different ,
    I have echo'd the array and its working and the calculations are working.
    but it looks like the update is only getting one figure .

    Problem
    the update is updating all rows with the same figure.

    Thanks Gery

  • #9
    Senior Coder tomws's Avatar
    Join Date
    Nov 2007
    Location
    Arkansas
    Posts
    2,644
    Thanks
    29
    Thanked 330 Times in 326 Posts
    Ah, ok. It needs a little changing to do that. The reason it's updating every row with the same value is because you're not isolating the update to each user with their own value. You're close, but your logic is a little flawed. Let's see if I can tweak your code to lead you in the right direction.

    Code:
    if(isset($_POST['lotto_id'])){$lotto_id = $_POST['lotto_id'];}
    if(isset($_POST['income'])){$income = $_POST['income'];}
    
    $result = mysql_query("SELECT user_id, balance FROM lotto_members WHERE lotto_id='$lotto_id'");
    $num_rows = mysql_num_rows($result);
    $divide_it = $income / $num_rows;
    
    while($row=mysql_fetch_array($result)){
      
      $user_id = $row['user_id'];  
      
      // existing Balance in table
      $balance = $row["balance"];
       
      // add the two together
      $new_total = $divide_it + $balance;
      
      //Update the DB
      $sql_update = "UPDATE lotto_member SET balance ='$new_total' WHERE lotto_id= $lotto_id AND user_id='$user_id'";
      mysql_query($sql_update) or die(mysql_error());
    }
    I think that's close, but honestly I'm writing this while watching a movie, so I could be wrong.
    Are you a Help Vampire?

  • Users who have thanked tomws for this post:

    geryatric (10-27-2009)

  • #10
    Super Moderator
    Join Date
    Feb 2009
    Location
    England
    Posts
    539
    Thanks
    8
    Thanked 63 Times in 54 Posts
    One thing that disturbs me every time I see it is inserting $_POST data directly into SQL. It makes me sweat. Please don't do '$lotto_id', do '".mysql_real_escape_string($lotto_id)."' or at the very least: if (!is_numeric($lotto_id)) die();
    lamped.co.uk :: Design, Development & Hosting
    marcgray.co.uk :: Technical blog

  • #11
    New to the CF scene
    Join Date
    Oct 2009
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by tomws View Post
    I think that's close, but honestly I'm writing this while watching a movie, so I could be wrong.
    Cant have been much of a movie

  • #12
    New to the CF scene
    Join Date
    Oct 2009
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Resolved query = UPDATE

    Thanks tomws
    User_id was the missing link

    and this needed to be inside the loop as well

    PHP Code:
    $num_rows mysql_num_rows($result);
    $divide_it $income $num_rows
    PHP Code:
    if(isset($_POST['lotto_id'])){$lotto_id $_POST['lotto_id'];}
    if(isset(
    $_POST['income'])){$income $_POST['income'];}

    $result mysql_query"SELECT user_id,balance FROM lotto_member WHERE lotto_id ='$lotto_id'");
    while(
    $row=mysql_fetch_array($result)){
    $num_rows mysql_num_rows($result);

    // existing Balance in table
    $balance $row["balance"];
    $user_id $row["user_id"];

    // divide Income by the number of members
    $divide_it $income $num_rows;

    // add the two together
    $new_total $divide_it $balance;

    //Update the DB
    $sql_update "UPDATE lotto_member SET balance ='$new_total' WHERE lotto_id= $lotto_id AND user_id='$user_id'";
    mysql_query($sql_update) or die(mysql_error());


    Thanks for all your help

    Gery Atric

  • #13
    New to the CF scene
    Join Date
    Oct 2009
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks for the reply ComputerX

    I don't understand what you are trying to tell me , can you expand a little , or can you show me how to form it differently .


  •  

    Posting Permissions

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