Davideo7 : Down rating character screen shots sounds like a lot of work... Wouldn't it just be easier to create a script to tally characters added in screen shots based on date and time these were submitted? Screen shots are already submitted with a date and time which they were added so making a script for character points in relation to their submitted dates and time should be possible. Even if users were to add characters to pre-existing screen shots you should be able to make the script check for those added characters and add/prevent points if that added character is done after an already existing screenshot. You already have this set so users can't add the same characters in multiple screen shots since it's 1 point per character per game so theoretically speaking the script attributes only 1 point per character per game to only 1 member (The first submitter).

This should be much easier than investigating thousands of screen shots, down rating duplicates and recalculate the points manually. You also have to consider that some screen shots might have duplicate characters with 1 or more characters that have other characters on them. In this case, players can gain what could be seen as unfair points when regarding the rule. You should also consider that there are 12k characters at this moment and we're only about 1/3 of the way through this competition. If this continues at the same rate, you can be talking about 30k characters added to Vizzed. This also implies members of Vizzed not in the top 5 who submitted screenshots of characters before those in the top 5. 

Fireproof : I've invested at least 3 hours into creating a query to do that and I even asked around on experts exchange and no help there. I could've made a script that flagged the screenshots when they initially got added but now it's too late for that now since we're already 1/3 of the way into the competition.

I could technically accomplish this problem using mostly PHP instead of relying on mostly mysql queries but that would be far too time consuming than what I currently can justify.

You seem like you're familiar with this sort of stuff? If so, maybe you could tell me how I could modify this query to pull off what I want it to do:


SELECT
`user_id`
, count(*)
FROM (
SELECT
@row_num :=IF( @prev_game = gcs.`game_id`
AND @prev_screen = gcs.`screenshot_id`
AND @prev_char = gcs.`character_id`
,@row_num + 1,1) AS RowNum
, gcs.`user_id`
, @prev_game := gcs.`game_id`
, @prev_screen := gcs.`screenshot_id`
, @prev_char := gcs.`character_id`
FROM game_characters_screenshot gcs

CROSS JOIN (SELECT
@row_num :=1
, @prev_game :=''
, @prev_screen :=''
, @prev_char :=''
) vars

LEFT JOIN game_screenshot gs ON gcs.screenshot_id = gs.id
ORDER BY
gcs.`game_id`
, gcs.`screenshot_id`
, gcs.`character_id`
, gs.`time` ASC
) SQ
WHERE
RowNum = 1
GROUP BY
`user_id`
;



And here's the query I'm currently using:



SELECT DISTINCT game_characters_screenshot.user_id as user_id, count( DISTINCT game_characters_screenshot.character_id, game_characters_screenshot.game_id ) AS total FROM game_characters_screenshot LEFT JOIN game ON game_characters_screenshot.game_id = game.id LEFT JOIN game_games ON game_characters_screenshot.game_id = game_games.id LEFT JOIN game_screenshot ON game_screenshot.id = game_characters_screenshot.screenshot_id WHERE hackType = '' AND game_screenshot.rating >= 3 AND cloneOf='' GROUP BY game_characters_screenshot.user_id ORDER BY total DESC


Davideo7 : Honestly, I know very little about this sort of thing but what I do know is that each language is only limited to what you think you can do with it. With that said, all I can offer is suggestions as I have an endless flow of ideas. If you think of it in a technical sense, you're going to spend a ton of resources (Time and Work) to shift through many characters to verify duplicates and adjust points. Since this is your current plan, it might benefit to use PHP to solve this problem since this code can be reused later and possibly for other potential competitions.

On one hand you can go back through each screen shot individually at this point so that your work is only 1/3 of what it could be by the end of the competition. On the other hand, you can leave those screen shots as they are since this is already 1/3 of the way in. Neither would be a wrong choice in my opinion.

All I would think that you would need is to award 1 point per distinct character per distinct game for the first user to submit if that distinct character and distinct game weren't previously submitted.

I'm not totally sure how to read MySQL though... but I wonder if you need  gcs.`game_id` and , gcs.`screenshot_id` and , gcs.`character_id`  because it only needs to be ordered by time.... I'm probably wrong but maybe I was able to supply an idea or 2 here.

Fireproof : I guess we'll just have to wait and see what I have time for as the month progresses. Thanks for all the input.

Davideo7 : Wow this doesn't sound easy

Broke the 4,000 mark. And with that, I think the last Yu-Gi-Oh! game has been completed. I am glad. I am sick of looking at Yu-Gi-Oh! cards. I was really not wanting to do that in the first place. But 3 users were on those pretty hard, and I was just struggling to even keep up. So I kind of had no choice

But at least they are done, and I can finally look at other games to do characters for

Well this was fun while it lasted. Going in and tagging characters and enemies was fun, but as soon as it turned into the brain-dead sit at your computer for hours on end tagging 1000's of Yu-Gi-Oh cards, I've started to lose interest. The Yu-Gi-Oh games are just too darn lucrative for a competition of this sort. You don't even have to play the games to earn the cards. You just look up a password list to enter in for the cards. I could tolerate doing it myself for a little bit, but I think my brain'll turn to mush if I so much as look at another Yu-Gi-Oh card.

