Create MariaDB table from a foreach cycle

Question

I have array with dynamic data and i need to create mysql table with array row[5] name. My code:

     if(is_array($myarray)){
     foreach ($myarray as $row) {

     $val6 = mysqli_real_escape_string($db_conn, $row[5]);

 $query ="CREATE TABLE $row[5] (
     id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
     USERNAME VARCHAR(30) NOT NULL,
     EMAIL VARCHAR(40) NOT NULL,
     DISCOUNT VARCHAR(5),
     PASSW CHAR(128),
     ROLE VARCHAR(9))";

    mysqli_query($db_conn, $query);
}

}

I try it but return error:

Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''peter05' ( id INT(6) UNSIGNED AUTO_INCREMENT PRIM...' at line 1

i need to create a MariaDB table into foreach cycle with the name equal to the value of $row[5].

some idea?

............................................................

The error message you show indicates you are using MariaDB, not MySQL, so I edited the title and tags to make that clear. — Bill Karwin, Feb 16 '23 at 17:37
@BillKarwin I do not understand why you reopen the question. The problem I say clearly the single quotes around the table name. — Jens, Feb 16 '23 at 17:42
Yes, but just closing the question doesn't show the OP what to do instead. — Bill Karwin, Feb 16 '23 at 17:45

Bill Karwin · Answer 1 · 2023-02-16T17:45:02.547

You shouldn't use mysqli_real_escape_string() for identifiers. It is used only for string values (and in most cases, using query parameters are better than escaping anyway).

if(is_array($myarray)){
  foreach ($myarray as $row) {

    $tablename = str_replace("`", "``", $row[5])

    $query ="CREATE TABLE `{$tablename}` (
      id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      USERNAME VARCHAR(30) NOT NULL,
      EMAIL VARCHAR(40) NOT NULL,
      DISCOUNT VARCHAR(5),
      PASSW CHAR(128),
      ROLE VARCHAR(9))";

    mysqli_query($db_conn, $query);
  }
}

This shows how to interpolate the tablename into your CREATE TABLE statement. This is necessary, because a tablename is not a string. You can't use string-escaping or parameters for an identifier like a tablename.

Putting backticks around the identifier is a good practice, because it could be an SQL reserved word, or contain special characters.

It is required, however, to be careful that the tablename doesn't contain a literal backtick character. In that case, change one backtick into two backticks.

Create MariaDB table from a foreach cycle

1 Answers1