Need help with update query in a loop.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Need help with update query in a loop.

Post by cdoyle »

Hi, Hope someone can see what I'm doing wrong with my loop, and update query.

What I'm trying to do is, in my game if a person uses this weapon it will randomly select other users.
For each person that it kills it gives the player 8 exp points. The 'if' in the loop, is suppose to check for each attack, if $bombexp+$player->exp is less then the players maxexp. It adds to $bombexp to the players exp.

If $bombexp +$player->exp would exceed the maxexp, it would increase their level and then go from there over and over until it reaches the end of the loop.

The problem I'm seeing is, if the query is ran I don't think it's checking for each row. What I mean is, if $bomb+$player->exp is less then max exp for the first row of the select query, it doesn't seem to check the rest of the rows. So a player could end up with a exp greater then maxexp.

Then the next time I run it, since the first row would now have a exp greater then maxexp, it then updates the level but then it assumes that each row is exceeds the maxexp, and increases the level by 1 for each row in the query. So the player ends up gaining 20 levels.

So I must just have my if statement in the wrong place right?

Here is what I have so far.

Code: Select all

 
 $bombexp = 8;
            $newexp = $bombexp + $player->exp - $player->maxexp;
            $selectplayers = $db->execute("select p.id, p.username from players as p
                                              left join medical_ward as m on ( m.player_id = p.id )
                                              where p.City_ID=? and m.player_id is null and p.id !=?
                                              ORDER BY RAND() Limit 20", array($player->City_ID, $player->id));
            while ($selectplayers1 = $selectplayers->fetchrow())
 
            {
                if ($bombexp + $player->exp < $player->maxexp)
                {
                    $queryupdate = $db->execute("UPDATE `players` set `exp`=`exp`+? where `id`=?",array($bombexp,$player->id));
                }
                else //player has gained a level! =)
                {   
$query = $db->execute("UPDATE `players` set `level`=`level`+?, `maxexp`=?, `maxhp`=?, `exp`=?, `kills`=`kills`+?, `hp`=?, `energy`=?, `maxenergy`=?, `Max_Intent`=`Max_Intent`+? where `id`=?", array(1, ($player->level+1) * 70 - 20, $player->maxhp + 30, $newexp, 1, 0, 0, $player->maxenergy + 2, 1, $player->id));
                }
            }
 
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Re: Need help with update query in a loop.

Post by Burrito »

what do the values show when you echo them out (outside of your update statements)?
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need help with update query in a loop.

Post by cdoyle »

Do you mean to echo out the exp of the player?

if so, I did and it does appear to not be checking past the first loop.

I had the exp set to 20, and when I echoed the script,
it just displayed 20, twenty times.

So it's not looking at the updated value after the exp has been added each time.

Any ideas how to fix that?
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Re: Need help with update query in a loop.

Post by Burrito »

so just to clear this up for me: each row in your table should have a different value for the exp of the players?

all that you're selecting in your query is the player id and the username. I don't know where the $player->exp property is being set but that's the first place I'd look.
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need help with update query in a loop.

Post by cdoyle »

No, that's not what this does.

I'm not all the way done with it yet, but it doesn't matter what the other players in the game exp are.

Only the player who uses this weapon, and starts this query.

For each person the select query randomly selects, it will set their HP to 0 and give this player 10 exp each.

Currently the query is set toselect 20 players from the game, (eventually it will set these 20 players HP to 0).
It will then give the player who used the weapon 200exp (10 exp for each player it killed).

So for example the max_exp for the player is 100 for his current level and he is currently at 80.
He sets this weapon off, so it gives him 10 exp for the first person it killed, then 10 for the next. Then because he's now at 100exp now, the next person it kills should bump him up 1 level (resetting his max exp etc). Then it will continue again giving 10 till it selects all 20.

When I said I echoed the exp, it was the player who set the weapon off. Instead of the exp increasing each time. It just displayed the same value. Which to me means I need to code something to make it requery the players stats each time., and then rerun the 'IF'.

Does that make sense?
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Re: Need help with update query in a loop.

Post by Burrito »

you're setting this:

Code: Select all

 
$newexp = $bombexp + $player->exp - $player->maxexp;
 
above your loop, if you need to be incrementing that value, you're going to need to change it in your loop.
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need help with update query in a loop.

Post by cdoyle »

Ok, I tried this
but it still allows the players exp to exceed the max exp and doesn't gain a level until the next time I run it.
It's either like the if statement is only being checked when the first row is selected, or it's not using the player's current exp on the second row. Meaning if before this is ran the player has 10 exp, it thinks he has 10exp for each row of the select query, instead of it increasing each the update query runs

Code: Select all

 
     $selectplayers = $db->execute("select p.id, p.username from players as p
                                              left join medical_ward as m on ( m.player_id = p.id )
                                              where p.City_ID=? and m.player_id is null and p.id !=?
                                              ORDER BY RAND() Limit 20", array($player->City_ID, $player->id));
           
while ($selectplayers1 = $selectplayers->fetchrow())
            {
 
                if ($bombexp + $player->exp < $player->maxexp)
                {
                    $queryupdate = $db->execute("UPDATE `players` set `exp`=`exp`+? where `id`=?",array($bombexp,$player->id));
                  
 
 
                }
                else //player has gained a level! =)
                {
                   $newexp = $bombexp + $player->exp - $player->maxexp;
$query = $db->execute("UPDATE `players` set
`level`=`level`+?, `maxexp`=?, `maxhp`=?, `exp`=?, `kills`=`kills`+?, `hp`=?, `energy`=?, `maxenergy`=?, `Max_Intent`=`Max_Intent`+? where `id`=?",
array(1, ($player->level+1) * 70 - 20, $player->maxhp + 30, $newexp, 1, 0, 0, $player->maxenergy + 2, 1, $player->id));
                }
            }
 
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need help with update query in a loop.

Post by cdoyle »

OK, I think I got it.
Inside the loop, I run a query to get the players current exp and maxexp.
That seemed to fix it.

Code: Select all

 
        $selectplayers = $db->execute("select p.id, p.username from players as p
                                              left join medical_ward as m on ( m.player_id = p.id )
                                              where p.City_ID=? and m.player_id is null and p.id !=?
                                              ORDER BY RAND() Limit 20", array($player->City_ID, $player->id));
           
while ($selectplayers1 = $selectplayers->fetchrow())
            {
                $getplayerexp = $db->execute("SELECT `exp`, `maxexp` from `players` where `id`=?", array($player->id));
                $getplayerexp1 = $getplayerexp->fetchrow();
                if ($bombexp + $getplayerexp1['exp'] < $getplayerexp1['maxexp'])
                {
                    $queryupdate = $db->execute("UPDATE `players` set `exp`=`exp`+? where `id`=?",array($bombexp,$player->id));
                  
 
 
                }
                else //player has gained a level! =)
                {
                   $newexp = $bombexp + $getplayerexp1['exp'] - $getplayerexp1['maxexp'];
$query = $db->execute("UPDATE `players` set
`level`=`level`+?, `maxexp`=?, `maxhp`=?, `exp`=?, `kills`=`kills`+?, `hp`=?, `energy`=?, `maxenergy`=?, `Max_Intent`=`Max_Intent`+? where `id`=?",
array(1, ($player->level+1) * 70 - 20, $player->maxhp + 30, $newexp, 1, 0, 0, $player->maxenergy + 2, 1, $player->id));
                }
            }
 
            break;
 
Post Reply