Jump to content

Archived

This topic is now archived and is closed to further replies.

php_b34st

Sorting data retrieved from db

Recommended Posts

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']?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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";

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
combine the queries?

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

Share this post


Link to post
Share on other sites
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 :)

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.