BrianM Posted May 14, 2008 Share Posted May 14, 2008 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); Quote Link to comment Share on other sites More sharing options...
realjumper Posted May 14, 2008 Share Posted May 14, 2008 Shouldn't the ID field specify a length (of your choosing)? `id` int(11) NOT NULL auto_increment, Quote Link to comment Share on other sites More sharing options...
BrianM Posted May 14, 2008 Author Share Posted May 14, 2008 Simply stupidity on my part, thank you for pointing that out! Problem solved. Quote Link to comment Share on other sites More sharing options...
revraz Posted May 14, 2008 Share Posted May 14, 2008 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. Quote Link to comment Share on other sites More sharing options...
BrianM Posted May 14, 2008 Author Share Posted May 14, 2008 Anyone know of a way to set default values in the cells? Quote Link to comment Share on other sites More sharing options...
DarkWater Posted May 14, 2008 Share Posted May 14, 2008 DEFAULT "lol" is how you do it I THINK. Check the MySQL manual. Quote Link to comment Share on other sites More sharing options...
BrianM Posted May 14, 2008 Author Share Posted May 14, 2008 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? Quote Link to comment Share on other sites More sharing options...
realjumper Posted May 14, 2008 Share Posted May 14, 2008 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 Quote Link to comment Share on other sites More sharing options...
revraz Posted May 14, 2008 Share Posted May 14, 2008 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. Quote Link to comment Share on other sites More sharing options...
BrianM Posted May 14, 2008 Author Share Posted May 14, 2008 Hah, never mind, I fixed it. I set each field as varchar(10) in the place of date. Quote Link to comment Share on other sites More sharing options...
revraz Posted May 14, 2008 Share Posted May 14, 2008 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. Quote Link to comment Share on other sites More sharing options...
DarkWater Posted May 14, 2008 Share Posted May 14, 2008 Yeah, you need it in the right format, dude. DEFAULT '2007-02-05' would work because it's formatted correctly. Quote Link to comment Share on other sites More sharing options...
realjumper Posted May 14, 2008 Share Posted May 14, 2008 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 Quote Link to comment Share on other sites More sharing options...
BrianM Posted May 14, 2008 Author Share Posted May 14, 2008 But the company I'm doing this site for wants to date format as: 00-00-0000 not 0000-00-00 :| Quote Link to comment Share on other sites More sharing options...
DarkWater Posted May 14, 2008 Share Posted May 14, 2008 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. Quote Link to comment Share on other sites More sharing options...
BrianM Posted May 14, 2008 Author Share Posted May 14, 2008 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. Quote Link to comment Share on other sites More sharing options...
DarkWater Posted May 14, 2008 Share Posted May 14, 2008 But why mess with MySQL's internal date storage? That's a BAD idea dude. Quote Link to comment Share on other sites More sharing options...
piyushsharmajec Posted May 14, 2008 Share Posted May 14, 2008 I think for your usual format either you can fetch in your desired format, by form it through php Quote Link to comment Share on other sites More sharing options...
realjumper Posted May 14, 2008 Share Posted May 14, 2008 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 Quote Link to comment Share on other sites More sharing options...
DarkWater Posted May 14, 2008 Share Posted May 14, 2008 Hell, check out DATE_FORMAT in the MySQL manual. Quote Link to comment Share on other sites More sharing options...
piyushsharmajec Posted May 14, 2008 Share Posted May 14, 2008 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.