SQL logic and PHP problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Telos
Forum Commoner
Posts: 37
Joined: Sat Aug 02, 2003 9:03 am
Location: Finland

SQL logic and PHP problem

Post by Telos »

Hopefully this is the correct forum but I have a problem which is like this:

I have a clan website where I store matches in their own table and member data in own table. There are other tables but these are the only ones related to my problem. I store an MVP player from each of the matches in it's own field. The mvp_player field contains the id number of the member. Now I have a problem that how do I get to display like "top5 most valuable players"
I need the member id and how many mvp prices he/she has ordered by the count of the prices desc. Any ideas how to achieve this? Thankx in advance.
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Post by patrikG »

moved to Databases
Illusionist
Forum Regular
Posts: 903
Joined: Mon Jan 12, 2004 9:32 pm

Post by Illusionist »

i have no idea what your talking about. So I'll just show oyu how to limit 5 in a desc order:

"SELECT ???? FROM ???? ORDER BY id DESC LIMIT 5"
Telos
Forum Commoner
Posts: 37
Joined: Sat Aug 02, 2003 9:03 am
Location: Finland

Post by Telos »

Ok, let me try to explain this more better.

I have two tables that the problem is about: members and matches
in matches table I have a field like this: mvp_player which has a member id number who was the most valuable player in that match.
Now I need to print out top5 list of most valuable players, meaning that I need to count how many mvp prices every member has and then order that list to descending order and then limit it to 5. when I know the id number of a member I can get his nickname from the members table easily but how do I get the list of member ids and mvp prices out of that database? Hopefully this englightened you a bit.
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post by Wayne »

Code: Select all

select count(mvp_player) AS player_value, mvp_player FROM matches GROUP BY mvp_player ORDER BY player_value DESC LIMIT 5
should do it .... then you just need to tweak the SQL to join in the members details that you require on the mvp_player id
Telos
Forum Commoner
Posts: 37
Joined: Sat Aug 02, 2003 9:03 am
Location: Finland

Post by Telos »

thanx. Seems like I have to dig up the notes I did at school about SQL :) Thant helped me but I just lacked the skill to implement the player nickname search into that same sql sentence so I just did another sql query which gets me the nickname.
Telos
Forum Commoner
Posts: 37
Joined: Sat Aug 02, 2003 9:03 am
Location: Finland

Post by Telos »

sorry for doublepost but another problem with another project. I have matches table again but there are fields named threestars, twostars, onestars which have the three best players in that match. How do I get a descending top list of star players? I should get all the player names like distinct and then somehow count how many stars he/she has and finally order the list correctly. The fields include the name of the player. Maybe it can be created with just one single sql query but like you probably know I don't have the skills to create that sentence.
Post Reply