Shmups Forum Leaderboards System

[bcass]

Following on from the discussions in the Death Smiles thread, this thread has been created to see if there's any interest in creating an Xbox Live style online leaderboards system for this website. This could be used to enter scores into, which users could then filter in various ways (more extensively than Xbox Live). This new system would basically be a replacement for all the hi-score threads. Potentially, it could save a lot of time and make browsing scores much more convenient.

MR_Soren has offered to do the coding. Failing that, I also have the relevant experience to complete the project.

There has been a suggestion that access to the system would be limited to people registered on the forum. The way I see it working, is that basically there'd be a link on the forum which takes you to the leaderboards page where you can then select a game. You'd then be shown all the hiscores for that game, together with drop-down selection boxes that allowed you to filter the data however you wanted, eg - view scores by date (week/month/year), game region, game format, game mode, game difficulty etc. Scores could be submitted by forum members via a web form, in which you could include notes, screenshots, etc. Selected trusted leaderboard admins would then be notified and could verify the scores and then click a button to permit them into the system. This last step may not be necessary, depending on whether or not the system was being abused.

Use this thread to make any suggestions. All ideas welcome.

[TrevHead (TVR)]

Even though automating the system would lead to more obsure shooters getting their own threads. Ild have to say nay to the idea. Because it would remove one of the aspects that is so cool about this forums high score section, the human touch. Atm when we post our scores most of us will tend to chat about the game sharing tips and scoring strategies many of which dont even make there way to the strategy section on this forum. Also theres that sense of community and competition when a popular game comes out and lots of ppl play at the same time.

Atm the strategy section is a bit on the weak side with not alot of activaty apart from the odd question or full on strategy guide post. Atm most of the forum's strategy descussion is split between shmups chat (although it plays second fiddle to news topics and all the increasing ranting and flame wars) and the strategy section (where talking crap isnt allowed). So anything that could futher decrease any strategy communication on this site is a bad idea. Although there is a possiblity that everything will migrate to the strategy section.

Maybe tools to aid the members who are running the threads might be a good idea, as long as it doesnt stop ppl posting they way they do atm.

[EPS21]

How about keeping the threads, yet still having the system input scores automatically so thread starters don't have to keep updating? Since there seems to be plans to include notes/comments in each submission, those comments along with the score can automatically be made into a new post, and people who just want to comment and reply to the thread still can. That way you get both the convenient automation while still retaining the human element.

[bcass]

Having the system automatically take scores from the first post of a thread would be more difficult, and probably less desirable as it's harder to validate a forum post than it is a user inputting into a dedicated webform. Having the new webform add a post to the relevant thread is possible though.

This new system doesn't have to replace discussion about scores. You could still have the score discussion threads but have a link in the first post linking to the hi-scores data (together with a link back to the discussion thread on the revelant games leaderboard page). It might be possible for the first post to contain a dynamically generated image showing the top 10 or something.

[bcass]

I suppose the most elegant solution to make it as seamless as possible would be to customise the current Reply form for the Hi Scores forum. The new reply form would have a tick box, which if ticked would display extra fields allowing you to enter a score etc. as well as your post. Automatically inserted before the 1st post of the thread would be the current scores (preferably filterable using drop-downs). Each displayed score could link back to the post the score was submitted in (which could contain supporting evidence, notes and discussion).

This idea is much more ambitious though and would require far more development time than what I proposed in the OP.

[captpain]

I suppose the most elegant solution to make it as seemless as possible would be to customise the current Reply form for the Hi Scores forum. The new reply form would have a tick box, which if ticked would display extra fields allowing you to enter a score etc. as well as your post. Automatically inserted before the 1st post of the thread would be the current scores (preferably filterable using drop-downs).

This idea is much more ambitious though and would require far more development time than what I proposed in the OP.

Yeah, wouldn't the OP have to express the format, somehow? Also it would need forums integration, of course.

There's something fun about the Hi Scores forum -- in-thread discussion, probably. It's fun.

[mrsmiley381]

I rather like the idea of a database of scores. If you need the help, please let me know as I have some database experience and am always looking to expand my resume and involvement in niche communities such as this. One suggestion I have is to allow people to post a link that will highlight their score in the database. This way, they can still start a thread and just link to the score. I know this might sound a little rough and not like a full explanation, but it's a thought.

