mysql_query wont create table

[BrianM]

This code creates the table without any problems when the user creates the new client information. I'm going to show only the part I'm having a problem with since I've narrowed it down to the exact area.

 

This code works:

$insert_two = "CREATE TABLE `".$_POST['projectnumber']."`
(
PermitProcess varchar(40),
Required char(3),
SubmittalDate date,
CommentsReceivedDate1 date,
ResubmitDate1 date,
CommentsReceivedDate2 date,
ResubmitDate2 date,
CommentsReceivedDate3 date,
ResubmitDate3 date,
CommentsReceivedDate4 date,
ResubmitDate4 date,
ApprovalDate date,
PermitNumber varchar(10),
CoCReqDate date,
CoCSubmittalDate date
)";
mysql_query($insert_two, $mysql_connect);

 

But when I add in -- `ID` not null auto_increment - and - primary key (`ID`) -- as shown below, it WONT create the table. What do I need to change, all MySQL says is that it's a syntax error which is apparent, I wish it would be a little more specific, but does anyone see a problem or have any suggestions to fix this problem so that my script creates the table with the ID field in it.

 

$insert_two = "CREATE TABLE `".$_POST['projectnumber']."`
(
`ID` not null auto_increment,
PermitProcess varchar(40),
Required char(3),
SubmittalDate date,
CommentsReceivedDate1 date,
ResubmitDate1 date,
CommentsReceivedDate2 date,
ResubmitDate2 date,
CommentsReceivedDate3 date,
ResubmitDate3 date,
CommentsReceivedDate4 date,
ResubmitDate4 date,
ApprovalDate date,
PermitNumber varchar(10),
CoCReqDate date,
CoCSubmittalDate date,
primary key (`ID`)
)";
mysql_query($insert_two, $mysql_connect);

[realjumper]

Shouldn't the ID field specify a length (of your choosing)?

 

`id` int(11) NOT NULL auto_increment,

[BrianM]

Simply stupidity on my part, thank you for pointing that out!

 

Problem solved.

[revraz]

If you have access to PhpMyAdmin and create a table in there, you can copy and paste the code to use for PHP as well.

[BrianM]

Anyone know of a way to set default values in the cells?

[DarkWater]

DEFAULT "lol" is how you do it I THINK.  Check the MySQL manual.

[BrianM]

Well here is what I got from that.

 

This works:

$insert_two = "CREATE TABLE `".$_POST['projectnumber']."`
(
ID tinyint not null auto_increment,
PermitProcess varchar(40) not null,
Required char(3) not null default 'N/A',
SubmittalDate date not null default '00-00-0000',
CommentsReceivedDate1 date not null default '00-00-0000',
ResubmitDate1 date not null default '00-00-0000',
CommentsReceivedDate2 date not null default '00-00-0000',
ResubmitDate2 date not null default '00-00-0000',
CommentsReceivedDate3 date not null default '00-00-0000',
ResubmitDate3 date not null default '00-00-0000',
CommentsReceivedDate4 date not null default '00-00-0000',
ResubmitDate4 date not null default '00-00-0000',
ApprovalDate date not null default '00-00-0000',
PermitNumber varchar(10) not null default 'N/A',
CoCReqDate date not null default '00-00-0000',
CoCSubmittalDate date not null default '00-00-0000',
primary key (ID)
)";
mysql_query($insert_two, $mysql_connect);

 

But after taking away the word 'date' after each field name it wont work, and the reason I did this is because the 'date' overwrites my default value I have set (00-00-0000) and sets it back to 0000-00-00.

 

$insert_two = "CREATE TABLE `".$_POST['projectnumber']."`
(
ID tinyint not null auto_increment,
PermitProcess varchar(40) not null,
Required char(3) not null default 'N/A',
SubmittalDate not null default '00-00-0000',
CommentsReceivedDate1 not null default '00-00-0000',
ResubmitDate1 not null default '00-00-0000',
CommentsReceivedDate2 not null default '00-00-0000',
ResubmitDate2 not null default '00-00-0000',
CommentsReceivedDate3 not null default '00-00-0000',
ResubmitDate3 not null default '00-00-0000',
CommentsReceivedDate4 not null default '00-00-0000',
ResubmitDate4 not null default '00-00-0000',
ApprovalDate not null default '00-00-0000',
PermitNumber varchar(10) not null default 'N/A',
CoCReqDate not null default '00-00-0000',
CoCSubmittalDate not null default '00-00-0000',
primary key (ID)
)";
mysql_query($insert_two, $mysql_connect);

 

Any suggestions or fix's?

[realjumper]

As far as I'm aware, Mysql will always default to 00-00-0000 for a `date` field where it is specified as NOT NULL.

 

EDIT: Sorry, I meant Mysql will always default to 0000-00-00 for a `date` field where it is specified as NOT NULL

[revraz]

If you use a DATE fieldtype, you need to conform to the date format rule, 0000-00-00

 

Plus, if you do want to change it, you won't be able to do anything useful in a 00-00-0000 format.

[BrianM]

Hah, never mind, I fixed it. I set each field as varchar(10) in the place of date.

[revraz]

Now re-read my 2nd sentence.  You didn't really "fix" it, you will just make it difficult for yourself later on when you want to query dates.

[DarkWater]

Yeah, you need it in the right format, dude.

 

DEFAULT '2007-02-05' would work because it's formatted correctly.

[realjumper]

Hah, never mind, I fixed it. I set each field as varchar(10) in the place of date.

 

That's cool...but what if you ever need to search by one of your date fields?...or ORDER BY one your date fields? You won't be able to with varchar

[BrianM]

But the company I'm doing this site for wants to date format as: 00-00-0000 not 0000-00-00 :|

[DarkWater]

So you can change that AFTER you retrieve it from the database...O_O  INSIDE the database, MySQL internally wants it to be 0000-00-00.

[BrianM]

ME > MySQL -- I get what I want lol

 

I wrote a dynamic table editor which allows you to edit a table using a query string in the URL /index.php?table=table_name and it updates the contents in the database. Took me a long time to get it working, but really I just wanted a default date value set as 00-00-0000 and not 0000-00-00 even though they will end up changing it later anyways.

[DarkWater]

But why mess with MySQL's internal date storage?  That's a BAD idea dude.

[piyushsharmajec]

I think for your usual format either you can fetch in your desired format,

by form it through php

[realjumper]

But the company I'm doing this site for wants to date format as: 00-00-0000 not 0000-00-00 :|

 

As darkwater says, you can format the date to anything you like after you retrieve it from the database....BUT....mysql will always store it as 0000-00-00. Check out www.php.net/date

[DarkWater]

Hell, check out DATE_FORMAT in the MySQL manual.

[piyushsharmajec]

We can fetch date from database and change it in our desirable format.

After that you can also store in format as you want by making changes in insert query.