[SOLVED] Order an array

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

[SOLVED] Order an array

Post 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
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post 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
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

ok i will research the sum feature... how do you do a join?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

Anyone?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

have you tried playing around with it in phpMyAdmin or something?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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
Post Reply