mysqli :: prepare SQL error

Question

Help please :). I'm gettig this error:

Warning: mysqli::prepare() [mysqli.prepare]: (42000/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 '?(id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id))' at line 1 in ***/classes/db.mysql.class.php on line 69

Warning: mysqli::prepare() [mysqli.prepare]: (42000/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 '?)' at line 1 in ***/classes/db.mysql.class.php on line 75

on this php code call:

public function createTable($tableName) {

    $this->connect();

    if ($stmt = $this->dbSocket->prepare("CREATE TABLE ?(id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id))")) {
        $stmt->bind_param("s", $tableName);
        $stmt->execute();
        $stmt->close();
    }

    if ($stmt = $this->dbSocket->prepare("INSERT INTO sys_userTables(userTableName) VALUES (u_?)")) {
        $stmt->bind_param("s", $tableName);
        $stmt->execute();
        $stmt->close();
    }

    $this->disonnect();
}

$tableName is string and is passed correctly.

connect() method is:

private function connect() {
    $this->dbSocket = new mysqli($this->dbHost, $this->dbUser, $this->dbPassword, $this->dbDatabase);
    if (mysqli_connect_errno()) {
        printf("Brak połączenia z serwerem MySQL. Kod błędu: %s\n", mysqli_connect_error());
        exit();
    }
}

TIA.

dup of [What is wrong with the SQL?](http://stackoverflow.com/q/8215433/), [Can I parameterize the table name in a prepared statement?](http://stackoverflow.com/q/11312737/) — outis, Jul 19 '12 at 10:50

F. Orvalho · Accepted Answer · 2012-07-19T11:14:55.830

1

You cannot use a table name as a parameter.

If the point of this is to create several tables with the same structure but different name, I suggest using something like:

$table_names = array('a', 'b', 'c');

foreach($table_names as $name) {
  $query = "CREATE TABLE `$name` (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id))";
  // run query or add it to a collection to run later
  // or append a ';' to the end of the string and do it with a multi_query
}

edited Jul 19 '12 at 11:14

answered Jul 19 '12 at 11:09

F. Orvalho

576
4
12

1

Do not forget to escape the table names since the mysqli driver will not escape the inserted variable. – Benedikt Wölk Jul 19 '12 at 11:12
I had overlooked it! Thanks :) – F. Orvalho Jul 19 '12 at 11:15

mysqli :: prepare SQL error

1 Answers1

Linked