Solved [PHP] How to order by a storage

[Zoe Skandinavia]

Hi!, i tried to order the highscores by the reset times (storage) but i can't!. This is my code:

<?php
                                   
                                    $limitt = 5;
                                    $skills = mysql_select_multi("SELECT `name`,`level`,`experience` FROM `players` WHERE `group_id` < '2' AND `name` != 'Account Manager' ORDER BY `level` DESC, `experience` DESC LIMIT $limitt");
                                    $playerID = mysql_select_single("SELECT `id` FROM `players` WHERE `name` = '".$skills['name']."' LIMIT 5");           
                                    $number_of_rows = 0;
                       
                                    $resets = mysql_select_single("SELECT `value` FROM `player_storage` WHERE (`key` = 86228 AND `player_id` = '". $playerID['id'] ."') LIMIT $limitt");
                                   
                                    if ($resets['value'] == -1) { $reset = "Not Ready"; }
                                    elseif ($resets['value'] == 0) { $reset = "Unranked"; }
                                    elseif ($resets['value'] == 1) { $reset = "Bronze"; }
                                    elseif ($resets['value'] == 2) { $reset = "Silver"; }
                                    elseif ($resets['value'] == 3) { $reset = "Ore"; }
                                    elseif ($resets['value'] == 4) { $reset = "Platinum"; }
                                    else { $reset = "Not ready"; }
                                   
                           
                                    foreach($skills as $skillss) {
                                        $number_of_rows++;
                                        echo '+ <b></b><a href="characterprofile.php?name='.urlencode($skillss['name']).'" class="menu">'.$skillss['name'].'</a><br>';
                                        echo '<em class="style2">Level: <b>'.urlencode($skillss['level']).'</b></em></li><br />';
                                        echo '<em class="style2">Resets: <b>'.urlencode($reset).'</b></em></li><br />';
                                    }
                                    ?>

Actually it's ordered by Level, but i want that if a player is level 1200 with 2 resets compared with a player level 3000 with 1 reset, the player with level 1200 and 2 resets will be over the another player. In little words, the highscore is based on the number of resets. Please help me!. Thanks

 

[ZooTeam]

To make it easier and not having it be two arrays comparing each other, I would instead of making it a storage, make it an actual colum on the players table.
This is also cleaner and makes more sense since resets are something with more importance.

If you choose to use resets as a column in the players table I will help you further.

EDIT:
This is what I would do if I had resets as a column in my players table and not as a storage.

<?php
  
   $limitt = 5;
   $playerID = mysql_select_single("SELECT `id` FROM `players` WHERE `name` = '".$skills['name']."' LIMIT 5");  
   $number_of_rows = 0;
   $skills = mysql_select_multi("SELECT `name`, `level`, `experience`, `resets` WHERE `player_id` = '". $playerID['id'] ."' ORDER BY `resets` DESC, `level` DESC, `experience` DESC LIMIT $limitt");
  
   $reset = get_player_reset_rank($skills['resets']);


   foreach($skills as $skillss) {
     $number_of_rows++;
     echo '+ <b></b><a href="characterprofile.php?name='.urlencode($skillss['name']).'" class="menu">'.$skillss['name'].'</a><br>';
     echo '<em class="style2">Level: <b>'.urlencode($skillss['level']).'</b></em></li><br />';
     echo '<em class="style2">Resets: <b>'.urlencode($reset).'</b></em></li><br />';
   }
  
   function get_player_reset_rank($reset)
   {
     // List all possible reset ranks in ascending order: (0 to 4)
     $reset_ranks = {"Unranked", "Bronze", "Silver", "Ore", "Platinum"};
     // How many ranks are there
     $reset_ranks_count = count($reset_ranks);
    
     //  Default limit
     $limitt = 5;
     $rank = "";
    
     // Case not ready:
     if($reset == -1) {
       $rank = "Not ready";
     // Rest of cases:
     } else {
       // Decide which is the rank that this player has:
       for($i = 0; $i < $reset_ranks_count; $i++) {
         if($reset == $i) {
           $rank = $reset_ranks[$i];
           break;
         }
       }
     }
     return $rank;
   }

?>

 

[Zoe Skandinavia]

To make it easier and not having it be two arrays comparing each other, I would instead of making it a storage, make it an actual colum on the players table.
This is also cleaner and makes more sense since resets are something with more importance.

