MySQL Create new table syntax errors

Question

I was trying to create a new table with the following PHP/mysql code snippet:

                    $query = "
                        CREATE TABLE IF NOT EXISTS :user (
                            id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                            username CHAR(24),
                            summonername VARCHAR(16),
                            password VARCHAR(16),
                            region CHAR(3),
                            lvl INT(2) DEFAULT '0',
                            maxlvl INT(2),
                            status VARCHAR(20),
                            enabled INT(1) DEFAULT '1',
                            priority INT(1) DEFAULT '0',
                            note VARCHAR(150)
                        )
                    ";

                    $query_params = array (
                        ':user' => $user,
                    );

If I execute this one, I get some syntax error:

Failed: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''testuser' ( id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, use' at line 1

It is weird because I checked the syntax according to the official documentation (http://dev.mysql.com/doc/refman/5.1/de/create-table.html) and also some user-made examples. Also, is there a way to set the default encoding of the table to utf8 (not for every single row, but global)?

I would be happy if someone can tell me how to fix the syntax errors there,

You can't use parameters for identifiers like table or column names. — VMai
– VMai, Commented Sep 16, 2014 at 16:36
as @VMai said, you can´t use identifiers on create table. But you have this solution: stackoverflow.com/questions/3665784/… — Jacobson
– Jacobson, Commented Sep 16, 2014 at 16:37
And I'm sure INT(1)/INT(2) don't mean whatever you think they mean! — Strawberry
– Strawberry, Commented Sep 16, 2014 at 16:54
INT(1) is a 1 character int...!? Like, a number with the length of 1 character. I'm aware that it is not 1 but can be 0-9 — philn
– philn, Commented Sep 16, 2014 at 16:57
It only really makes sense in the context of ZEROFILL. But seeing as padding can achieve exactly the same thing, it doesn't make a lot of sense there either. — Strawberry
– Strawberry, Commented Sep 16, 2014 at 17:27

Ignacio Vazquez-Abrams · Accepted Answer · 2014-09-16 16:36:06Z

1

You cannot use parameters for metadata. Nor should you be creating tables per user. Either add a field to hold the user, or sanitize the table name yourself.

answered Sep 16, 2014 at 16:36

Ignacio Vazquez-Abrams

804k160 gold badges1.4k silver badges1.4k bronze badges

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

2 Comments

philn Over a year ago

Okay, so that's why it is throwing an error. But how can I create tables with variable names? // Because I kinda need to create tables for many different users. // I'm aware that this is not the correct way to store the users of the web site... They are stored in another (single) table obviously

Ignacio Vazquez-Abrams Over a year ago

Same way you replace variables into every other string in PHP; there's nothing special about SQL queries that prevents you from doing this.

Strawberry · Accepted Answer · 2014-09-16 17:33:04Z

0

With reference to my comment above, and by way of example...

 CREATE TABLE my_table(i INT(4) ZEROFILL NOT NULL PRIMARY KEY);

 INSERT INTO my_table VALUES (1),(9),(10),(99),(100),(999),(1000),(9999),(10000);

 SELECT * FROM my_table;
 +-------+
 | i     |
 +-------+
 |  0001 |
 |  0009 |
 |  0010 |
 |  0099 |
 |  0100 |
 |  0999 |
 |  1000 |
 |  9999 |
 | 10000 |
 +-------+

answered Sep 16, 2014 at 17:33

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Comments

Akhil Clement · Accepted Answer · 2014-09-17 07:17:52Z

0

please try this query in create table

CREATE TABLE IF NOT EXISTS `user` (
                        id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                        username CHAR(24),
                        summonername VARCHAR(16),
                        password VARCHAR(16),
                        region CHAR(3),
                        lvl INT(2) DEFAULT '0',
                        maxlvl INT(2),
                        status VARCHAR(20),
                        enabled INT(1) DEFAULT '1',
                        priority INT(1) DEFAULT '0',
                        )

answered Sep 17, 2014 at 7:17

Akhil Clement

6951 gold badge10 silver badges18 bronze badges

Collectives™ on Stack Overflow

MySQL Create new table syntax errors

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related