0

What's the proper way to include a var in the table name of my sql query? I've tried 

CREATE TABLE `'$username'_table`

but that just creates

'username'_table

ive tried sever other combinations but i just keep getting either something with a space or quotes, i just want to to show up as simply

username_table






function makeUserTable($username)
           {
           return mysql_query("

    CREATE TABLE `'$username'_table` (
      `userid_id` bigint(255) unsigned NOT NULL AUTO_INCREMENT,
      `userid` varchar(255) NOT NULL DEFAULT '',
      `username` varchar(25) NOT NULL DEFAULT '',
      PRIMARY KEY (`userid_id`)
    )

    ");
           }
brybam
  • 5,009
  • 12
  • 51
  • 93

1 Answers1

2

Use {}:

`{$username}_table`

However, creating tables based on user names is never a good idea. For example, mySQL's table naming behaviour will differ based on the operating system - table names will be case sensitive on Linux/Unix, and insensitive on Windows. Also, the range of allowed characters will vary from file system to file system.

It's usually vastly better to have one big table with a username column.

Also, the method is vulnerable to SQL injection. If you choose to go this route, $username needs to be properly sanitized. Note that neither mysqli nor PDO can deal with table names as parameters.

Community
  • 1
  • 1
Pekka
  • 442,112
  • 142
  • 972
  • 1,088
  • Which is why using a wrapper like MySQLi or PDO comes in handy! –  Feb 23 '11 at 01:06
  • 2
    @Tim not really. :) [Neither mysqli nor PDO can parametrize table names](http://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-name-as-parameter) - another argument not to have dynamic table names in the first place – Pekka Feb 23 '11 at 01:07
  • Very interesting. I can see why it isn't supported and but why some people want to do it. –  Feb 23 '11 at 01:12
  • 1
    also, each table is a separate file system object and there can only be a certain system dependent number of tables/files open simultaneously -- if you have a lot of users, you will have performance hits each time your app needs to open a new table. – Jesse Cohen Feb 23 '11 at 01:14