Page 2 of 2

Posted: Sat May 19, 2007 9:23 am
by zyklon
it is, it is ordering by whichever user was inserted into the table first. I also checked to make sure all those columns existed, and they do exist, so there is no typo.

Posted: Sat May 19, 2007 9:27 am
by aaronhall
So you've checked to see that the rank column is not being updated?

Posted: Sat May 19, 2007 9:56 am
by zyklon
if i set them all to 0 and run it, it just list them by the time they were inserted 1,2,3... as if it were on auto increment.

Posted: Sat May 19, 2007 10:04 am
by aaronhall
Sounds like the `power` column is empty or has no variation across the users?

Posted: Sat May 19, 2007 10:15 am
by zyklon
it does have variation and lot of it.

Posted: Sat May 19, 2007 10:37 am
by aaronhall
Just for my amusement, what does the following query return

Code: Select all

select max(`power`), min(`power`) from `user`;

Posted: Sat May 19, 2007 10:44 am
by aaronhall
You best contingency plan is to probably select the ordered results of the entire user table into a temporary table (maintaining the user ids), running the update query on the temp table, truncating the user table and copying from the temp to the user table.

Posted: Sat May 19, 2007 10:50 am
by zyklon
no, i check using phpmyadmin.

Posted: Sat May 19, 2007 10:56 am
by aaronhall
The query above was also to check the the references were correct, but if you're sure that all is well, you probably found a bug in your version of MySQL. Move to plan B, or upgrade your MySQL installation.

Posted: Sat May 19, 2007 12:40 pm
by volka
I've tried the script

Code: Select all

error_reporting(E_ALL);
ini_set('display_errors', true);
ini_set('mysql.trace_mode', true);

$mysql = mysql_connect('localhost', 'localuser', 'localpass') or die(mysql_error());
mysql_select_db('test', $mysql) or die(mysql_error());

mysql_query("SET @i=0", $mysql) or die(mysql_error());
mysql_query("UPDATE `user` SET `rank`=(@i:=(@i+1)) ORDER BY `power` DESC", $mysql) or die(mysql_error());
echo 'affected rows: ', mysql_affected_rows($mysql), "<br />\n";
with 4.1.22-community-nt and php 4
No errors, no warnings and the rank is updated as supposed.