problem selecting data from table

[tim_poole247]

I have a table on my db called 'user_accounts' which stores each members info. I also have a db table called 'games' which stores data for different games on the server. Each game is identified by a 'db_name' which is stored in the 'games' table. the 'db_name' also acts as a prefix for other tables on the db specific to that game, for example 'db_name'_users. Im currently working on an Admin removal script. I can quite happily remove the Admin from the main user_accounts table. However, I need to find if that user is currently in any games, and if so delete them from the game aswell.

The main issue is because db_name is dynamic, and the script will be generic. Its part of an install utility, so the code needs to work for whatever the end user sets the db_name to.

What im trying to achieve is this:

a sql query on the 'games' table, which will give me all the 'db_name' values, and then search each 'db_name_users' table for the user. if the user is pressent then delete all entries of that user from that game. The user can be in multiple games, so it would have to delete them from all the games they are in.


I can normally figure things out via trial and error, but this is really causing me headaches, any ideas that someone can chuck at me 2 try out?

[Old Pedant]

Offhand, I'd say you have a bad database design.

What purpose is there in having each game in a separate table? Are the other fields in each of the separate tables SIGNIFICANTLY different from one game to the next? If not, they should be all in one table, which would simplify not only this task but many future task a lot.

Even if there are some fields that need to exist specifically for a given game, you'd be better off pulling all the common fields together into a single table and then only do the separate tables for the significantly different fields.

Read up on "normalization."

[Harmony.H]

Congratulations on having one of the most sophisticated phorums I've came across in some time! Its just incredible how much you can take away from something simply because of how visually beautiful it is. Youve put together a great phorum space great graphics, videos, layout. www.codingforums.com is definitely a must-see phorum! Great!