Page 1 of 1
sort clan ranks and UPDATE the fields
Posted: Mon Nov 28, 2005 6:14 pm
by davidprogramer
Hi. I recently encountered a problem updating clan ranks.
I have 15 clans. Each ranked 1 to 15. They each have a clan_rank field. Rank is based on who has the most points and who has the most wins. How would I loop through each clan, getting points and wins, and run an UPDATE query that updates each of the clans ranks???
If you need code or any further information please let me know!

Posted: Mon Nov 28, 2005 6:23 pm
by John Cartwright
Code: Select all
$sql = '
SELECT *
FROM `clans`
ORDER BY `points`, `wins`DESC';
$result = mysql_query($result) or die('SQL ['.$sql.'] caused: '. mysql_error())
//assuming you have a unique id for each clan
while ($row = mysql_fetch_assoc($result)) {
$clans[] = $row['clanId'];
}
for ($x = 0; $x <= 15; $x ++) {
$sql = '
UPDATE `clans`
SET `clank_rank` = \''.($x+1).'\'
WHERE `clanId` = \''.$clans[$x].'\'
LIMIT 1';
$result = mysql_query($result) or die('SQL ['.$sql.'] caused: '. mysql_error())
}
Should get the job done

Although I really don't see a point on having a column for the clan rank.. it makes a lot more sense to compute the order through the select query much like I have done in the above $sql.
Posted: Mon Nov 28, 2005 6:33 pm
by davidprogramer
I think we are on the same page here. That looks alot like a bubble sort. Any way to wrap that up in a function? And can you explain the code a little bit. Sorry for asking for so much, but I will have a hard time getting it done if I don't know what im doing
edit: Also! There is a unique id for each clan and you were right. It is clan_id.
edit2:
Although I really don't see a point on having a column for the clan rank.. it makes a lot more sense to compute the order through the select query much like I have done in the above $sql.
Because I have other pages that look for the clans rank.
Here check out the page. You can probably follow along with what im doing fairly easy.
http://www.aowars.com
also, I uploaded your script to a test.php file. Have a look~
http://www.aowars.com/modules.php?name= ... test&lid=1
Here is the code. I don't know why it results in an error:
Code: Select all
<?
/* require_once("modules/League/includes/league_functions.php");
$current_ladder = $_GET[lid];
$ladder = sql_query("SELECT ladder_id, ladder_name, ladder_directory, sql_db, sql_username, sql_pw, sql_prefix FROM ".$prefix."_league_ladders WHERE ladder_id = $current_ladder");
list($ladder_id, $ladder_name, $ladder_directory, $sql_db, $sql_username, $sql_pw, $sql_prefix) = sql_fetch_row($ladder);
connection($sql_db, $sql_username, $sql_pw);*/
$sql = '
SELECT *
FROM `halo_clans`
ORDER BY `clan_points`, `clan_wins`DESC';
$result = mysql_query($result) or die('SQL ['.$sql.'] caused: '. mysql_error());
//assuming you have a unique id for each clan
while ($row = mysql_fetch_assoc($result)) {
$clans[] = $row['clan_id'];
}
for ($x = 0; $x <= 15; $x ++) {
$sql = '
UPDATE `halo_clans`
SET `clank_rank` = \''.($x+1).'\'
WHERE `clan_id` = \''.$clans[$x].'\'
LIMIT 1';
$result = mysql_query($result) or die('SQL ['.$sql.'] caused: '. mysql_error());
}
?>
Posted: Mon Nov 28, 2005 8:03 pm
by John Cartwright
Had an error in the code, fixed it.
Code: Select all
function updateRanks() {
//setup query
//generally like to have the query not directly set in mysql_query()
//so you can echo it out for debugging (notice the or die() statement)
$sql = '
SELECT *
FROM `halo_clans`
ORDER BY `clan_points`, `clan_wins`DESC';
//store the query into a result
//perform the query
$result = mysql_query($sql) or die('SQL ['.$sql.'] caused: '. mysql_error());
//initialize variable as an array
$clans = array();
//assuming you have a unique id for each clan
//loop results
while ($row = mysql_fetch_assoc($result)) {
//store clan ids inside an array
$clans[] = $row['clan_id'];
}
//changed 15 to 14 (forgot 0 counts as 1)
//start a loop to loop 15 times
for ($x = 0; $x <= 14; $x ++) {
//setup the update query
$sql = '
UPDATE `halo_clans`
SET `clank_rank` = \''.($x+1).'\'
WHERE `clan_id` = \''.$clans[$x].'\'
LIMIT 1';
//perform the update query
//no need to store a result since you are not fetching rows
mysql_query($sql) or die('SQL ['.$sql.'] caused: '. mysql_error());
}
}
//call the function
updateRanks();
?>
If you simply do not understand what a function does simply go to
http://php.net/foobar but change the name of the function with foobar
Posted: Mon Nov 28, 2005 8:19 pm
by davidprogramer
Sweet. Thanks for the awesome help man. I am nearly done here!
Just eh, one more thing bud:
This code gets the correct database:
Code: Select all
$current_ladder = $_GET[lid];
$ladder = sql_query("SELECT ladder_id, ladder_name, ladder_directory, sql_db, sql_username, sql_pw, sql_prefix FROM ".$prefix."_league_ladders WHERE ladder_id = $current_ladder");
list($ladder_id, $ladder_name, $ladder_directory, $sql_db, $sql_username, $sql_pw, $sql_prefix) = sql_fetch_row($ladder);
connection($sql_db, $sql_username, $sql_pw);
function connection($sql_db, $sql_username, $sql_pw){
$hostname = "localhost";
$database = "$sql_db";
$username = "$sql_username";
$password = "$sql_pw";
$dbh = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");
mysql_select_db("$database",$dbh)
or die("Error: Could not connect to database: $database ");
echo "CONNECTED TO $database!<br>";
}
Code: Select all
//setup query
//generally like to have the query not directly set in mysql_query()
//so you can echo it out for debugging (notice the or die() statement)
//////////////// ADDED mysql_query to get to the correct database! ///// THROWS ERROR
$sql = mysql_query('SELECT * FROM `halo_clans` ORDER BY `clan_points`, `clan_wins`DESC');
//store the query into a result
//perform the query
$result = mysql_query($sql) or die('SQL ['.$sql.'] caused: '. mysql_error());
//initialize variable as an array
$clans = array();
//assuming you have a unique id for each clan
//loop results
while ($row = mysql_fetch_assoc($result)) {
//store clan ids inside an array
$clans[] = $row['clan_id'];
}
//changed 15 to 14 (forgot 0 counts as 1)
//start a loop to loop 15 times
for ($x = 0; $x <= 14; $x ++) {
//setup the update query
//////////////// ADDED mysql_query to get to the correct database! ///// THROWS ERROR
$sql = mysql_query('
UPDATE `halo_clans`
SET `clank_rank` = \''.($x+1).'\'
WHERE `clan_id` = \''.$clans[$x].'\'
LIMIT 1');
//perform the update query
//no need to store a result since you are not fetching rows
mysql_query($sql) or die('SQL ['.$sql.'] caused: '. mysql_error());
}
Once again. I TRULY appreciate the awesome help!
Posted: Mon Nov 28, 2005 8:23 pm
by John Cartwright
You almost got it.. except for some reason you are executing the query twice, and the second time your trying to execute the result and not the sql
try this
Code: Select all
//setup query
//generally like to have the query not directly set in mysql_query()
//so you can echo it out for debugging (notice the or die() statement)
//////////////// ADDED mysql_query to get to the correct database! ///// THROWS ERROR
$sql = 'SELECT * FROM `halo_clans` ORDER BY `clan_points`DESC';
//store the query into a result
//perform the query
$result = mysql_query($sql) or die('SQL ['.$sql.'] caused: '. mysql_error());
//initialize variable as an array
$clans = array();
//assuming you have a unique id for each clan
//loop results
while ($row = mysql_fetch_assoc($result)) {
//store clan ids inside an array
$clans[] = $row['clan_id'];
}
//changed 15 to 14 (forgot 0 counts as 1)
//start a loop to loop 15 times
for ($x = 0; $x <= 14; $x ++) {
//setup the update query
//////////////// ADDED mysql_query to get to the correct database! ///// THROWS ERROR
$sql = 'UPDATE `halo_clans` SET `clank_rank` = \''.($x+1).'\' WHERE `clan_id` = \''.$clans[$x].'\' LIMIT 1';
//perform the update query
//no need to store a result since you are not fetching rows
mysql_query($sql) or die('SQL ['.$sql.'] caused: '. mysql_error());
}
If that still throws an error post the error please
Posted: Mon Nov 28, 2005 8:26 pm
by davidprogramer
nope, that worked! You just ended about 5 hours of blood, sweat, and tears! That seriously makes me so happy i could cry lol.