Use a variable as table name when creating a table in mysql

Question

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

score 2 · Accepted Answer · edited Feb 05 '17 at 09:18

2

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.

edited Feb 05 '17 at 09:18

Your Common Sense

154,967
38
205
325

answered Feb 05 '17 at 00:53

Jirka Hrazdil

3,932
1
13
17

Hi thanks for your help that worked, I am new to database design and would appreciate your input, I am building a web app that has a small number of users, The content they see is determined by an Admin user, he can add and delete content while visiting a users profile. I need a way to display content based on which page profile is being displayed. Any ideas on this or resources you could point me to, Thanks for your time – Brian Smith Feb 05 '17 at 01:18
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 colisions (`rand` will return a value which is already used as an ID in `Users` table). Why don't you use `AUTO_INCREMENT` for generating user IDs? – Jirka Hrazdil Feb 05 '17 at 08:54
The users data is stored in a Users table which consists of username password etc., What I need however is a table to store a large amount of links that are different for each user as they are added by an admin user. The columns in the above example are ones i used for testing purposes. – Brian Smith Feb 05 '17 at 17:00
Then I would recommend creating table `CREATE TABLE Links (id int unsigned not null primary key auto_increment, userId int not null, link varchar(500))`. Column `userId` is used to link a row in the table `Links` to a user from `Users` table. `link` column contains the link itself (if I understood correctly, that by link you mean URL). – Jirka Hrazdil Feb 05 '17 at 17:58

score 0 · Answer 2 · answered Feb 05 '17 at 00:11

0

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

answered Feb 05 '17 at 00:11

Mithilesh Gupta

2,652
1
15
17

Hi thanks for input i used some typecasting to to turn $randID into a string value and used CONCAT() function in query, however this still isn't working any ideas? – Brian Smith Feb 05 '17 at 00:25

Use a variable as table name when creating a table in mysql

2 Answers2

Linked