Sorting data retrieved from db

[php_b34st]

Hi I posted a similar problem yesterday but I d[code]ont think I explained properly. What I am trying to do is get all of the usernames out of one table in a database then get statistics out of a different table for each of those usernames. I then want to sort the data so that the highest level is printed in the table first. I tried using ORDER BY in the sql statement but this did not seem to work. I think this is because it has to get the stats out one by one in the order that the [/code]usernames come out of the first table.

here is the code that I have so far:

[code]//Get data out of db
$query = "SELECT * FROM phpbb_users";
$result = mysql_query($query) or die("Could not query: " . mysql_error());

//Put required data into an array
$info = array();
$i = 0;
while ($row = mysql_fetch_assoc($result))
{
$info[$i]['username'] = $row['rsusername'];
$info[$i]['user_id'] = $row['user_id'];
$i++;
}

//Display each row
$i = 0;
foreach ($info as $r => $value)
{
$user = $info[$r]['username'];
$id = $info[$r]['user_id'];

$query = "SELECT * FROM stats WHERE userID='$user' AND cat='$skill' ORDER BY statC DESC";
$result = mysql_query($query) or die("Could not query: " . mysql_error());


$data = array();
$i = 0;
while ($row = mysql_fetch_assoc($result))
{
$data[$i]['userID'] = $row['userID'];
$data[$i]['cat'] = $row['cat'];
$data[$i]['statA'] = $row['statA'];
$data[$i]['statB'] = $row['statB'];
$data[$i]['statC'] = $row['statC'];
$i++;
}

foreach ($data as $r => $value)
{


echo '<tr>
<td class="row1"><span class="genmed">' .$rank . '</span></td>
<td class="row1"><span class="genmed"><a href="profile.php?mode=viewprofile&u=' . $id . '">' . $user . '</a></span></td>
<td class="row1"><span class="genmed">' . $data[$r]['statA'] . '</span></td>
<td class="row1"><span class="genmed">' . $data[$r]['statB'] . '</span></td>
<td class="row1"><span class="genmed">' . $data[$r]['statC'] . '</span></td>
      </tr>';
$i++;
}
$i++;
}[/code]

Is there anyway that I can sort the table by $row['statC']?

[michaellunsford]

mysql can do it for you

SELECT * FROM phpbb_users ORDER BY `statC`

[php_b34st]

Sorry I must not have explained properly again. I have tried to use ORDER BY but it did not work.

I am trying to sort the second query not the first. I think the problem is that mysql cannot order the data as it comes out because the first statement which gets each username determines the order in which the second statement gets the data out. I'm sorry I am really bad at describing things.

[michaellunsford]

well, you could try using arsort() (or asort for ascending) on your data array, but I've never tried it on a multi-dimensional array. I would do a more sloppy (but predictable) separate array for each element, then use $my_array[key($data)] to access it.

you might also try enclosing your field names in `slanted quotes` (whatever they're called)

$query = "SELECT * FROM `stats` WHERE `userID`='$user' AND `cat`='$skill' ORDER BY `statC` DESC";

[php_b34st]

I will look into the asort function that you have mentioned. Thank you. As for the sloppy but predictable array, I am trying to avoid that route as their is a lot of categories in the db and it would be a lot of unecessary code.

[michaellunsford]

again, mysql should be able to handle it.

When I run into this kind of problem, I plug my query into phpmyadmin and see what happens. If it doesn't work right, I rebuild the query using myphpadmin's interface, and it almost always reveals my problem.

[markbett]

I think perhaps what you are asking now is if you can sort the array you are keeping the data in and the answer to that is yes:

http://www.developertutorials.com/tutorials/php/sorting-array-php-051114/page1.html

[michaellunsford]

but will it work on multi-dimensional arrays?

[php_b34st]

No it does not seem to work for a multidimensional array. I have used the following code in the past for multi dimensional arrays:

[code]
function desc($a, $b)
{
if($a['statC'] == $b['statC'])
return 0;
else
return ($a['statC'] > $b['statC']) ? -1 : 1;
}
usort($data, 'desc');
[/code]

The problem with using this code this time is the 2nd for each loop is nested inside the first so it tries to declare this function for every user resulting in an error. any idea on how to modify the above code so that it doesnt have to be declared as a function?

[michaellunsford]

I'm getting kind of hazy on this thing, but I'd imagine you'd want to build all the arrays first -- end loop. then sort the arrays, then loop again through printing the arrays. This should bypass the foreach loop altogether, no?

[php_b34st]

I tried doing that (not sure if it was correct) but the result was that only the last result in the array was printed. Who would of thought sorting data could cause so much trouble? It has ahd me pulling my hair out for days now.

[michaellunsford]

okay, back to simple:

do it just like you're doing it now EXCEPT, create a simple array alongside the multidimensional one. all it does is use the same $i as the key, but the value is the field you want to sort by. do you asort (or arsort for reverse sort) to the simple array, reset it, then use it as the key to the big array.
[code=php:0]do {
  echo $data[key($simple)']['whatever'];
} while(next($simple));[/code]

[php_b34st]

Thanks for all the help so far but what you suggested outputs the data in the same order as before and asort/arsort still has no effect.

here is the code i added:

[code]$simple = array();
$simple[$i]['statC'] = $data[$i]['statC'];

arsort($simple);

do {
echo $data[key($simple)]['statC'];
}
while(next($simple));[/code]

[michaellunsford]

did you put it alongside? the other arrays?

[code=php:0] while ($row = mysql_fetch_assoc($result))
{
$data[$i]['userID'] = $row['userID'];
$data[$i]['cat'] = $row['cat'];
$data[$i]['statA'] = $row['statA'];
$data[$i]['statB'] = $row['statB'];
$simple[$i] = $row['statC'];
$data[$i]['statC'] = $row['statC'];
$i++;
}
arsort($simple);[/code]

[php_b34st]

Yes I did but it still puts them in the order that the usernames come out of the first table. asort/arsort have no effect while the 2nd for each loop is inside the first. If I use the 2nd loop on its own it does work but the problem there is that it doesnt do it for every user in the first tble onstead it does it just does it for the users in the 2nd table.

[michaellunsford]

as much as I'd hate to say it, you might have to add another dimension for the $r on your second loop.

You sure you can't get mysql to cooperate?

[php_b34st]

Yes Im sure mysql will not cooperate. I think this is because I have said get each of the stats out individually according to the user retrieved from the first for loop

[michaellunsford]

combine the queries?

SELECT `stats`.* FROM `phpbb_users`,`stats` WHERE `phpbb_users`.`userID` = `stats`.`userID` AND `stats`.`cat`='$skill' ORDER BY `statC` DESC

[php_b34st]

Thats it you have cracked it! Thank you so much for your persistance. Not only did u manage to get it to work you also managed to shorten the code :)