If you choose to use resets as a column in the players table I will help you further.

EDIT:
This is what I would do if I had resets as a column in my players table and not as a storage.

<?php
 
   $limitt = 5;
   $playerID = mysql_select_single("SELECT `id` FROM `players` WHERE `name` = '".$skills['name']."' LIMIT 5"); 
   $number_of_rows = 0;
   $skills = mysql_select_multi("SELECT `name`, `level`, `experience`, `resets` WHERE `player_id` = '". $playerID['id'] ."' ORDER BY `resets` DESC, `level` DESC, `experience` DESC LIMIT $limitt");
 
   $reset = get_player_reset_rank($skills['resets']);


   foreach($skills as $skillss) {
     $number_of_rows++;
     echo '+ <b></b><a href="characterprofile.php?name='.urlencode($skillss['name']).'" class="menu">'.$skillss['name'].'</a><br>';
     echo '<em class="style2">Level: <b>'.urlencode($skillss['level']).'</b></em></li><br />';
     echo '<em class="style2">Resets: <b>'.urlencode($reset).'</b></em></li><br />';
   }
 
   function get_player_reset_rank($reset)
   {
     // List all possible reset ranks in ascending order: (0 to 4)
     $reset_ranks = {"Unranked", "Bronze", "Silver", "Ore", "Platinum"};
     // How many ranks are there
     $reset_ranks_count = count($reset_ranks);
   
     //  Default limit
     $limitt = 5;
     $rank = "";
   
     // Case not ready:
     if($reset == -1) {
       $rank = "Not ready";
     // Rest of cases:
     } else {
       // Decide which is the rank that this player has:
       for($i = 0; $i < $reset_ranks_count; $i++) {
         if($reset == $i) {
           $rank = $reset_ranks[$i];
           break;
         }
       }
     }
     return $rank;
   }

?>

Well, in sometime i thought to create a table in players and it'll be easier, but i don't remember how to create it. And apart, can you tell me, how works this function?

for($i = 0; $i < $reset_ranks_count; $i++) {
         if($reset == $i) {
           $rank = $reset_ranks[$i];
           break;
         }
       }
     }
     return $rank;
   }

And what is this? "$i" i saw it a lot of times but i don't know for what is it.
Thanks for your time

 

[ZooTeam]

Well, in sometime i thought to create a table in players and it'll be easier, but i don't remember how to create it.

To create a new column in your players table you should do this:

ALTER TABLE `players` ADD `resets` INT NOT NULL DEFAULT -1;

You will have to make two functions named setPlayerReset and getPlayerReset or something.
This are not very hard to do.

If you want to keep it as a storage value instead then that's fine too, the PHP code can be done, it is just easier this way.
You could also create a trigger so that when the storage is changed then the resets field in the players db is automatically changed (not that hard aswell), and then the PHP code I posted will be fine too.

Maybe someone with some more time can help you further

And apart, can you tell me, how works this function?

for($i = 0; $i < $reset_ranks_count; $i++) {
         if($reset == $i) {
           $rank = $reset_ranks[$i];
           break;
         }
       }
     }
     return $rank;
   }

And what is this? "$i" i saw it a lot of times but i don't know for what is it.
Thanks for your time

The function evaluates and checks if reset (players current reset value) and i (a counter that goes from 0 to 4) are equal, in the case they are, then rank is assigned a name that is in the array reset_ranks, using i as an index to access it. Then the loop breaks and returns the assigned name.

(PHP 4, PHP 5)

for loops are the most complex loops in PHP. They behave like their C counterparts. The syntax of a for loop is:

for (expr1; expr2; expr3)
statement

The first expression (expr1) is evaluated (executed) once unconditionally at the beginning of the loop.

In the beginning of each iteration, expr2 is evaluated. If it evaluates to TRUE, the loop continues and the nested statement(s) are executed. If it evaluates to FALSE, the execution of the loop ends.

At the end of each iteration, expr3 is evaluated (executed).

You can take a look at some examples in http://php.net/manual/en/control-structures.for.php

i is just a variable that serves as a counter, there's nothing special about it.

 

[Zoe Skandinavia]

To create a new column in your players table you should do this:

ALTER TABLE `players` ADD `resets` INT NOT NULL DEFAULT -1;

You will have to make two functions named setPlayerReset and getPlayerReset or something.
This are not very hard to do.

