WHERE statement not working as expected

davidjames52

Hi all,

I have a while statement, what I need to do is update each record that the query selects by updating rank in each one by adding 1 to it. But at the moment all it does is update all of them to the same value which is the last value in the loop. Can some one please steer me in the right direction to what I am doing wrong. Here is the code in the while statement that I am using. Thanks in advance.

$updateallotherranks = mysql_query("SELECT rank FROM users WHERE rank >= '$newrank' AND rank <'$rankupper'");// this is selecting the right records to update so I am right to this point//
while($updatealllink1 = mysql_fetch_array($updateallotherranks)){// sets my loop up in selecting records//
$otherplayeroldrank = $updatealllink1['rank'];// to delect the rank current rank vlaue in the loop//
$newotherplayerrank = $otherplayeroldrank+1;// adds 1 to the rank valvue selected//
$updateotherplayerranks = mysql_query("UPDATE users SET rank = '$newotherplayerrank' WHERE rank = '$otherplayeroldrank'");// update the current record in the loop to the new value+1 where the old rank value exists//
}

Roger_Ramjet

Forget all that - AND LEARN TO USE THE BOASRDS PHP TAGS so we can read your code.

$sql = "UPDATE users SET rank=rank+1 WHERE rank >= '$newrank' AND rank <'$rankupper'"

Installer

It looks to me like you're upping the rank on all of them because your second query has no way to differentiate among those with the same rank. One (or more) is updated, then in a subsequent iteration the second query finds it again, and it's updated again. Or something like that; I'm not sure. Anyway, it might help to select a unique field (such as "player_id") in the first query and use that in the update query.

$updateallotherranks = mysql_query("SELECT player_id, rank FROM users 
                                    WHERE rank >= '$newrank' AND rank < '$rankupper'");
while ($updatealllink1 = mysql_fetch_array($updateallotherranks)) {
    $otherplayeroldrank = $updatealllink1['rank'];
    $newotherplayerrank = $otherplayeroldrank + 1;
    $updateotherplayerranks = mysql_query("UPDATE users SET rank = '$newotherplayerrank' 
                                           WHERE rank = '$otherplayeroldrank' 
                                           AND player_id = $updatealllink1['player_id']");
}

I hope this helps, but I'm not real sure I read your code or your problem right. For instance, when you say it updates "all of them to the same value", do you mean all the records in the table, or all that are selected by the first query?

It would help others if, for future posts, you learn how to post highlighted code. The lack of highlighting and formatting, and your choices of variable names make it difficult.

Roger_Ramjet

Ok Installer, except that my query does them all in one go. No need for a loop and multiple queries: just add 1 to the existing rank for all rows that meet the condition.

Installer

Yes, yours is much better. I posted without checking to see if someone else had put up a post while I was preparing mine, as I usually do. Then when I did see yours, I didn't read it closely enough--I thought you were demonstrating the use of php tags. My error all the way around.

davidjames52

Thank you both. I didnt even think of the simplest thing.

bradgrafelman

Don't forget to mark this thread resolved.