0

I would like to create a table that is named with a number. Within phpMyAdmin it's totally possible. But when I try with create within a exec, it is not possible. I tried quotes and still no success. Only way I found was to add an alpha in front.

Won't work:

$Integer=1;    
$listersDatabase->exec("create table $Integer(row int(15) auto_increment primary key)");

Will work:

$Integer=1;    
$listersDatabase->exec("create table T$Integer(row int(15) auto_increment primary key)");

How can create a table name that is a number?

New Note:

If you are trying to create indexed tables to improve speed based on some ID for example user1_stats, user2_stats, user3_stats... you are wrong. MySQL allows you to partition tables based on some ID. MySQL performance: multiple tables vs. index on single table and partitions

halfer
  • 19,824
  • 17
  • 99
  • 186
Maciek Semik
  • 1,872
  • 23
  • 43

1 Answers1

3

Have you tried adding backticks?

From the manual: An identifier may be quoted or unquoted. If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it. (Exception: A reserved word that follows a period in a qualified name must be an identifier, so it need not be quoted.) Reserved words are listed at Section 9.3, “Keywords and Reserved Words”.

http://dev.mysql.com/doc/refman/5.7/en/identifiers.html

EDIT: Identifiers may begin with a digit but unless quoted may not consist solely of digits.

Ruben Vincenten
  • 807
  • 4
  • 7
  • also worth noting that [exec()](http://php.net/manual/en/pdo.exec.php) takes a string, and for the sake of clean code that isn't doing implicit conversions, $Integer should really be a string, not an int. – devlin carnate Jan 12 '16 at 23:54
  • Back tick worked perfectly. Why is there a difference between using a ` and ' ? First time I have ever used one. Thanks! – Maciek Semik Jan 12 '16 at 23:56
  • Because backticks are the default quote characters. If you really need to use single and/or double quotes, you can use ANSI_QUOTES: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_ansi_quotes I do advise against it, use single and double quotes for strings, not for escaping special keywords / weird table names. – Ruben Vincenten Jan 12 '16 at 23:59