Incorrect table name, php/mysql

Question

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!

why do you need table-per-user? why not to store all the data in one table + field with email? — zerkms, May 15 '10 at 15:06
because there would be more ( 1 to 10 additional rows of info ) then just email & password for each user. — Roger Travis, May 15 '10 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? — Your Common Sense, May 15 '10 at 15:31

score 4 · Accepted Answer · edited May 23 '17 at 12:13

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.

score 3 · Answer 2 · answered May 15 '10 at 15:07

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.

answered May 15 '10 at 15:07

Ignacio Vazquez-Abrams

776,304
153
1,341
1,358

strange, my localhost ( xampp ) took such tablename without a problem, as long as it was in `apostrophes`. – Roger Travis May 15 '10 at 15:21

score 2 · Answer 3 · answered May 15 '10 at 15:07

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.

answered May 15 '10 at 15:07

Your Common Sense

156,878
40
214
345

score 1 · Answer 4 · answered May 15 '10 at 15:08

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

score 1 · Answer 5 · answered May 15 '10 at 16:05

1

Table names can't contain a @ or ..

answered May 15 '10 at 16:05

luochong

35
5

Incorrect table name, php/mysql

5 Answers5