SQL query to find games

[utw-Mephisto]

I am looking for an easy way to find the names of games, which never been played. Unfortunately it is not convinient when using arcade to sort them.

Is there a query I can run to get a list, which I can use in a simple script to deleted those games ? I have almost 1700 games and I would like to free up some space (no kidding :D )

[MrZeropage]

This will provide a list of Game-Names with those never played:
SELECT gtitle FROM ".TABLE_PREFIX."games_list WHERE timesplayed=0

normally you should never remove games manually this way from games_list as there might be scores, tournaments ect. in the DB left using that game, but if "timesplayed" is 0 it never has been played, so no worry.
So you can alter that query to delete all games the have never been played, just remember to remove the .swf and .gif files

[utw-Mephisto]

Thanks a lot .. it would be soooo perfect if you could also delete selected games in one go (Functions to execute with selected Games) [/hint] :)
edit:
Ok, the query did not work, but I just realised that (if I modify the query) it would not remove the game from the server anyway just from the database which does not really help ... dammit ... and since it goes back to the main menu after I deleted one game, you can imagine how much work it is to delete something like 800 games by hand :(

This was the error btw.:


An error occurred while attempting to execute your query. The following information was returned.
error number: 1064
error desc: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '".TABLE_PREFIX."games_list WHERE timesplayed=0' at line 1


But again - I won't need it unless I find a way to delete those games from the server AND filesystem ..

[MrZeropage]

you need to replace ".TABLE_PREFIX." with your tableprefix :D



and you realized my main problem for deleting multiple selected games - there have to be different steps to be done with each game:
- delete from filesystem
- delete from database
- delete scores, highscores from that game
- delete tournaments with that game


What you can do:
AdminCP -> Game List -> sort by "times played" -> mark/select all games never played and deactivate them

[utw-Mephisto]

Well .. I think I have to do it step by step then since deactivating doesn't really give me back my diskspace :P

At least I have something which keeps me busy during the nightshift :)
Is there a file or something I can change so that after I delete a game, it jumps back to the gamelist again instead of the main menu ?

[MrZeropage]

open the "delete"-link in a new tab/browser ;)

[utw-Mephisto]

Yeah - I just thought outside the box myself LOL :D

just checked - I don't have a table prefix but I think there is still an error in your query - I think it should be

SELECT gtitle FROM games_list WHERE gcount=0

[MrZeropage]

oh ok :D

you are right: gcount !

sorry, timesplayed is userinformation...

[utw-Mephisto]

Deleting about 830 by hand is quite ?tzend ... only 231 to go :(