[this is alittle complex]

Thank you everyone I have got everythin to work exactly how I hoped it would, and now I have set the script up as a cron job and its ran once every 24 hours :)

Big thank you to everyone who helped.

[this is alittle complex]

I got it to work in phpmyadmin with the following code! :D

[code]
CREATE TEMPORARY TABLE new_ladder
(
new_rank integer auto_increment primary key,
id integer
)
SELECT id
FROM vod_ladder
ORDER BY rating
DESC;

UPDATE vod_ladder AS vdl, new_ladder AS nvdl
SET vdl.rank = nvdl.new_rank
WHERE (vdl.id=nvdl.id)
[/code]

ive tried executing the code like this:

[code]
    $exe_sql = "UPDATE ".$pre."ladder
                SET rating=c_rating, wins=c_wins, losses=c_losses
                WHERE (rating != c_rating)
                OR (wins != c_wins)
                OR (losses != c_losses)";
    $sql->sql_query();
    
    $exe_sql = "CREATE TEMPORARY TABLE new_ladder
                (
                    new_rank INTEGER AUTO_INCREMENT PRIMARY KEY,
                    id INTEGER
                )
                SELECT id
                FROM vod_ladder
                ORDER BY rating
                DESC";
    $sql->sql_query();
    $exe_sql = "UPDATE vod_ladder AS vdl, new_ladder AS nvdl
                SET vdl.rank = nvdl.new_rank
                WHERE (vdl.id = nvdl.id)";
    $sql->sql_query();
[/code]

But when i run it I get the error:

There appears to be an error. Below the information is provided.
Code: 1044
Message: Access denied for user 't3_user'@'localhost' to database 't3_DB'

Any ideas? 0_o

[this is alittle complex]

would a temporary table last long enough for me to perform a query that dumps/updates the data back into the original table? A friend is helping me figure out the temporary table thing but the only way he knows how to update the original table based on the temp table is to perform a query within a while loop..And I do nt want to travel down this path :(

[this is alittle complex]

[!--quoteo(post=347468:date=Feb 19 2006, 01:43 PM:name=shoz)--][div class=\'quotetop\']QUOTE(shoz @ Feb 19 2006, 01:43 PM) [snapback]347468[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I WOULD NOT suggest doing it this way, but using MYSQL 4.1 I was able to get the desired result using one query with the following.
To create table `users`
[code]
CREATE TABLE `users` (
  `name_id` int(11) NOT NULL auto_increment,
  `name` varchar(60) default NULL,
   `rank` int(11) default NULL,
  PRIMARY KEY  (`name_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `users` VALUES (1,'john',0),(2,'steve',0),(3,'smith',0),(4,'fred',0);
[/code]

The query we want to rank by
[code]
SELECT * FROM users ORDER BY name DESC
[/code]

query to update the rank
[code]
UPDATE
users AS n1
INNER JOIN
    (
    SELECT
    @cc:=1, sn2.name_id, sn2.name,
        (
        SELECT
        (SUM(IF(sn1.name_id=sn2.name_id, @cc:=0, @cc))+1)
        FROM
            (
            SELECT
            *
            FROM
            users
            ORDER BY
            name DESC
            ) AS sn1
        ) AS rank
    FROM
    users AS sn2
    ORDER BY
    name
    DESC
    ) AS n2
ON n1.name_id=n2.name_id
SET
n1.rank=n2.rank;
[/code]
Using the temporary table option suggested by fenway you can create the temporary table with an auto_increment column and create the update query based on that.
[code]
CREATE TEMPORARY TABLE users_tmp (rank INT AUTO_INCREMENT, name_id INT, PRIMARY KEY(rank));

INSERT INTO users_tmp SELECT '', id FROM users ORDER BY name DESC;

UPDATE users n1 INNER JOIN users_tmp n2 ON n1.id=n2.name_id SET n1.rank=n2.rank;
[/code]
You should [a href=\"http://dev.mysql.com/doc/refman/4.1/en/lock-tables.html\" target=\"_blank\"]lock[/a] the table before running the queries or find some other way to ensure that the table doesn't change during the queries. If you'd like to be able to continue "Selecting" from the table during the update, consider using [a href=\"http://www.innodb.com/index.php\" target=\"_blank\"]InnoDB[/a] tables.

Using php you can create the update using something similar to the following
[code]
$query = 'SELECT * FROM users ORDER BY name DESC ';
$update_query = 'UPDATE users SET rank = CASE name_id ';
for ($i = 1;$row = mysql_etc($result); $i++)
{
   $update_query .= "WHEN {$row['name_id']} THEN  $i ";
}
$update_query .= 'END ';
mysql_query($update_query);
[/code]
[a href=\"http://dev.mysql.com/doc/refman/4.1/en/control-flow-functions.html\" target=\"_blank\"]Control Flow Functions[/a]
Again, lock the table when doing the queries.
[/quote]


why would you NOT want to do this?

[this is alittle complex]

I wish I had mysql 5 but i dont..

And, You are aware that im actually going to be putting a query WITHIN the while loop, right?

Ive done it before, and it slows down the server terribly :(((( I am just trying to avoid severe server stress that ive been part of before ;-(

[!--quoteo(post=347287:date=Feb 19 2006, 01:03 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 19 2006, 01:03 AM) [snapback]347287[/snapback][/div][div class=\'quotemain\'][!--quotec--]
First, there doesn't have to be a way to do it in a single query; a temporary table is always an option. Second, you can determine what you call "rank" simply by using a server variable counter, which in principle, you should be able to use to update any column. So I still say it's possible.
[/quote]

How would I go about getting this mysql server variable counter?

[this is alittle complex]

Ok, I have done alittle research, and let me rephrase what it is I am trying to do...

[code]
UPDATE table
SET rank=AUTO_INCREMENT_STARTING_WITH_1_HERE
ORDER BY rating
[/code]

I am wanting a table to be ordered a particular way a then a field given a number based on the order it was recieved starting with 1. So if it was ordered first, it would get rank=1, if it was ordered second, it would get rank=2, and so on and so fourth.

A few people have told me its not possible, but there has got to be a way to do it :(

[this is alittle complex]

Is it possible to select X amount of rows from a mysql database, organize them via a particular field from largest to smallest, and then UPDATE that field based on the order the rows were pulled in ONE query?

the reason for wanting to put all of this into one query because the only other way i know how to achieve what i want to achieve is to update the rows in one query and then select the rows in a seperate query sorting them from smallest to largest and run a while loop with an update query that updates the rows one by one, the only problem with this is I plan on doing THOUSANDS of rows in a single update every 24 hours, and im pretty sure a few hundred thousand queries would probably crash the server, or slow it extremly at the very least.

So, any suggests or help here would be greatly appreciated