How to properly include a var when creating a table in mysql php

Question

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`)
    )

    ");
           }

Community · Accepted Answer · 2017-05-23 10:24:25Z

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.

edited May 23, 2017 at 10:24

CommunityBot

11 silver badge

answered Feb 23, 2011 at 1:04

Pekka

451k150 gold badges990 silver badges1.1k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user142162 Over a year ago

Which is why using a wrapper like MySQLi or PDO comes in handy!

Pekka Over a year ago

@Tim not really. :) Neither mysqli nor PDO can parametrize table names - another argument not to have dynamic table names in the first place

user142162 Over a year ago

Very interesting. I can see why it isn't supported and but why some people want to do it.

Jesse Cohen Over a year ago

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.

Collectives™ on Stack Overflow

How to properly include a var when creating a table in mysql php

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related