douyudouchao6779
2017-02-05 00:08
浏览 315
已采纳

在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";
    }
}
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dtftao7249656 2017-02-05 00:53
    已采纳

    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.

    点赞 评论
  • duanmu6752 2017-02-05 00:11

    You do not have a valid table name

    From the Mysql docs:

    Identifiers may begin with a digit but unless quoted may not consist solely of digits
    
    点赞 评论

相关推荐 更多相似问题