mysql part not working

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

User avatar
zyklon
Forum Commoner
Posts: 49
Joined: Mon Jul 31, 2006 7:14 pm
Location: MA, USA

Post 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.
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post by aaronhall »

So you've checked to see that the rank column is not being updated?
User avatar
zyklon
Forum Commoner
Posts: 49
Joined: Mon Jul 31, 2006 7:14 pm
Location: MA, USA

Post 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.
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post by aaronhall »

Sounds like the `power` column is empty or has no variation across the users?
User avatar
zyklon
Forum Commoner
Posts: 49
Joined: Mon Jul 31, 2006 7:14 pm
Location: MA, USA

Post by zyklon »

it does have variation and lot of it.
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post by aaronhall »

Just for my amusement, what does the following query return

Code: Select all

select max(`power`), min(`power`) from `user`;
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post 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.
User avatar
zyklon
Forum Commoner
Posts: 49
Joined: Mon Jul 31, 2006 7:14 pm
Location: MA, USA

Post by zyklon »

no, i check using phpmyadmin.
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post 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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.
Post Reply