[Icarus]

This idea is much more ambitious though and would require far more development time than what I proposed in the OP.

Easier than you think, for the most part.

First, run quick a conditional script in PHP to check if current forum ID is equal to the high score forum ID (highscore forum is "2", by the way), and if it is, run code to insert extra fields into the post editor.
A separate database to the forum database could be used to store scores, and ensure that for each game table, the scorer's username is also unique, so duplicate score posts are avoided. Then, you'd use SQL to run something similar to an INSERT INTO … ON DUPLICATE KEY UPDATE for all entries, which would first check to see if the user has a score posted already - if yes, update his entry with new score, if no, insert as new score. Listing scores in the OP would be as simple as sorting all the scores by descending (largest first) order.

This quick solution would only work for a single score table, however. Functions to have split tables - for example, by ship, difficulty mode, combinations of, etc - would be more challenging to implement, and would have to be looked at in a game by game basis. A possible way of doing it would be to have a lookup table with multiple primary keys for each sorting category, and run a foreach loop against each set to filter the results on listing:

Code: Select all

if (($ship == 'recoN') && ($mode == 'Original')) {
    foreach ($result as $array) {
        // execute HTML building loop to create score table
    }
}

That's a heavily simplified example, but it works. For multiple category sets (like for Mushi Futari v1.5, for example), a more object-oriented approach is required, which would first store all the category sets as a multidimensional array, iterate through each set, and loop a SQL query and resulting HTML generation script for each set.

I've done something similar with a recent restaurant project, which required dish filtering by type etc for a food menu-building feature implemented in the back-end CMS.

[bcass]

Easier than you think, for the most part.

Yeah, you might be right. I've assumed a worse-case scenario until I've actually seen what the posting.php code looks like together with any other significant anomalies associated with this version of the forum software. I was hoping we could have just one multi-purpose scores table for all games rather than a table for each game. It depends on what data we want to store.

[mrsmiley381]

This idea is much more ambitious though and would require far more development time than what I proposed in the OP.

Easier than you think, for the most part.

First, run quick a conditional script in PHP to check if current forum ID is equal to the high score forum ID (highscore forum is "2", by the way), and if it is, run code to insert extra fields into the post editor.
A separate database to the forum database could be used to store scores, and ensure that for each game table, the scorer's username is also unique, so duplicate score posts are avoided. Then, you'd use SQL to run something similar to an INSERT INTO … ON DUPLICATE KEY UPDATE for all entries, which would first check to see if the user has a score posted already - if yes, update his entry with new score, if no, insert as new score. Listing scores in the OP would be as simple as sorting all the scores by descending (largest first) order.

This quick solution would only work for a single score table, however. Functions to have split tables - for example, by ship, difficulty mode, combinations of, etc - would be more challenging to implement, and would have to be looked at in a game by game basis. A possible way of doing it would be to have a lookup table with multiple primary keys for each sorting category, and run a foreach loop against each set to filter the results on listing:

Code: Select all

if (($ship == 'recoN') && ($mode == 'Original')) {
    foreach ($result as $array) {
        // execute HTML building loop to create score table
    }
}

That's a heavily simplified example, but it works. For multiple category sets (like for Mushi Futari v1.5, for example), a more object-oriented approach is required, which would first store all the category sets as a multidimensional array, iterate through each set, and loop a SQL query and resulting HTML generation script for each set.

I've done something similar with a recent restaurant project, which required dish filtering by type etc for a food menu-building feature implemented in the back-end CMS.

That's some pretty good thought. My way of separating score values would be to do multiple for loops, where after selecting the game as a primary key you could load the next few categories as drop-down menus relative to the game selected. I might be thinking using some weird logic right now and I wish I could find a good example, but it sounds like with the right setup you could do just about anything.

[captpain]

WHAT CAN'T Icarus do?

[Icarus]

I was hoping we could have just one multi-purpose scores table for all games rather than a table for each game. It depends on what data we want to store.

You can. It'd be quite complicated, however. Just give each game a unique ID, and assign it to each posted score. Both username and gameid would be unique, for example, again to avoid double posts.

For example, the gamelist would look quite simple:

Code: Select all

