[Q]Dont know how to call it

[Minase]

first sorry for title dont know how to call this thread.

i have 2 tables users and alliances

USERS (CID,CAPTURE) - CID=Clan ID,CAPTURE=some points

ALLIANCES (ID,PID) PID = Player ID

 

i want to make something like a top of alliances ordered by capture.but i dont know how to structure it dang it 

 

first i used a query to make a sum of alliances points

 

$capture = mysql_query("SELECT SUM(Capture) AS CaptureSum FROM `" . DBPREFIX . "users` where CID=1");

but now how i can order the alliances by variable $capture?

$rank = mysql_query("SELECT * FROM `" . DBPREFIX . "alliances` ORDER BY ????? DESC");

 

can i structure it into a query? if yes how please?

and this is more like a php subject cause is not just a query.at what im thinking now i need to use 2 variables (1 to select sum from users,and another to order).but i dont have a clue how to do it

 

simple explain:

I want to make a simple alliance ranking who would order results by $capture

[DarkWater]

Show me 3 sample rows from each table and I'll write up a query.

[Minase]

 

-- USERS --

ID - CID - Capture

1  - 1  - 100

2  - 2  - 200

3  - 1  - 50

 

-- ALLIANCES --

CID - PID

1    - 1

2    - 2

1    - 3

 

first query select * from alliances

second one order them by capture

thanks

[DarkWater]

Okay, I just made tables with that data and started with a query, but want to know what your final results will look like.  Like uhh, which ids do we associate with.  Show me what you want as returned data. >_<

[Minase]

//edited

 

i just need to display $top (no database record)

thanks

[DarkWater]

Wasn't quite sure of how you wanted the data, but this seems to work as far as I can tell:

 

SELECT SUM(u.Capture) as capturetotal, u.id as pid FROM users AS u LEFT JOIN alliances AS al ON al.pid = u.id  GROUP BY al.pid ORDER BY capturetotal DESC;

[Minase]

thanks

[DarkWater]

It worked?  I mean, it worked on my sample data, but I just want to make sure.

[Minase]

thanks it worked but not how it was suposed i tryed editing it,but no luck...

http://img134.imageshack.us/img134/262/unbenanntau4.png

 

hope you understand better what i want to do in that picture if not let me explain again

 

first parse all aliances and sellect capture sum (from users) for every alliance.after that order them by capture.

hope you understand what i mean thanks

[DarkWater]

Okay, just gimme a few minutes.  I'm playing Mario Kart with my brother and sister, but I didn't forget about you. >_<

[Minase]

ok thanks

[DarkWater]

Okay, since the data is different from what you showed me, can you please give me a direct dump of all the tables you want and give me only 3 rows from each table in the dump?