PHP MySQL CREATE TABLE

Question

I'm trying to create a table using PHP, PDO and MySQL. For the needs of my application, the name of the table has to be a variable.

Here is my code :

$request = $pdo->prepare("CREATE TABLE IF NOT EXISTS :table (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `parent_id` bigint(20) unsigned NOT NULL,
      `position` bigint(20) unsigned NOT NULL,
      `left` bigint(20) unsigned NOT NULL,
      `right` bigint(20) unsigned NOT NULL,
      `level` bigint(20) unsigned NOT NULL,
      `title` text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
      `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
      `content` text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;");
$request->execute(array(
    'table'=>$uuid));

Can't I use ":table" in the MySQL statement ?? Currently I wrote :

[...]
CREATE TABLE IF NOT EXISTS `$uuid`
[...]

This works but it sounds weird to me ^^' Is it the only solution to my problem ?

What happens whey you try to execute the version with :table? — DWright, Jan 21 '13 at 17:34
@rekire $uuid contains the result of uniqid("", true) which I use to generate random IDs — fwoelffel, Jan 21 '13 at 17:43
@DWright the version with :table doesn't work. It does nothing (no error) — fwoelffel, Jan 21 '13 at 17:44
possible duplicate of [Can PHP PDO Statements accept the table name as parameter?](http://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-name-as-parameter) — rekire, Jan 21 '13 at 17:49
And this is why I won't be moving away from the `mysql` extension anytime soon. — Niet the Dark Absol, Jan 21 '13 at 17:58

score 4 · Answer 1 · answered Jan 21 '13 at 17:34

4

You can't pass the table name as parameter. If you want to create table with variable name you must use dynamic query.

answered Jan 21 '13 at 17:34

Hamlet Hakobyan

32,965
6
52
68

I don't get it... ^^' I just need to create one table each time someone register on my website (one new user = one new table). Are dynamic queries really what I need ? How would you do it ? – fwoelffel Jan 21 '13 at 17:47
5

"one new user = one new table" worst design. Include `UserId` field which references to your users table PK. – Hamlet Hakobyan Jan 21 '13 at 17:50
Hokobyan I know it's a really bad design. But I'm running out of time for this project and this seems to be the simpliest version. I have a table which contains all my users and each users can store files online and access them trough a "tree-view". So each user has a table (like the one i'm trying to create using variable name) in which files are stored. The name of the table corresponds to the user id. – fwoelffel Jan 21 '13 at 18:01
2

@Eschard1991: I have to agree with Hakobyan... Youre headed to a land full of FAIL.... – prodigitalson Jan 21 '13 at 18:10
"But I'm running out of time for this project and this seems to be the simplest version." Do you know what is the `technical debt`. Read about this and you will understand that not the simplest way. – Hamlet Hakobyan Jan 21 '13 at 18:54

score 2 · Answer 2 · answered Jan 21 '13 at 18:14

$pdo->query("CREATE TABLE IF NOT EXISTS userfiles (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `parent_id` bigint(20) unsigned NOT NULL,
  `position` bigint(20) unsigned NOT NULL,
  `left` bigint(20) unsigned NOT NULL,
  `right` bigint(20) unsigned NOT NULL,
  `level` bigint(20) unsigned NOT NULL,
  `title` text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
  `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `content` text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8");

This is THE ONLY proper way of handling such situations.
Such matters are very basic things.
and your current setup is just like a car with square wheels.
Despite of your shortage of time you have to make it single table.
Otherwise you will waste A LOT more time and eventually will turn to the proper design anyway but after innumerable pains

PHP MySQL CREATE TABLE

2 Answers2