php - Creating dynamic tables with MySQLi securely -

i want able create dynamic tables, custom user surveys... survey monkey... how go create that?

because want give ability user create survey, different amount of text fields, , different option fields... need create custom table each survey.

would possible?

<?php  $table_name = 'survey_'.$_post['surveyid'];  $query = 'create table ? (             `responseid` int not null auto_increment,             `textarea1` text null,             `textarea2` text null,             `textarea3` varchar(255) null,             `drop_down1` varchar(255) null,             `drop_down2` varchar(255) null,             `bool1` bit null,             `bool2` bit null,         primary key (`responseid`))';  if($stmt = $mysqli->prepare($query)){     $stmt->bind_param('s', $table_name);     $stmt->execute();     $stmt->close(); }else die("failed prepare");  ?> 

the above example comes "failed prepare", because don't think can prepare table name... there work around using mysqli?

if(ctype_digit($_post['surveyid']) && $_post['surveyid']>0){      $table_name = 'survey_'.$_post['surveyid'];      $query = 'create table '.$table_name.' (             `responseid` int not null auto_increment,             `textarea1` text null,             `textarea2` text null,             `textarea3` varchar(255) null,             `drop_down1` varchar(255) null,             `drop_down2` varchar(255) null,             `bool1` bit null,             `bool2` bit null,         primary key (`responseid`))'; 

i know can try sanitize $_post['surveyid'] (like did above) prefer prepare if possible.

$table_name = 'survey_'.$_post['surveyid']; 

do not above. easy hacker exploit site if include $_get or $_post data directly in sql string.

but can't use parameters table name. parameter takes place of single scalar value only. you can prepare create table can't use parameters identifiers (e.g. table names).

the best practice make sure table name conforms rule, example leading portion of string of numeric digits, maximum length of mysql table name:

$table_name = 'survey_' . strspn(trim($_post['surveyid']), '0123456789', 0, 56); 

if have other rules surveyid, use preg_replace():

$table_name = 'survey_' . preg_replace('^(\w+)', '$1', trim($_post['surveyid']));