在mysql中创建表时使用变量作为表名 [英] Use a variable as table name when creating a table in mysql

问题描述

我有一个变量whos的值是0到1000之间的一个随机数，我想在创建新表时使用它作为名称.我试图通过将sql与存储随机数的变量连接起来来做到这一点，但是这种方法没有用，有没有办法做到这一点?谢谢

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";
    }
}

推荐答案

您可以使用:

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

与其创建许多只包含一行的表，不如将列名，姓氏，电子邮件和reg_date添加到您的表Users中.同样，您通过调用rand(0,1000)生成用户ID的方式也会导致冲突(rand将返回一个已被用作Users表中ID的值).使用AUTO_INCREMENT生成用户ID.

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.

这篇关于在mysql中创建表时使用变量作为表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！