Incorrect table name, php/mysql

I've got this code

        mysqli_query ( $userdatabase,
        'CREATE TABLE `user_'.$emailreg.'` (
        ID int NOT NULL AUTO_INCREMENT PRIMARY KEY,

        IP varchar(10),
        FLD1 varchar(20),
        FLD2 开发者_开发知识库varchar(40),
        FLD3 varchar(25),
        FLD4 varchar(25),
        FLD5 varchar(25) )' );

        echo ( mysqli_error ($userdatabase) );

that works fine on my localhost, but when I upload it to the server, it starts giving me a "Incorrect table name 'user_vasya@rossya.pu'" error. any idea?

Thanks!

As soon as you say "I create a table per user" this is an antipattern I call Metadata Tribbles. The problem is that these tables tend to reproduce out of control. :-)

Instead, you should create one table, with a column for the user's email as an attribute.

If you use delimited identifiers, SQL table names can contain special characters, or even whitespace, SQL reserved words, international characters, etc. But if you take the back-quotes off, @ and . are not valid characters for an identifier, because it confuses the SQL parser. Think about this: do you use any other programming languages that accept . as a valid character in a variable name or class name?

Other tips:

IP is more easily stored as INT UNSIGNED, and there are functions INET_ATON() and INET_NTOA() to convert an IP address string to and from the binary equivalent. This helps you make sure you don't accidentally store an invalid IP address. Also you can use bitwise binary operators to do subnet masking operations against an IP address in binary form.

The columns FLD1, FLD2, FLD3 etc. are not descriptive and indicate that you haven't designed this table sufficiently. You might even need to split this table into a few separate tables to manage multi-valued attributes. But one can't tell how to do this since your columns are named so abstractly.

Table names can't contain a @ or .. Also, what you're trying to do is very poor database design. Use a single table, and add the email address (or better yet, an ID from another table as an integer) as a key.

Looks like it's incorrect filename for the host filesystem.

But you shouldn't create a table for each user anyway. It must be one table for all users with user_id field.

My first thought was "OMG! What a horrible name for a table!" :-)

The MySQL docs state that:

Database and table names cannot contain “/”, “\”, “.”, or characters that are not allowed in file names.

I suspect you violate that on two counts, the . in your domain name and (probably) the @ seperator.

You should probably consider having one table which is keyed on the email address (or another unique ID).

Table names can't contain a @ or ..