CODEWITHSUNDEEP
phpmysqlsyntax-error
philn
philn

Reputation: 323

MySQL Create new table syntax errors

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,

Upvotes: 0

Views: 187

Answers (3)

Akhil Clement
Akhil Clement

Reputation: 685

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',
                        )

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

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 |
 +-------+

Upvotes: 0

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 799450

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.

Upvotes: 1

Related Questions