Creating table based on user input

[sincspecv]

I need to set up a table in MySQL based on the users input in a form. i have the input assigned to a variable ($userdir) but when i run the script it just creates a table called '$userdir' instead of what the user typed in.

heres the code im using:
[code]
$userdir = $_POST['userdir'];
$connection = @mysql_connect('localhost', 'root', 'aboy15') or die(mysql_error());
$sql = 'CREATE TABLE $userdir ('
        . ' `date` DATE NOT NULL, '
        . ' `update` TEXT NOT NULL'
        . ' )'
        . ' ENGINE = myisam;';
$result = @mysql_query($sql, $connection) or die(mysql_error());
[/code]

what am i doing wrong?

[trq]

You need your query within double quotes. Php variables are not parsed oitherwise.

Really though, the question must be asked. Why are you making a new table for each user? This is usually a good indication of poor design.

[DarkendSoul]

I don't think what you want to do is attempt to create a table each time the user posts, you may want to insert rows. If this is true...

[sincspecv]

well, i have a forum about automotive performance. currently i am trying to create something so users can put up information about thier current project car. it's along the lines of cardomain (www.cardomain.com) but i want it to be more like a log. i want it set up so that each time the user updates their project it creates a new row, and in each row is the date and the update. i couldn't think of any better way to do this than to have a new table for each user. i thought about it for days and it was the best i could come up with. if you have a better idea i would be happy to hear it. this is my first big project with php. every other project, i've done was rather simple and usually had some sort of guideline.

thanks,
matt

[sincspecv]

when i use double quotes i get this error:

[quote]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"$userdir" ( `date` DATE NOT NULL, `update` TEXT NOT NULL ) ENGINE = myisam' at line 1[/quote]

[Jessica]

you need to concat the strings with .
'".$userdir."'

I think what you really want is:
$sql = "CREATE TABLE $userdir (date DATE NOT NULL, update TEXT NOT NULL) ENGINE = myisam";

[sincspecv]

[quote]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '".$userdir." ( `date` DATE NOT NULL, `update` TEXT NOT NULL ) ENGINE = myisam' at line 1[/quote]

[SharkBait]

Date is a type in MySQL change it to something else.

I also usually put my PHP variables inside curly braces too.

[code=php:0] $sql = "CREATE TABLE {$userdir} (......";[/code]

[trq]

Trust me, you do NOT want a new table for each user. You want 2 tables, one stores usernames and ids, the other stores project ids and that same user id. eg;

[code]
CREATE TABLE users (
  id INT PRIMARY KEY AUTO INCRIMENT,
  username
);

CREATE TABLE projects (
  id INT PRIMARY KEY AUTO INCRIMENT,
  users_id INT,
  date_added TIMESTAMP,
  data TEXT
);
[/code]

Then, anytime user 4 makes an update you would run a query like...

[code]
INSERT INTO projects (user_id,date_added,data) VALUES (4,NOW(),'here is my car');
[/code]

When he comes back in a few days to make an update, run another....


[code]
INSERT INTO projects (user_id,date_added,data) VALUES (4,NOW(),'ive painted the doors');
[/code]

Now, if you want to retrieve all of user 4's data, you'd simply run....

[code]
SELECT data FROM project WHERE user_id = 4;
[/code]

Now you have two tables that can hold an unlimitted number of users and updates.

[trq]

[quote]I also usually put my PHP variables inside curly braces too.[/quote]

This is totally unrequired unless your working with complex variables (arrays || objects).

[sincspecv]

well i got this to work:
[code]$sql = "CREATE TABLE $userdir (date DATE NOT NULL, update TEXT NOT NULL) ENGINE = myisam";[/code]
but i think im gonna go with this:
[quote author=thorpe link=topic=121870.msg501991#msg501991 date=1168489919]
Trust me, you do NOT want a new table for each user. You want 2 tables, one stores usernames and ids, the other stores project ids and that same user id. eg;


[code]
CREATE TABLE users (
  id INT PRIMARY KEY AUTO INCRIMENT,
  username
);

CREATE TABLE projects (
  id INT PRIMARY KEY AUTO INCRIMENT,
  users_id INT,
  date_added TIMESTAMP,
  data TEXT
);
[/code]

Then, anytime user 4 makes an update you would run a query like...

[code]
INSERT INTO projects (user_id,date_added,data) VALUES (4,NOW(),'here is my car');
[/code]

When he comes back in a few days to make an update, run another....


[code]
INSERT INTO projects (user_id,date_added,data) VALUES (4,NOW(),'ive painted the doors');
[/code]

Now, if you want to retrieve all of user 4's data, you'd simply run....

[code]
SELECT data FROM project WHERE user_id = 4;
[/code]

Now you have two tables that can hold an unlimitted number of users and updates.
[/quote]

thanks a lot man. i just feel stupid.

[trq]

Dont feel stupid... If youv'e not done it before you wouldn't know. You can look up tutorials on 'database normalization' if your keen to learn more, this is just the tip of the iceburg.