Page 1 of 1

[SOLVED] Order an array

Posted: Thu Jul 08, 2004 7:25 pm
by josh
I am stuck, I have looked at the php.net manuals and cannot find anything to do with this:

My database is like so:

id | User | Hours
_____________
| 1 | Bob | 5
| 2 | Bob | 9
| 3 | Sam | 2
| 4 | Sam | 5




something like that, only alot more fields, and alot more users, but the basic idea is each row is the results of a form a user fills out, now my client decides he wants to be able to click any user and see the total
hours for that user, so I wrote the following code:

Code: Select all

<?php

//Total hours
mysql_free_result($result);
// pid is username
$pid=$_REQUEST['pid'];
//pilotidnum is username
$sql = "SELECT totalhours FROM pirep where `pilotidnum` = '$pid'"; 
$result = mysql_query($sql) or die("Query failed : " . mysql_error());
$totalhours=0;
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) { 
   foreach ($line as $col_value) { 
       $totalhours=$totalhours + $col_value; 
   }
} 


?>
Now he decides not only be able to click a user to see the total hours, but have a list, for example top X users by hours, so he could define X as 5 it would show the top 5 users ordered most hours to less hours, this would have to be printed out in a table.

As for getting a list of all users I have a table in my database named "pilots" that holds users emails, etc...

so for a list of users i could simply do:
$sql = "SELECT * FROM pilots WHERE `hub` = '$hub' order by pid"

I would then have to calculate the hours for each user that I received from the pilots table (i could use the script i posted above, but im not sure EXACTLY how to approach this, arrays?
could anyone guide me how to do this or post some source code.. thanks

Posted: Thu Jul 08, 2004 7:36 pm
by markl999
My brains too tired to go into the full problem, but you can use MySQL's SUM() function to get the sum of the total hours . Using that you should be able to do a join on the pilto id and get all the info you require in one shot. Hopefully someone else will provide something more concrete :o

Posted: Thu Jul 08, 2004 7:40 pm
by josh
ok i will research the sum feature... how do you do a join?

Posted: Thu Jul 08, 2004 7:48 pm
by feyd

Posted: Thu Jul 08, 2004 8:09 pm
by josh
ok, pretty interesting features i didnt know about... how would i use these to get my result in one queery like markl999 said?

Posted: Fri Jul 09, 2004 9:35 pm
by josh
Anyone?

Posted: Fri Jul 09, 2004 10:19 pm
by feyd
have you tried playing around with it in phpMyAdmin or something?

Posted: Sat Jul 10, 2004 3:02 pm
by josh
I would i just don't know where to start...
Instead of going through a loop to retreive the sum I use the sum() feature so that narrows down my code, I now would need to retreive an array of the users, then for each user in the array retreive the sums and order them from greatest to least

Posted: Sat Jul 10, 2004 3:15 pm
by Weirdan

Code: Select all

select id, sum(hours) as total from something where something_else='asd' group by id order by total desc limit 5

Posted: Sat Jul 10, 2004 5:15 pm
by josh
Thanks that works exactly how i needed it to.. i had no idea what to put for where something_else='asd' so i jsut took out the entire where clause and it worked perfect