PHP MySQL CREATE TABLE [duplicate]

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
– DWright, Commented Jan 21, 2013 at 17:34
@rekire $uuid contains the result of uniqid("", true) which I use to generate random IDs — fwoelffel
– fwoelffel, Commented Jan 21, 2013 at 17:43
@DWright the version with :table doesn't work. It does nothing (no error) — fwoelffel
– fwoelffel, Commented Jan 21, 2013 at 17:44
And this is why I won't be moving away from the mysql extension anytime soon. — Niet the Dark Absol
– Niet the Dark Absol, Commented Jan 21, 2013 at 17:58

Hamlet Hakobyan · Accepted Answer · 2013-01-21 17:34:14Z

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, 2013 at 17:34

Hamlet Hakobyan

33.5k6 gold badges55 silver badges70 bronze badges

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

5 Comments

fwoelffel Over a year ago

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 ?

Hamlet Hakobyan Over a year ago

"one new user = one new table" worst design. Include UserId field which references to your users table PK.

fwoelffel Over a year ago

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.

prodigitalson Over a year ago

@Eschard1991: I have to agree with Hakobyan... Youre headed to a land full of FAIL....

Hamlet Hakobyan Over a year ago

"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.

Your Common Sense · Accepted Answer · 2013-01-21 18:14:08Z

$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

Collectives™ on Stack Overflow

PHP MySQL CREATE TABLE [duplicate]

2 Answers 2

5 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Linked

Related