[SOLVED] One more table question :)

[jeff5656]

I get an error with this table.  I suspect it is in the enum line but it LOOKS ok to me!

 

Error: 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 '(20) NOT NULL, procedure varchar(20) NOT NULL, proc_type enum (`GC`,`NS`,`' at line 5: CREATE TABLE procedures ( proc_id int(11) NOT NULL auto_increment, pt_id int(11) NOT NULL, proc_date date(20) NOT NULL, procedure varchar(20) NOT NULL, proc_type enum (`GC`,`NS`,`Staff`) NOT NULL, dx varchar(20) NOT NULL, PRIMARY KEY (proc_id) )

 

<?php 

include ("connectdb.php");


$sql = "CREATE TABLE procedures
(
  proc_id int(11) NOT NULL auto_increment,
  pt_id int(11) NOT NULL,
  proc_date date(20) NOT NULL,
  procedure varchar(20) NOT NULL,
  proc_type enum ('GC','NS','Staff') NOT NULL,
  dx varchar(20) NOT NULL,
  
  PRIMARY KEY  (proc_id)
)";

// Execute query
mysql_query($sql) or die(mysql_error().": $sql");


?>

[premiso]

I think you should be using single quotes for the enum line not backticks...

[twm]

procedure  is a reserved word in mysql.  change it to procedures, and it should work

[jeff5656]

I think you should be using single quotes for the enum line not backticks...

I tried it with and with the ' mark and it still gives me the error.

 

also, I changed it to procedures and it still gives me the same syntax error.

[twm]

ok, this worked in mysql:

 

<?php 

include ("connectdb.php");


$sql = "CREATE TABLE `procedures` (
  `proc_id` int(11) NOT NULL auto_increment,
  `pt_id` int(11) NOT NULL,
  `proc_date` date NOT NULL,
  `procedures` varchar(20) NOT NULL,
  `proc_type` enum('GC','NS','Staff') default NULL,
  `dx` varchar(20) NOT NULL,
  PRIMARY KEY  (`proc_id`)
)";

// Execute query
mysql_query($sql) or die(mysql_error().": $sql");


?>

[premiso]

Eh sorry, I was looking at the wrong spot for the error

 

proc_date of date(20)

 

Was where the issue was being thrown, you cannot define the size of type date.

 

proc_date date

 

would work.

[redarrow]

corrected......

<?php 

include ("connectdb.php");


$sql = "CREATE TABLE `procedures` (
  `proc_id` int(11) NOT NULL auto_increment PRIMARY KEY,
  `pt_id` int(11) NOT NULL,
  `proc_date` date NOT NULL,
  `procedures` varchar(20) NOT NULL,
  `proc_type` enum('GC','NS','Staff') NOT NULL,
  `dx` varchar(20) NOT NULL)";

// Execute query
mysql_query($sql) or die(mysql_error().": $sql");

?>

[redarrow]

Yes good lesion here, All mysql error's are before the error line what is shown.

 

So if it says line 5 like it did trust me it on line 4 lol.

 

weired but true..

 

For some reason mysql spits the line afther the error out but we no it the line before..

 

make no scence when it tells you it the line it showing like line 5 but problam on line 4.

 

 

[jeff5656]

Thanks!  That worked.