| id | gamename                            |
--------------------------------------------
| 01 | Mushihimesama Futari ver1.5         |
| 02 | Guwange                             |
| 03 | Battle Garegga                      |

While the scores table - which would be pretty huge, to be quite honest, if we are storing EVERY score in the database - would look someting like this as an example, with the gamelist linked to it as a UFK = unique foreign key:

Code: Select all

| username (UFK)    | score               | ship (UFK)  | stage    | gameid (UFK) |
-----------------------------------------------------------------------------------
| Icarus            | 374,558,877         | Reco        | ALL      | 1            |
| Icarus            | 21,568,013	       | Kosame      | 6        | 2            |
| Icarus            | A,404,440           | Bornnam     | 6        | 3            |
| Aquas             | 336,918,336         | Palm        | ALL      | 1            |
| EPS21             | 302,568,226         | Palm        | ALL      | 1            |
| Prometheus        | 49,804,980          | Kosame      | ALL      | 2            |
| AST-NEV           | 21,419,069          | Shishin     | 6        | 2            |
| Plasmo            | G,766,020           | Gain        | ALL      | 3            |
| Vorpal            | C,518,160           | Gain        | ALL      | 3            |
| Ayanami           | 7,463,810           | Wild Snail  | 6        | 3            |

Since the scores table has multiple keys in it, you'd treat it like a lookup table. Again, this is super simplified, as "username" would list just the user IDs instead of full username (names are listed here to illustrate example). Since all the information you don't want to repeat is set as unique, all you'd do is check to see if (in this example) the particular user, ship type, and game id are present in a particular row when running the query, and if it is, update the score with the new information, otherwise insert it as a new row with all corresponding data. Again, this would be done with an INSERT INTO … ON DUPLICATE KEY UPDATE type of query. For example, if I were to post a new score with Palm for Futari ver1.5:

Code: Select all

$user = 'Icarus';
$score = '402,534,114';
$ship = 'Palm';
$stage = 'ALL';
$gameid = '1';

$sql = "INSERT INTO tblscores (username, score, ship, stage, gameid)
        VALUES ('$user', '$score', '$ship', '$stage', '$gameid')
        ON DUPLICATE KEY UPDATE score = '$score', stage = '$stage'";

if (!mysqli_query($link, $sql)) {
    // generate PHP/MySQL error message
}

This code would attempt to insert into the table the new information - if it finds a duplicate key (in this case, if I had posted a score for Palm already), it would update the old score with the new one, otherwise it will insert the score as a new entry, which, in this case, what this example would then do.

However, given how many scores total have been posted to the forum to date, this would make the table extremely big, searching for duplicate rows would take quite a while, and unless regular maintenance and indexing is done, it would make general database access extremely slow. The other issue is sorting it out into threads - the problems aren't just in category sorting (like in the previous post, splitting it into multiple categories such as "Reco Normal" and "Original"), but in some cases, multiple games as well (like the Caravan Shooting Game thread). Though, come to think of it, the '"multiple games thread" problem could be solved by treating the games themselves as categories…

The final issue that comes to mind is how to link the scores database to the phpBB forum database. I haven't used phpBB for quite a while, but I can remember it being really convoluted. You could store the scores (and corresponding tables) within the phpBB forum database, but given how big this forum is already, that would be similar to pumping several million gallons of saturated fat into an already huge blue whale.

It is possible, though. I'd have to sit down, think carefully and scribble down about how it could be done.

[mrsmiley381]

Well, if you have one database, you can have it ordered such that game name always comes first. Then you could use a different search algorithm - I forget what it's called, but it searches an array (or a table should work) in giant half chunks. If the table is large enough this should work much faster than a linear search - if you can implement such a search function in an established database.

A table for each game might work, but I'm a little rusty on databases and I think that might not be helpful at all.

I could be looking at the organization for this all wrong. If I am I hope my mistake can cause someone to have a stunning realization of some sort.

[Aliquantic]

