Page 1 of 1
Sorting key then value
Posted: Mon Oct 26, 2009 1:42 pm
by WorldCom
Hi all,
This is not really a problem but I want to know if it can be done.
On my football pool site, I already have set up a php sort set up to order the members' from most wins to least wins.
The array is:
$player_id();
[username] => [# of wins]
So the username is the key and # of wins is a numeric value. This works fine.
Is there a way to sort first from the [# of wins] then sort the [username]
eg.
Zack - 10 wins
Amber - 10 wins
Bob - 10 wins
The way it sits now, if would group all the same wins together but the sort is from whoever entered their data first.
I'd like it to be:
Amber
Bob
Zack
This is not a big deal, just wondering if there is a way.
I've already tried ksort
This is a small sniplet of the sorting and retreiving data.
Code: Select all
arsort( $player_id );
foreach( $player_id as $key => $value ){
$result = mysql_query("SELECT id, wiguser FROM members WHERE wiguser='$key'");
$pics_name = mysql_fetch_array($result);
Re: Sorting key then value
Posted: Mon Oct 26, 2009 2:42 pm
by Mark Baker
Why do people use multiple queries against the database, when they should simply be able to use one query (which could probably handle the sorting for them rather than having to do it in PHP)
How do you get your array of player IDs?
Re: Sorting key then value
Posted: Mon Oct 26, 2009 2:48 pm
by John Cartwright
Code: Select all
... ORDER BY numberwins DESC, username ASC
Re: Sorting key then value
Posted: Mon Oct 26, 2009 4:34 pm
by WorldCom
Mark Baker wrote:Why do people use multiple queries against the database, when they should simply be able to use one query (which could probably handle the sorting for them rather than having to do it in PHP)
How do you get your array of player IDs?
Since there are 2 different pools, my first query is to obtain the user ID's of members in the pool.
I realize I could get this info from 1 query but I wrote this script 4 years ago and I was a bit newer at php.
@John Cartwright
The wins are not in the database. Only the user picks are.
Code: Select all
$result = mysql_query("SELECT * FROM user_data WHERE `$pool_select`=1 AND year=$year_data");
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
$user_result = mysql_query("SELECT wiguser FROM members WHERE id=$row[1]");
$user_d = mysql_fetch_array($user_result);
$user_sort[$row[1]] = strtolower($user_d[0]);
}
asort($user_sort); // This is the first sort to order usernames
foreach( $user_sort as $userid => $username ){
$user_picks = mysql_fetch_array(mysql_query("SELECT * FROM user_picks WHERE game_id='$game_info[0]' AND user_id =$userid"));
$win = 0;
for( $i=3; $i<=18; $i++ ){
if( $user_picks[$i] != 0 ){
if( $user_picks[$i] == 1 ){
if ( $results_info[($i - 1)] > $results_info[($i + 15)] ){ $win++; }
}
if( $user_picks[$i] == 2 ){
if( $results_info[($i + 15)] > $results_info[($i - 1)] ){ $win++; }
}
}
}
$player_id[$username] = $win; // This sorts orders the usernames with the amount of wins
The first sort was suppose to order the usernames but when it hits the second sort it's kinda useless.
The second sort is what I was asking to have ordered wins then username.
It it can be done. Everything does work but I know it's a little choppy

Re: Sorting key then value
Posted: Tue Oct 27, 2009 9:40 am
by WorldCom
Well I updated my code a bit.
I am still using multiple requests because for future weeks, members' may not have selected any picks and there is no entry in the database, yet I still want all members listed no matter which week you view.
Code: Select all
$query = "SELECT user_data.user_id, members.wiguser FROM user_data, members WHERE user_data.`$pool_select`=1 AND user_data.year=$year_data AND user_data.user_id=members.id ORDER BY members.wiguser";
$result = mysql_query($query);
while( $row = mysql_fetch_array($result, MYSQL_NUM)){
$result_1= mysql_query("SELECT * FROM user_picks WHERE game_id='$game_info[0]' AND user_id=$row[0]");
if( mysql_num_rows($result_1) > 0 ){
$user_picks = mysql_fetch_array($result_1, MYSQL_NUM);
} else {
$user_picks = array();
}
$win = 0;
for( $i=3; $i<=18; $i++ ){
if( $user_picks[$i] == 1 && $results_info[($i - 1)] > $results_info[($i + 15)] ) $win++;
if( $user_picks[$i] == 2 && $results_info[($i + 15)] > $results_info[($i - 1)] ) $win++;
}
$player_id[$row[1]] = $win;
}
The first query orders the members by username.
So the $player_id array is [username][wins] in alphabetical order.
Then I apply: arsort( $player_id ); before the display portion of the script.
I would have thought that after the sort, that is should retain the order of the members at but it doesn't seem to.
Here is the order of some of the members with 10 wins.
jdry 10
mikej 10
KingWig 10
Marty 10
KDOG 10
murdog 10
macbruiser 10
Again, this isn't a big deal but I am kinda wondering why the sort shifts the names.
Thanks again.