Auto Creating a Table in SQL

Question

I wonder if someone can help?

I am new to PHP and have started creating a membership based website as a project to try and learn some new PHP and I was wondering what the correct syntax would be to auto-create a table?

Here is my current code, I am looking to create an individual table for each user however upon trying and trying I can't seem to get it to work!

Any suggestions/corrections?

<?php
    require("dbconnection.php");

    if(empty($_SESSION['username'])) {
        header("Location: index.php");
        die("Redirecting to index.php");   
    }

    $user = $_SESSION['username'];
    $sql = "CREATE TABLE $user (id int(5) NOT NULL)";
    $result = mysql_query($sql);

    if(!$result) {
        echo "FAILED";
    }
    echo "CREATED";
?>

The dbconnection.php file is working correctly as all my other pages call it in order to carry out other tasks.

Are you sure you want a table for each user? It seems highly unusual. — Nathan H, Jan 16 '14 at 14:13
Add echo $sql; and "or die(mysql_error())" at the appropriate places. (This looks like a bad idea BTW). And don't use deprecated methods. — Strawberry, Jan 16 '14 at 14:14
I don't understand why you want to do this, but did you make sure you meet the required privileges to create tables? — Lars Beck, Jan 16 '14 at 14:14
**PLEASE, DON'T DO THAT!** Create a table for each user means that you systems has serious design problems. Study about normalization. — Jorge Campos, Jan 16 '14 at 14:15
Hi Nathan, Yes I have chosen to go this way as for what I am trying to achieve makes it easier to track the information for me personally. — skinmanater, Jan 16 '14 at 14:16
I still doesn't sound good. http://stackoverflow.com/questions/7544544/database-efficiency-table-per-user-vs-table-of-users — Nathan H, Jan 16 '14 at 14:18
What exactly are you trying to track? It can easily be done with a little normalization. — Jorge Campos, Jan 16 '14 at 14:18
http://stackoverflow.com/questions/19297966/creating-table-for-each-user — Nathan H, Jan 16 '14 at 14:18
Well, leaving the "why" question aside for a moment: could you try an `echo mysql_error()` right after the "FAILED"? — Aioros, Jan 16 '14 at 14:19
I recommend explaining reasoning as to why you want to do this, and let people here explain why it's bad, and how to make it better. — Nathan H, Jan 16 '14 at 14:20
Ok, as an example... If a user creates a table and each column relates to their school subjects for example, each user will have different subjects, if i do this in one table surely I will have one massive table? Surely for my usability it would be easier to have a separate table for each user which only contains their specific subjects?? I am obviously wrong in my intentions, I just wanted to make it as easy as possible... Thank you all for your comments. — skinmanater, Jan 16 '14 at 14:24
http://net.tutsplus.com/tutorials/databases/sql-for-beginners-part-3-database-relationships/ You'll find many ways to handle relationships in your database. — Needpoule, Jan 16 '14 at 14:31

score 2 · Answer 1 · answered Jan 16 '14 at 14:18

2

DO NOT DO THAT !

Why not inserting a new row in a single table that hold all data of all the users ?

$sql = "INSERT INTO users (username) VALUES ('".$sql."')";

answered Jan 16 '14 at 14:18

fluminis

3,575
4
34
47

score 1 · Accepted Answer · answered Jan 16 '14 at 14:30

Based on my experience, It's highly not recommendable to create each table for each user because it's very expensive in terms of space and resources. If Facebook were doing the same thing, they would be having 1.1 billion tables on their database! Instead of that they have 1 table for all these members. Use one table, then keep a Primary Key column e.g. Id to be used to identify the person. e.g.

$sql = "CREATE TABLE IF NOT EXISTS users (
          id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
          names VARCHAR(100),
          email VARCHAR(100),
          password VARCHAR(200)
        )";
mysql_query($sql) or die(mysql_error());

Then as user signs up, the id column auto increments, thus he/she will have a unique Id that you can use to trace him/her, like this;

$res = mysql_query("SELECT * FROM users WHERE id ='".$id."'") or die(mysql_error());
$data = mysql_fetch_assoc($res);
echo $data['names']." ".$data['email']; /* names, email, password ... etc */

This is much better. Rather than creating 1 million tables, you can just have 1 table for all the 1 million people.

Regards!

Thank you Brian, much more helpful than the DON'T DO THAT with no explanation! — skinmanater, Jan 16 '14 at 14:40

Auto Creating a Table in SQL

2 Answers2