If you want to keep it as a storage value instead then that's fine too, the PHP code can be done, it is just easier this way.
You could also create a trigger so that when the storage is changed then the resets field in the players db is automatically changed (not that hard aswell), and then the PHP code I posted will be fine too.

Maybe someone with some more time can help you further



The function evaluates and checks if reset (players current reset value) and i (a counter that goes from 0 to 4) are equal, in the case they are, then rank is assigned a name that is in the array reset_ranks, using i as an index to access it. Then the loop breaks and returns the assigned name.


You can take a look at some examples in http://php.net/manual/en/control-structures.for.php

i is just a variable that serves as a counter, there's nothing special about it.

Well, i'll try to do it. Thanks for everything, you are awesome^^

 

[ZooTeam]

No prob, do post here if you have any more doubts, I don't really have the time to make the code and test it out but I can help you with any doubts you have! Good luck.

 

[Zoe Skandinavia]

No prob, do post here if you have any more doubts, I don't really have the time to make the code and test it out but I can help you with any doubts you have! Good luck.

Hi again, i tested the code and when i reload the page, doesn't appears nothing, is like if something is wrong, but i don't know what:/ help me

 

[ZooTeam]

reset_ranks should be

$reset_ranks = array("Unranked", "Bronze", "Silver", "Ore", "Platinum");

Also:

$skills = mysql_select_multi("SELECT `name`, `level`, `experience`, `resets` WHERE `player_id` = '". $playerID['id'] ."' ORDER BY `resets` DESC, `level` DESC, `experience` DESC LIMIT $limitt");

should be:

$skills = mysql_select_multi("SELECT `name`, `level`, `experience`, `resets` WHERE `group_id` < 2 AND `name` != 'Account Manager' ORDER BY `resets` DESC, `level` DESC, `experience` DESC LIMIT $limitt");

 

[Zoe Skandinavia]

reset_ranks should be

$reset_ranks = array("Unranked", "Bronze", "Silver", "Ore", "Platinum");

Also:

$skills = mysql_select_multi("SELECT `name`, `level`, `experience`, `resets` WHERE `player_id` = '". $playerID['id'] ."' ORDER BY `resets` DESC, `level` DESC, `experience` DESC LIMIT $limitt");

should be:

$skills = mysql_select_multi("SELECT `name`, `level`, `experience`, `resets` WHERE `group_id` < 2 AND `name` != 'Account Manager' ORDER BY `resets` DESC, `level` DESC, `experience` DESC LIMIT $limitt");

I changed all that you said and now says this:

string(163) "SELECT `name`, `level`, `experience`, `resets` WHERE `group_id` < 2 AND `name` != 'Account Manager' ORDER BY `resets` DESC, `level` DESC, `experience` DESC LIMIT 5"
(query - SQL error)
Type: select_multi (select multiple rows from database)

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `group_id` < 2 AND `name` != 'Account Manager' ORDER BY `resets` DESC, `le' at line 1

 

[ZooTeam]

Yup, forgot:

$skills = mysql_select_multi("SELECT `name`, `level`, `experience`, `resets` FROM `players` WHERE `group_id` < 2 AND `name` != 'Account Manager' ORDER BY `resets` DESC, `level` DESC, `experience` DESC LIMIT $limitt");

 

[Zoe Skandinavia]

Yup, forgot:

$skills = mysql_select_multi("SELECT `name`, `level`, `experience`, `resets` FROM `players` WHERE `group_id` < 2 AND `name` != 'Account Manager' ORDER BY `resets` DESC, `level` DESC, `experience` DESC LIMIT $limitt");

The page works fine now, but when i put the resets in 2 to a player, dont change the rank from unranked to Silver :/

 

[ZooTeam]

Try putting $resets = ... in the foreach loop.

 

[Zoe Skandinavia]

Try putting $resets = ... in the foreach loop.

Where? in foreach($skills as $skillss) ?

 

[EvulMastah]

Fixing your original code into single query:

SELECT `id`, `name`, `level`, `experience`, `key`, `value` FROM `players` LEFT JOIN `player_storage` ON `players`.`id` = `player_storage`.`player_id` WHERE `group_id` < 2 AND `name` != 'Account Manager' AND `key` = 86228 ORDER BY CAST(`value` AS INT) DESC, `experience` DESC LIMIT $limitt

If you are using TFS 1.x, you may replace the CAST(`value` AS INT) with just `value`