CREATE TABLE problem

[Andrew]

I have a problem creating mySQL tables with PHP. I am making an app
where a user can create a project. Pressing "submit" on proj_form.php
goes to proj_add.php where a couple of things happen. The project's
meta information is put into a project table, mysql_insert_id() gets
the $proj_ID, and a table named that $proj_ID is created to hold all
of that project's tasks. Here is my code to create the table for a
project's tasks:

// 2) Create a table for the tasks of the new project
$sql = "CREATE TABLE $proj_ID (
task_ID int unsigned NOT NULL auto_increment,
task varchar(40),
person varchar(10),
notes varchar(255),
PRIMARY KEY (`task_ID`))";

Here is the error message that creates:
You have an error in your SQL syntax near ' ( task_ID int unsigned NOT
NULL auto_increment, task' at line 1

It has to do with trying to create a table named $proj_ID. How do I
get that to work?

Thanks,
-Andrew

[Geoff Berrow]

Message-ID: <42**************************@posting.google.com > from Andrew
contained the following:

It has to do with trying to create a table named $proj_ID. How do I
get that to work?

The SQL is fine. Try echoing the SQL to make sure $proj_ID has a value.
--
Geoff Berrow
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Randell D.]

"Andrew" <ak****@web4000.com> wrote in message
news:42**************************@posting.google.c om...

I have a problem creating mySQL tables with PHP. I am making an app
where a user can create a project. Pressing "submit" on proj_form.php
goes to proj_add.php where a couple of things happen. The project's
meta information is put into a project table, mysql_insert_id() gets
the $proj_ID, and a table named that $proj_ID is created to hold all
of that project's tasks. Here is my code to create the table for a
project's tasks:

// 2) Create a table for the tasks of the new project
$sql = "CREATE TABLE $proj_ID (
task_ID int unsigned NOT NULL auto_increment,
task varchar(40),
person varchar(10),
notes varchar(255),
PRIMARY KEY (`task_ID`))";

Here is the error message that creates:
You have an error in your SQL syntax near ' ( task_ID int unsigned NOT
NULL auto_increment, task' at line 1

It has to do with trying to create a table named $proj_ID. How do I
get that to work?

Thanks,
-Andrew

I'm just as new but I believe what I've done below should work

$sql = "CREATE TABLE $proj_ID (
task_ID int NOT NULL auto_increment,
task varchar(40),
person varchar(10),
notes varchar(254),
PRIMARY KEY ('task_ID'))";

First off - I believe 'int' is unsigned automatically - and I'm more
definite of this since its an auto_increment column so I removed the
"unsigned" piece that you had.

Second - notes is too long - I think 254 is the maximume for varchar (since
zero is a number) - remember, you could always use TEXT - meaning changing
your notes entry to

notes TEXT,

from

notes varchar(255),
Third - you had the wrong hooks on your PRIMARY KEY - they should be
aposrtaphes (meaning ') and not hooks (meaning not `)

Again - I'm learning MySQL too - but give that a try...

[Andy Hassall]

On 6 Sep 2003 19:40:58 -0700, ak****@web4000.com (Andrew) wrote:

I have a problem creating mySQL tables with PHP. I am making an app
where a user can create a project. Pressing "submit" on proj_form.php
goes to proj_add.php where a couple of things happen. The project's
meta information is put into a project table, mysql_insert_id() gets
the $proj_ID, and a table named that $proj_ID is created to hold all
of that project's tasks. Here is my code to create the table for a
project's tasks:
Avoid doing this. Don't create tables on the fly. Just add proj_id to the
primary key of a single Task table.
// 2) Create a table for the tasks of the new project
$sql = "CREATE TABLE $proj_ID (
task_ID int unsigned NOT NULL auto_increment,
task varchar(40),
person varchar(10),
notes varchar(255),
PRIMARY KEY (`task_ID`))";

Here is the error message that creates:
You have an error in your SQL syntax near ' ( task_ID int unsigned NOT
NULL auto_increment, task' at line 1

It has to do with trying to create a table named $proj_ID. How do I
get that to work?

You've used `` backticks on the task_ID in the primary key - only use them
when you have very bad column names, e.g. containing spaces or weird
characters. They should never be necessary. Isn't the problem though.

The following works OK:

mysql> CREATE TABLE proj_ID (
-> task_ID int unsigned NOT NULL auto_increment,
-> task varchar(40),
-> person varchar(10),
-> notes varchar(255),
-> primary key (task_ID)
-> );
Query OK, 0 rows affected (0.00 sec)

What does $proj_ID contain? Are you sure it's correct?
--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)