CREATE TABLE problem

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

re: CREATE TABLE problem

Message-ID: <42cdd29b.0309061840.52a98064@posting.google.com > from Andrew
contained the following:
[color=blue]
>It has to do with trying to create a table named $proj_ID. How do I
>get that to work?[/color]


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/

re: CREATE TABLE problem

"Andrew" <akoper@web4000.com> wrote in message
news:42cdd29b.0309061840.52a98064@posting.google.c om...[color=blue]
> 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[/color]

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...

re: CREATE TABLE problem

On 6 Sep 2003 19:40:58 -0700, akoper@web4000.com (Andrew) wrote:
[color=blue]
>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:[/color]

Avoid doing this. Don't create tables on the fly. Just add proj_id to the
primary key of a single Task table.
[color=blue]
>// 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?[/color]

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 (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)