timtimtimma

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.

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

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 :(

[!--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?

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?

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 :(

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