Next up, people will be tagging the same ol' cards for the JP language Yu-Gi-Oh games. . .

madison : Probably should erase that so nobody sees that last part

rcarter2 : How much did you do? 3 yugioh games or 2. Also, when this competition started i said it's gonna be about endurance. And how much of sitting there tagging things you can go through. Nice 1.3k lead though. You may have this in the bag.

madison : First i need to learn japanese.lol

mostcheats43 : It's not hard, just time consuming.

rcarter2 : I can't believe how quickly we hit the 10,000 mark, I'm just glad I didn't make the reward even higher at like 25,000 because we're definitely going to hit that and I would definitely be broke after this competition if I made a higher reward lol.

This was a great idea though, I'm glad I made this competition and offered a huge reward because it resulted into a lot of content for Vizzed.

Also, just make sure you don't fall victim to this rule:
"-If more than 75% of your tagged characters are in games where others have tagged the same characters, than you are not eligible for the cash prizes "

I didn't check your submissions but just reminding you about the rule.

madison : Also reminding you of the rule as well (refer to what I said to rcarter2).

mourinhosgum : It's too late to add this rule in now but I probably should've made it so you can only get up to 10 points per a game. On the other hand, we probably wouldn't have as much work done so maybe it's a good thing I didn't do that.

Davideo7 : I think we all did a silent truce rule because of it. I never touched any game anyone has done and same with the others i think. And since most of our points come from the yugioh games i never touched one someone else has. The top 3 have 10 000 points alone.
Also, i like to think of this as Doing work for the site and not like a comptetion. We're just getting paid like a real job for this. Much faster way to get work done on the site when you offer $425

Once the rule was in place, I tried not to tag any screenshots that others have already done, but racarter on a few occasions kept coming to the game at the same time I was playing it, and I know he tagged a few that I already did.

mourinhosgum : Just a brief glance at rcarter's screenies and I counted roughly 2k Yu-Gi-Oh cards. If nothing else, David will have a fairly comprehensive database of Yu-Gi-Oh cards by the end of this, hehe. . . D:

madison : Why did tyranit decide to bring pokemon and yugioh into this. Also even though i only did a bit of it he did steal sacred cards from me. And i have tried to do the same to him with some fighting games

tyranit : This could've all been avoided.lol

mourinhosgum : That's great to hear. That's pretty much what this has become, you guys doing work for the site for money. And like I already said, it's money well spent because this has resulted into a ton of submitted screenshots, characters, etc. Now I'll just have to make sure that next month's competition has a big prize like this so that I can get tons more work done for the site

madison : And here I thought it was going to be the Pokemon games that users would score the most points off of lol.

mourinhosgum : Pokemon isn't too bad because you actually have to play the game to see all the different Pokemon. Unlike Yu-Gi-Oh, where I haven't played a single match, and have several hundred cards by putting stupid passwords in.

Davideo7 : You may want to watch how much money your putting up for grabs. This competition has a max of $425 and a little while ago you were selling games for tax time... Also, this has made me learn a bit about different games. Like how koopa troopas are the koopas that are just walking around. I always thought they were just koopas. And lots of different kirby enemies. Like the awesome ghost with sunglasses on in kirbys dream land.

madison : i've actually been doing some pretty sweet pokemon things cause of this. I solo'ed the 8 gyms in ruby with just blaziken and HM slaves. and am using only a nidoking and charizard in Yellow

madison : That's a good point. Of course, if someone really wanted to, they could find a way to alter their Pokemon save file or download a compatible one online with a full Pokedex and that wouldn't be against the rules.

mourinhosgum : To be honest, I wasn't so sure we'd hit the 10,000 mark, plus I had planned on being a bigger threat and possibly claiming one of the top 3 spots just so I could save myself some money lol but I wont be doing that now since the top spots in this competition are just way out of reach for me.

Yeah $425 is a ton of money but thankfully I've had a lot of donations lately. I wont be investing this much into next month's competition, it'll probably just be a regular MEGA competition where 1st gets $50 and 2nd gets $25.

Early on I was competing in this competition and I still sorta am (but not for a top 5 spot) and I've been having a lot of fun doing so. It's fun to get to know these various game characters a little bit better by finding good screenshots to take of them, finding games that they are in, etc. These game characters are a big part of retro gaming so it's great that they're getting recognition because of these new features and this competition.

Davideo7 : For reals? I. . . think I need to figure out how to do that.

Davideo7 : I was planning on manipulating the saves or using a gameshark but couldn't figure it out.lol
One series i discovered is world heroes. It's a fighting game that has given me quite a few laughs with the different modes and characters, and i beat or got far in many games i used to play. I beat SMW like 3 times in like 30 minutes each

madison : Or you could find someone on the site who has a full Pokedex of a Pokemon game and have them send it to you