MySQL Table Name

[TheServant]

Hey guys, a bit of a joint post with MySQL, but I though I'd try here first.

I am planning on making an online game, but there will be different ages. So my first will be Age 0 (a0) and second Age 1 (a1) etc...
Creating the tables like "a0_user_info" will be easy, but I would like to have one registration form, so that you can choose which age you are registering with. The problem comes when I am inserting the submitted data into the correct tables:
[PHP]mysql_query("INSERT INTO user_info(user_name)
VALUES('$submitted_name')")
or die(mysql_error());[/PHP]

I would like to have something like:
[PHP]mysql_query("INSERT INTO $age.'_user_info'(user_name)
VALUES('$submitted_name')")
or die(mysql_error());[/PHP]

Which should submit the data to table a0_user_info in the case of the user registering to Age 0. So is that INSERT INTO syntax possible? It doesn't work, so I am not too confident.

I might have to make new variables where I join the strings prior to calling them from the database with a new variable, but I would prefer not to and figure out the syntax to do it in one line like I've tried above.

[Atli]

Hi.

That could be as simple as:

$age = "a0";
$sql = "INSERT INTO {$age}_user_info ETC...";
 

[Atli]

Just occurred to me...

Would it not be simpler to have all ages in the same table and having an Age column, specifying the age?

[coolsti]

To the OP:

your syntax is wrong, and Atli pointed out an example with correct syntax. You were close, but you have some single quotation marks that should not be there in your query.

Basically when you have a problem like this, you should open up a MySQL console window (or using whatever direct access to mysql is available to you) and copy and paste your query with the PHP variables substituted for acceptable values, and then see if MySQL accepts your query or informs you that there is a syntax error.

You can do this by adding a line like echo $query to your code and then you will see in the browser what your query statement has evaluated to, and then copy and paste this into the mysql console.

In your case, you would then see right away that there is a syntax error, and having the actual query statement echoed to the screen may make it clear to you why.

Steve, Denmark

[Markus]

Just occurred to me...

Would it not be simpler to have all ages in the same table and having an Age column, specifying the age?

This makes alot more sense to me, TS.

Unless you have reasons for having seperate tables?

Markus, England.

;)

[TheServant]

This makes alot more sense to me, TS.

Unless you have reasons for having seperate tables?

Markus, England.

;)

Thanks for your help guys. The reason I don't have it all in one table is the ages will not be running simultaneously, more that I will leave old ages running even after the new one has started. So if I make changes to variables (adding new ones/getting rid of old ones) it will not affect previous ages. So if I have tables for each age, then they are isolated and I can make any changes I want to specific ages.

Very helpful syntax Atli. Thanks for your suggestion coolsti, I did try that in a few different ways, but didn't guess to try the '{' characters, so that's why I came here!

TS

[coolsti]

Good to hear you also tried to debug the query with entering it to mysql directly. That will be a tremendous amount of help when your queries become complicated, not only for debugging syntax but for seeing how long the query takes and if needed optimizing the query.

Just FYI, you could also have done the following instead of using brackets, but I myself like to use the bracket idea:

$age = "a0";
$sql = "INSERT INTO " . $age . "_user_info ETC...";