4

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 '[email protected]'" error. any idea?

Thanks!

Improve this question
4
  • 1
    why do you need table-per-user? why not to store all the data in one table + field with email? Commented May 15, 2010 at 15:06
  • because there would be more ( 1 to 10 additional rows of info ) then just email & password for each user. Commented May 15, 2010 at 15:23
  • @user it is OK to have additional rows for each user. This is NORMAL database setup. Why don't you by yourself a book and learn a bit? Commented May 15, 2010 at 15:31
  • yeap, you're right. re-coding everything now. :))) Commented May 15, 2010 at 15:53

5 Answers 5

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

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.

Improve this answer

1 Comment

strange, my localhost ( xampp ) took such tablename without a problem, as long as it was in apostrophes.
2

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.

Improve this answer

Comments

1

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).

Improve this answer

Comments

1

Table names can't contain a @ or ..

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.