1 Topic by ango (edited by ango 2006-04-21 12:20)

  • ango
  • Senior Member
  • Offline
  • From: Germany
  • Registered: 2006-03-07
  • Posts: 103

Topic: Arcade Game Mod - SQL Query Help

I need a mysql query to count top scores of every user from all games, descending sorted.

I´ve the following tables:

arcade_games
- game_id
- game_name
- game_filename
- game_desc
- game_width
- game_height
- game_cat

and

arcade_ranking
- rank_id
- rank_game
- rank_player
- rank_score
- rank_date

The output should look like this: First Place USER1 with 30 highscores, Second place USER2 with 20 Highscores and Third place USER3 with 10 highscores

I´m not a programmer please help ...

My PunBB Arcade Mod & Game Packs 1-9 (update 01-06-2007):
Test and download here !

2 Reply by ango

  • ango
  • Senior Member
  • Offline
  • From: Germany
  • Registered: 2006-03-07
  • Posts: 103

Re: Arcade Game Mod - SQL Query Help

Whats wrong with this query?

SELECT username, MAX(rank_score) , COUNT(*) as total
FROM arcade_ranking, arcade_games, users
WHERE rank_game = game_filename AND rank_player = id
GROUP BY username ORDER BY total DESC LIMIT 3

This query collects all scores and not only the highscores. It makes no difference of using MAX(rank_score) or only rank_score, the result is always the same ...

My PunBB Arcade Mod & Game Packs 1-9 (update 01-06-2007):
Test and download here !
  • Registered: 2006-03-15
  • Posts: 100

Re: Arcade Game Mod - SQL Query Help

SELECT username, MAX(rank_score) , COUNT(*) as total
FROM arcade_ranking, arcade_games, users
WHERE rank_game = game_filename AND rank_player = id
GROUP BY username ORDER BY total DESC LIMIT 3


it doent look like like ther eis any thing wrong with it looks good did you try it yet what erros doi you get...

http://www.dharmil.info/ - My site

http://www.yourarcadesite.1.vg/ - My Arcade, Play 250 games online for free and Save your high score!

http://www.forums.dharmil.info/ - My forums i created using php/mysql still working on them about 15% or more done

4 Reply by Smartys (edited by Smartys 2006-04-23 18:08)

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Arcade Game Mod - SQL Query Help

ango: Because max returns the max from the returned results.
I'm not even sure what you're asking for though
Do you just want to grab the number of entries in the highscore table per user and sort by that?

5 Reply by ango

  • ango
  • Senior Member
  • Offline
  • From: Germany
  • Registered: 2006-03-07
  • Posts: 103

Re: Arcade Game Mod - SQL Query Help

I want to grab the top highscores of each game per user.
The output should look like this: First Place USER1 with 30 highscores, Second place USER2 with 20 Highscores and Third place USER3 with 10 highscores

My PunBB Arcade Mod & Game Packs 1-9 (update 01-06-2007):
Test and download here !

6 Reply by Smartys (edited by Smartys 2006-04-23 19:37)

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Arcade Game Mod - SQL Query Help

That's different from what you said originally and is different from what I said tongue
Anyways, just answer this: Would those 30/20/10 highscores be from ONE game, or ALL the games combined?
And if it's for one game, do you want this to only return for one game or do you want it to be ordered by the game ID first and then by the number of highscores?

  • Registered: 2006-03-15
  • Posts: 100

Re: Arcade Game Mod - SQL Query Help

i think i get it

example

ok i just played 10 games i got 10 high scores you played 20 games and you got 20 high scores and ango played 30 games and he got 30 high scores

the person who has the most high scores is first and so on

os it would be like this
First Place ANgo with 30 highscores, Second place Smartys with 20 Highscores and Third place dharmil with 10 highscores

http://www.dharmil.info/ - My site

http://www.yourarcadesite.1.vg/ - My Arcade, Play 250 games online for free and Save your high score!

http://www.forums.dharmil.info/ - My forums i created using php/mysql still working on them about 15% or more done

8 Reply by ango

  • ango
  • Senior Member
  • Offline
  • From: Germany
  • Registered: 2006-03-07
  • Posts: 103

Re: Arcade Game Mod - SQL Query Help

Yes, dharmil is right.
Sorry, that I did not clearly expressed what it means.

I´d like to create a small statistic block in front of the arcade game mod.
Not to graphic, just simple layout and easy to administrate like the fundamental idea of punbb.

My PunBB Arcade Mod & Game Packs 1-9 (update 01-06-2007):
Test and download here !

9 Reply by Smartys (edited by Smartys 2006-04-24 10:50)

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Arcade Game Mod - SQL Query Help

ah, OK

select username, count(*) as total_scores from arcade_ranking inner join users on (users.id = arcade_ranking.rank_player) group by arcade_ranking.rank_player order by total_scores desc LIMIT 3

10 Reply by dharmil (edited by dharmil 2006-04-24 19:39)

  • Registered: 2006-03-15
  • Posts: 100

Re: Arcade Game Mod - SQL Query Help

thanks

http://www.dharmil.info/ - My site

http://www.yourarcadesite.1.vg/ - My Arcade, Play 250 games online for free and Save your high score!

http://www.forums.dharmil.info/ - My forums i created using php/mysql still working on them about 15% or more done

11 Reply by ango (edited by ango 2006-04-25 07:51)

  • ango
  • Senior Member
  • Offline
  • From: Germany
  • Registered: 2006-03-07
  • Posts: 103

Re: Arcade Game Mod - SQL Query Help

thanx a lot, I´ll try ist.

For the moment I´ve fixed this with an "is_topscore" entry in the highscoretable. Maybe it´s better for later statistics.
You can see it here. User: punbb, PW: test

My PunBB Arcade Mod & Game Packs 1-9 (update 01-06-2007):
Test and download here !

12 Reply by ango

  • ango
  • Senior Member
  • Offline
  • From: Germany
  • Registered: 2006-03-07
  • Posts: 103

Re: Arcade Game Mod - SQL Query Help

I´ve tried the query but the result is not correct. This query collects all highscore entries per user. It counts also the 2., 3., 4. ...place of a user not only topscore (1.place).

My PunBB Arcade Mod & Game Packs 1-9 (update 01-06-2007):
Test and download here !
  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Arcade Game Mod - SQL Query Help

Oh, I thought you wanted all the highscores
Did you say you added a column is_topscore to rows on arcade_ranking? Then just change my query to

select username, count(*) as total_scores from arcade_ranking inner join users on (users.id = arcade_ranking.rank_player) where arcade_ranking.is_topscore = 1 group by arcade_ranking.rank_player order by total_scores desc LIMIT 3

14 Reply by ango

  • ango
  • Senior Member
  • Offline
  • From: Germany
  • Registered: 2006-03-07
  • Posts: 103

Re: Arcade Game Mod - SQL Query Help

Yep, thats it. thanx again

My PunBB Arcade Mod & Game Packs 1-9 (update 01-06-2007):
Test and download here !