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
zyklon
Forum Commoner
Posts: 49 Joined: Mon Jul 31, 2006 7:14 pm
Location: MA, USA
Post
by zyklon » Sat May 19, 2007 9:23 am
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.
aaronhall
DevNet Resident
Posts: 1040 Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:
Post
by aaronhall » Sat May 19, 2007 9:27 am
So you've checked to see that the rank column is not being updated?
zyklon
Forum Commoner
Posts: 49 Joined: Mon Jul 31, 2006 7:14 pm
Location: MA, USA
Post
by zyklon » Sat May 19, 2007 9:56 am
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.
aaronhall
DevNet Resident
Posts: 1040 Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:
Post
by aaronhall » Sat May 19, 2007 10:04 am
Sounds like the `power` column is empty or has no variation across the users?
zyklon
Forum Commoner
Posts: 49 Joined: Mon Jul 31, 2006 7:14 pm
Location: MA, USA
Post
by zyklon » Sat May 19, 2007 10:15 am
it does have variation and lot of it.
aaronhall
DevNet Resident
Posts: 1040 Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:
Post
by aaronhall » Sat May 19, 2007 10:37 am
Just for my amusement, what does the following query return
Code: Select all
select max(`power`), min(`power`) from `user`;
aaronhall
DevNet Resident
Posts: 1040 Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:
Post
by aaronhall » Sat May 19, 2007 10:44 am
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.
zyklon
Forum Commoner
Posts: 49 Joined: Mon Jul 31, 2006 7:14 pm
Location: MA, USA
Post
by zyklon » Sat May 19, 2007 10:50 am
no, i check using phpmyadmin.
aaronhall
DevNet Resident
Posts: 1040 Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:
Post
by aaronhall » Sat May 19, 2007 10:56 am
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.
volka
DevNet Evangelist
Posts: 8391 Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger
Post
by volka » Sat May 19, 2007 12:40 pm
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.