Incorrectly sum in a while loop

Question

$dt = mysql_query("SELECT * FROM `member_territory` mt LEFT JOIN `drug_territory` dt ON mt.mt_ter = dt.t_id");

 while($t = mysql_fetch_array($dt)) {

     $total +=($t['t_reward']* $t['mt_lev'])*150;

     mysql_query("UPDATE `members` SET 
                 `drug_income` = '".$total."',
                 `drug_incometotal` = `drug_incometotal` + '".$total."', 
                 `wallet` = `wallet` + '".$total."' 
                 WHERE `playerid` = '".$t['mt_playerid']."'");

  }

So here is my code rather self explainingtary $total when inserted into drug_income that is correct but when ever it is inserted into drug_incometotal or wallet its incorrect.

im not sure why and i have tried everything to my knowledge to pull my head around it!!.

Any ideas why i am getting this incorrect result ( as i say drug_income is correct) only when i try to '+' it to something in the data base it returns an incorrect result.

What part of the code is incorrect.. :) seems fine as part of it inserts correctly but the rest doesnt. — user3740302, Jun 14 '14 at 12:03
Seems to me like the code works fine, but your algorithm is wrong. Are you sure you want to be incrementing `drug_incometotal` by the incrementing `$total` variable every time, or did you mean to only increase it once after the loop? — Niet the Dark Absol, Jun 14 '14 at 12:06
You're safe from SQL Injection in this case, but you should still be using [parameterized queries](http://stackoverflow.com/q/60174/812837) anyways. You also appear to be adding `total` as a string, which might be having an effect. You should be able to do this as a single statement - ie `UPDATE members FROM member_territory` (or whatever), which should _also_ be faster than pulling the data into php for the calculation. `drug_incometotal` appears to be a derived value, which generally shouldn't be stored - can you just query for this when necessary (or use a view)? — Clockwork-Muse, Jun 15 '14 at 22:23

score 1 · Accepted Answer · answered Jun 14 '14 at 12:10

1

i dont want to increment just once after the loop... – user3740302 1 min ago

Okay then. So you should probably move your UPDATE query outside of the loop, right?

answered Jun 14 '14 at 12:10

Niet the Dark Absol

320,036
81
464
592

If i remove it out of the while loop it doesnt update at all :/ – user3740302 Jun 14 '14 at 12:11
That would be because you're using `$t['mt_playerid']`. Is there anywhere else you can get the player ID from? – Niet the Dark Absol Jun 14 '14 at 12:11
make a new query i guess... let me look thanks for your help..? – user3740302 Jun 14 '14 at 12:12
*hand over face* ..... thanks its has been fixed ;) fetched the previous query again outside of the while loop :) – user3740302 Jun 14 '14 at 12:17
...Except the query is looping to update _multiple rows_, so it might be better to say he needs a _second_ `UPDATE` outside the loop... or a slightly different db design. – Clockwork-Muse Jun 15 '14 at 22:27

score 0 · Answer 2 · answered Jun 14 '14 at 12:06

0

You may try this modified update query

mysql_query("UPDATE `members` SET drug_income = ".$total.", drug_incometotal = drug_incometotal + ".$total.", wallet = wallet + ".$total." WHERE `playerid` = '".$t['mt_playerid']."'");

It may resolve your problem..

answered Jun 14 '14 at 12:06

K.K.Agarwal

846
5
10

2

I'm having trouble spotting the difference. What have you changed, and why? – Niet the Dark Absol Jun 14 '14 at 12:06
because in your update query you are using $total as a string type value that will be concatenated in your current value – K.K.Agarwal Jun 14 '14 at 12:08
So what? `+` casts its arguments to numbers anyway. – Niet the Dark Absol Jun 14 '14 at 12:09

Incorrectly sum in a while loop

2 Answers2