It seems to me that the leaderboard would have to be integrated into the forums to really get some sort of traction (see here for an example that didn't get anywhere), as otherwise there may just be too much competition/people not wanting to submit scores twice... and even then it may not really change things much for anything other than the most active games. It still sounds like a very good idea if it can be implented, though, and a lot more convenient than having to use spreadsheets all the time or looking everywhere to see other people's scores... who knows, eventually it might even encourage people from other forums to post scores!

Speaking of which, it'd be handy if there was a choice between "silently" posting scores (in case of small updates or simply boring scores) and making a post on a thread like now for that community spirit. I can't really think of an elegant way of allowing that, though...

[MR_Soren]

I was planning to think this out more before starting discussions, but the ball is rolling now. Here are my thoughts:


I imagine that somebody would first "build" a scoreboard for each game.

They would begin by filling out a standard form of non-score data:
Game Type in the name of the game. Example: DeathSmiles MBL 1.1

Rules & Info This is a place to add instructions such as no continues, default settings, etc. Also post links to relevant articles/videos, etc. Basically the stuff you see in the first post in a high score forum thread.

Additional Moderators Usernames of other forum members who are allowed to edit this scoreboard - ideally a couple site admins/mods would be silently added to each board so somebody always has control of it.


Then there would be a form that custom builds the scoreboard for a specific game:
Player and Score Screenshot Video date will be pre-defined as every game should use these things.

Then, there will be options to add additional fields.
Number Adds an additional numerical field. Useful for games like Mars Matrix experience or DoDonPachi hit counter.
Select Adds a select box. Useful for selecting ships/characters/versions. Example: Sakura, Casper, Windia, Follett, Rosa Example 2: PCB, 360 J, 360 US
Text Allows players to add short notes that will not be filtered or sorted in any way. (maybe not necessary?)

The board would be created when the form is submitted, and there would be an automatically generated high score board index so the game could be found.

Drawback:
As suggested above, this might hinder or kill discussion about a game. Part of the fun of posting a score is the story and discussion that goes with it.
Possible solutions:
1. Attach discussion threads to each scoreboard, possibly a crude custom-written thing I tack onto each board.
2. Find a way to embed the scoreboards into threads on this forum.

I don't have any experience working with this message board system, so I do not know what is really possible here.


Drawback 2:
Importing old data could be a pain due to the varieties of data columns and ways of typing ship names, but ideally it would be done by pasting the text of an existing scoreboard into a parser that did the score uploads automatically.

[Icarus]

1. Attach discussion threads to each scoreboard, possibly a crude custom-written thing I tack onto each board.
2. Find a way to embed the scoreboards into threads on this forum.

1. Modify the current phpBB table structure to include a lookup table, and set the primary keys as "forum thread ID" and "game name/ID".
2. Modify the SQL query for displaying forum threads to check if the requested forum thread exists in the lookup table - if it doesn't, ignore, else populate the OP with the corresponding data from the scores table, using "game name/ID" as the main search parameter.

Quick idea to integrate auto-generated scoreboards with the existing forum threading system. There's probably a more elegant way of doing it, but its 12.38am here, I'm already wiped out from programming some personal projects, and I have work in the morning, so my brain isn't quite functioning right now.

[bcass]

it'd be handy if there was a choice between "silently" posting scores (in case of small updates or simply boring scores) and making a post on a thread like now for that community spirit. I can't really think of an elegant way of allowing that, though...

Shouldn't there always be supporting evidence for the score though? In which case, you'd always want to make a post containing your evidence if you uploaded a new score.

Also, with regards to SQL UPDATEs, there should probably be a check so that people can't update scores with lower values than those that are already in the table against their name.

[mrsmiley381]

it'd be handy if there was a choice between "silently" posting scores (in case of small updates or simply boring scores) and making a post on a thread like now for that community spirit. I can't really think of an elegant way of allowing that, though...

Shouldn't there always be supporting evidence for the score though? In which case, you'd always want to make a post containing your evidence if you uploaded a new score.

Require a file upload with it? That's on option, but not everyone has a camera or a reliable and believable way of proving it. What's been forum practice in the past?

[cools]

As I said in the DS thread, use phpBB3s databse for authentication only. Once you start integrating directly it's an absolute pig to update the forum software. A flexible scores DB should be easily achievable, just normalise around the various options currently available - if a game doesn't distinguish scores a particular way thats no big deal.

A link to the discussion thread is a good idea but thats just an URL to store so not difficult

[bcass]

I think there's too much merit in having it integrated into the actual forum rather than just a seperate page. I think a lot of people won't bother using it if it isn't integrated. Updates to the forum software could be a killer though, like you say. Maybe there's a more graceful way of doing this, like a java/flash object that can be embedded within the 1st post of a thread where scores could be viewed/filtered/uploaded via the object? The only links between forum and leaderboards would be forumID (use as gameID) and memberID (to validate score uploads).

[Shalashaska]

I think it sounds like a great idea (coming from somebody who wouldn't have to do any of the work ), both the OP and the more complicated suggestions. I don't think it'd kill off the discussions. If anything it may inject some life into the strategy section.

[marus]

I agree. A lot of the high-score threads don't get their first pages updated for a long period of time (or ever) when new scores are updated. It's especially a problem for older or more obscure games. While a system like this might make the process of uploading scores more impersonal, posting a score and then not having it put in the table is equally frustrating. Having a program that streamlines this process would alleviate this.

It would also serve as an easy way to keep track of personal scores - a user could just filter scores by username to see all of their personal scores and look over their notes. If the upload form allowed for links to replays and notes and whatnot, the program could also serve as an easy first-stop resource to anyone picking up a new game, allowing users to quickly scan the top scores and videos/strats without having to wade through a long thread.

I do think that there should be some kind of integration with this forum though. I couldn't see it getting much activity otherwise.

EDIT: An idea I just thought of would be to allow the system to send users notifications when certain events happen. For example, a user could choose to have a PM sent to him whenever a new score is posted for a certain game, or when one of his scores is beaten. Something else that might add back more of a personal touch would be to let users add other users as 'friends' or something. You could then set to have a PM sent to you when one of your friends adds a score, so you can easily keep track of a friend's or rival's progress.

[Despatche]

no

[zakk]

There's been a few attempts at external database sites for this stuff; they all seem to fail due eventual lack of interest for some reason.

While all the proposals of modifying phpBB are interesting; you'll have to do some pretty awesome persuasion to get bloodf to agree to it. The main issue is that maintaining local plugins/patches become a pain when you have to eventually upgrade due to a security issue or what not. You'll note this place runs pretty lean as far as phpBB plugins go; there's a reason for that.

What would actually be useful is a website that helps thread maintainers maintain the tables. I actually use something like this I developed for my threads (insert joke here about how I still don't update them). I just cut and paste people's score posts into a web form (with some editing since people inevitably just can't follow simple directions) and it parses it and puts it into a database. I then have a page that lets me filter it by ship/game/modes/whatever else and then spit out bbcode based on a template.

Like all such tools I write it was developed up to the point where it was immediately useful to me and then nothing else was added. So it's not something that I'll ever open up to general use unless I got some free time and sufficient motivation. Also I'm unenthused about taking on the support role for such a thing.
http://bigcore.rsdio.com/zakk/stuff/soe ... lllazy.png

[CStarFlare]

I've wanted this for a long time. Automated scoreboards are so wonderful (see solid state squad)

[Aliquantic]

Digging up through the forums... did anything come out of this thread?

[CStarFlare]

I actually started building one as a way to teach myself php. So far I can enter scores, filter the scoreboards by ship and platform, view a player's progress for each game or all his scores... I'm pretty excited that it seems to be functioning well and I think it's flexible enough to handle most games, but it's pretty ugly and I keep adding things to the list of things I'd like to do before I decide I'm done. I also admit that every time I try to work on it, I end up playing a game to try to get new scores to enter.

It's also obviously not integrated into the forums, so it's not really what people in this thread have been talking about.

Right now I'm just running it off my latptop, but eventually I'll try to get it up on a server just so I know how to do that. No idea if anyone would actually want to use it; the adoption rate would have to be pretty high for it to be worth using. Still, I think it's very nice to have the process automated and not have to think whether or not your score is worth the board owner's time, or if the owner is still actively updating, etc.

[cools]

If you can get it authenticating against the phpBB3 database, I'd be interested in using it on AO.

[bcass]

I still think my idea of an independant object that could be embedded within the first post of each thread is the best idea. That way, any updates/changes to the forum software wouldn't affect the scores system.

[Aliquantic]

Good luck with your project CStarFlare Of course I don't have the skills to help in any way, but I'd love to see a leaderboard like that gaining widespread use (something like Royalflare for Touhou).