...

View Full Version : query = UPDATE



geryatric
10-17-2009, 11:13 PM
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 .



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)

barkermn01
10-17-2009, 11:34 PM
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 .



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

tomws
10-18-2009, 03:41 AM
$new_total used in the query is the last value calculated. The loop overwrites it on each pass.

geryatric
10-24-2009, 04:35 PM
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

tomws
10-24-2009, 04:39 PM
Move the query into the loop.

geryatric
10-25-2009, 12:00 AM
Thanks for the Reply Tomws
I have tried that still no joy here is the latest


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

tomws
10-25-2009, 01:58 AM
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?

geryatric
10-25-2009, 10:03 AM
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

tomws
10-26-2009, 02:31 AM
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.


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. :D

Lamped
10-26-2009, 02:39 AM
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();

geryatric
10-27-2009, 09:46 PM
I think that's close, but honestly I'm writing this while watching a movie, so I could be wrong. :D

Cant have been much of a movie :p

geryatric
10-27-2009, 09:54 PM
Thanks tomws
User_id was the missing link

and this needed to be inside the loop as well



$num_rows = mysql_num_rows($result);
$divide_it = $income / $num_rows;

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

geryatric
10-27-2009, 11:26 PM
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 .



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum