mysql 表 变量_在mysql中创建表时使用变量作为表名

I have variable whos value is a random number between 0 and 1000, I would like to use this as the name when creating a new table. I have tried to do this by concatenating my sql with the variable that stores the random number, this hasn't worked, is there a way of doing this? Thanks

include 'includes/db_connect_ssg.php';

if (isset($_POST['new_user_name'])&&isset($_POST['new_user_password'])) {

$username = $_POST['new_user_name'];

$password = $_POST['new_user_password'];

$randID = rand(0,1000);

$sql = "INSERT INTO `Users`(`id`, `username`, `password`, `admin`, `href`) VALUES ('$randID','$username','$password','0','ssgprofile.php?id=$randID')";

$query = mysqli_query($dbc, $sql);

$id = (string)$randID;

$q = "CREATE TABLE CONCAT('userTable_',$id) (

id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,

firstname VARCHAR(30) NOT NULL,

lastname VARCHAR(30) NOT NULL,

email VARCHAR(50),

reg_date TIMESTAMP

)";

$qquery = mysqli_query($dbc, $q);

if ($query&&$qquery) {

include 'admin_loadUsers.php';

}else{

echo "Could not connect sorry please try again later, for more info please contact BB Smithy at 0838100085";

}

}

解决方案

You could just use:

$q = "CREATE TABLE `userTable_".$id."` (

id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,

firstname VARCHAR(30) NOT NULL,

lastname VARCHAR(30) NOT NULL,

email VARCHAR(50),

reg_date TIMESTAMP

)";

But beware, creating a table with name containing a number is nearly always a sign of bad database design.

Instead of creating many tables with just one row, simply add columns firstname, lastname, email and reg_date to your table Users. Also your way of generating user ID by calling rand(0,1000) will result in collisions (rand will return a value which is already used as an ID in Users table). Use AUTO_INCREMENT for generating user IDs.