Create a table for each user with their ID

Question

I have a problem with a website that I am currently creating that I need a table for each user but it does not work. I have tried to create a table during registration or the first login, which also works. My problem is with the naming of the table, because I want the table to have the following name. "user_1" The "user" is fixed and the number should be the ID of the user. This is how it should look like, the table user works without problems, but the other tables do not work.

DB currently:

users
user_

DB desired content:

users
user_1
user_2

Table users:

+----+--------------+-----------+------------------+---------------|
| id |  first name  | last name |  email           | password      |
+------------------------------------------------------------------+
|  1 |  user1       | user1     |  user1@gmail.com | password Hash||
+------------------------------------------------------------------+
|  2 |  user2       | user2     |  user2@gmail.com | passowrd Hash |
+----+--------------+-----------+------------------+---------------+

My previous opportunities: So far I have tried to create the table if the registration was successful. Here was the error that I could create the table, but it created it under the name "user_". The "id" was simply left out and so I can create a table.

With my second approach I tried to store a "False" value in the table during registration and check during login if the value is "False", if so then change this value to "True" and create the table. But here I had the same problem as with my first attempt that the id was not given to me.

My code: registration.php:

//Keine Fehler, wir können den Nutzer registrieren
if(!$error) {    
    $passwort_hash = password_hash($passwort, PASSWORD_DEFAULT);
    
    $statement = $pdo->prepare("INSERT INTO users (email, passwort, vorname, nachname) VALUES (:email, :passwort, :vorname, :nachname)");
    $result = $statement->execute(array('email' => $email, 'passwort' => $passwort_hash, 'vorname' => $vorname, 'nachname' => $nachname));
    
    if($result) {
            $pdo = new PDO('mysql:host=localhost;dbname=fitnessstats', 'root', '');

            if(isset($_GET['login'])) {
            $email = $_POST['email'];
            $passwort = $_POST['passwort'];

            $statement = $pdo->prepare("SELECT * FROM users WHERE email = :email");
            $result = $statement->execute(array('email' => $email));
            $user = $statement->fetch();
    
            //Überprüfung des Passworts
            if ($user !== false && password_verify($passwort, $user['passwort'])) {
                $_SESSION['userid'] = $user['id'];
                include 'createPersonalTable.php';
                die('<script language="javascript" type="text/javascript"> document.location="geheim.php"; </script>'); //'Login erfolgreich. Weiter zu <a href="geheim.php">internen Bereich</a>'
            } else {
                $errorMessage = "E-Mail oder Passwort war ungültig<br>";
            }
        }
        //User ID
        $showFormular = false;
    } else {
        echo 'Beim Abspeichern ist leider ein Fehler aufgetreten<br>';
    }
}

createPersonalTable.php:

<?php
session_start();
$pdo = new PDO('mysql:host=localhost;dbname=fitnessstats', 'root', '');

        $selectID = $pdo->prepare("SELECT * FROM users WHERE email = $email");
        $idOutput = $selectID->execute(array('id' => $useridTable));
        echo "$useridTable";

        $tablename = "user_" . $useridTable;
        //$tablename = "user_" . $vorname . $nachname;
        $createTable = "CREATE TABLE $tablename (
            id INT(255) AUTO_INCREMENT PRIMARY KEY,
            gewicht FLOAT,
            dauer FLOAT,
            anzahl INT
        )";
        if(mysqli_query($conn, $createTable)){
            echo "Table created successfully.";
            //  
        } else{
            echo "ERROR: Could not able to execute $createTable. " . mysqli_error($conn);
        }      
        if(isset($errorMessage)) {
            echo $errorMessage;
        }
?>

I think the main problem is reading the ID (i.e. in this section).

$selectID = $pdo->prepare("SELECT * FROM users WHERE email = $email");
        $idOutput = $selectID->execute(array('id' => $useridTable));
        echo "$useridTable";

Thanks in advance

Why not keep `gewicht` together with `user_id` in one table? That is proper way, not creating separate tables for each user. Your approach will be not maintainable. E.g. you need to add new column or list all users where `gewicht > 100.2`, etc... — Justinas, Jul 09 '20 at 11:36
Does this answer your question? [Database efficiency - table per user vs. table of users](https://stackoverflow.com/questions/7544544/database-efficiency-table-per-user-vs-table-of-users) — Justinas, Jul 09 '20 at 11:37
"I need a table for each user"...this is almost certainly not a good design. Why do you think you need that?? What info are these tables storing exactly? Probably you can either put the columns directly into the users table, if it's a 1-1 mapping, or if it's a 1-many mapping you have a single "userdata" table, and have one more column "user ID" which serves as a foreign key back to the main users table. Maybe you need to take a break from writing this application, and study relational database design in more detail before you create any more unnecessary problems for yourself. — ADyson, Jul 09 '20 at 11:43
You are quite right, this code `$selectID = $pdo->prepare("SELECT * FROM users WHERE email = $email"); $idOutput = $selectID->execute(array('id' => $useridTable));` makes no sense. You prepare a query with no parameters, then execute it with a parameter that you set to a variable that doesn't exist, then try to use that variable. Look at how it differs to the other similar query in your code. — droopsnoot, Jul 09 '20 at 12:05
There's also no need to keep creating new database connections when you already must have a connection in order for your first query to have worked. If there's a valid reason for them, such as connecting to different databases, call them something different or they'll overwrite each other. — droopsnoot, Jul 09 '20 at 12:07
But to echo the above posters, having a table per user is a terrible idea. It's up there with storing multiple values in a single column because it saves having a separate table. — droopsnoot, Jul 09 '20 at 12:09
Thanks for all replies I will not do it now by creating a separate table for each user, but I will create a main table where I store the data and partly refer to a foreign key. — mattia_m, Jul 09 '20 at 12:39

score 2 · Accepted Answer · answered Oct 11 '20 at 12:36

A look back: The question above did not make much sense in retrospect, because normally you create relations between tables, if you come across this question in the future, look here for a suitable solution.

I don't write all my code in here because it's a lot, but I have rearranged my tables a bit and W3schools has helped me a lot to solve the problem a bit differently.

https://www.w3schools.com/sql/sql_foreignkey.asp

https://www.w3schools.com/sql/sql_join.asp

score -1 · Answer 2 · answered Jul 09 '20 at 11:42

-1

I use PDO and there a function called lastInsertId() You can use it when you store a new user in the users database For example: $id = pdo->lastInsertId();

Then you add this $id variable in your table name

answered Jul 09 '20 at 11:42

Baptiste Leroux

49
8

Create a table for each user with their ID

DB currently:

DB desired content:

Table users:

2 Answers2