Jump to content


Photo

Problem with Create Table


  • Please log in to reply
4 replies to this topic

#1 andyg666

andyg666
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 31 August 2006 - 04:30 PM

I'm using XAMPP, with PHP 5 and MySQL 5. I have created a database and I am attempting to use the following PHP code to create tables:

mysql_select_db("my_db", $con);

$sql = "CREATE TABLE tblActivity 
(
ActivityID int NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(ActivityID),
LocationID int,
ActivityType varchar(15),
Accessibility text,
Popularity int(2),
AvgPopulation int(7),
Keywords text,
Description text,
CreatedBy varchar(15),
CreationDate datetime(yyyy-mm-dd hh:mm:ss),
ModifyDate datetime(yyyy-mm-dd hh:mm:ss)
)";

$result = mysql_query($sql,$con);
if (!$result)
  {
  die("Could not create tblActivity: " . mysql_error() . "<BR>");
  }
  else
  {
  echo "Creating tblActivity...<br>";
  } 
 

This code returns the following error message:

"Could not create tblActivity: 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 '(yyyy-mm-dd hh:mm:ss), ModifyDate datetime(yyyy-mm-dd hh:mm:ss) )' at line 13"


I'm not sure what is incorrect--I've checked this against tons of examples and tutorials. I can't find any errors in the syntax. Any ideas?

Thanks!

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 31 August 2006 - 09:08 PM

Drop those invalid modifiers to the datetime columns.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 andyg666

andyg666
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 01 September 2006 - 02:46 PM

i copied them directly from the tutorials at w3schools.com.

i've actually tried it without them ( just datetime() ) and i get the same error message. let me try it one more time and i'll report back. thanks.

#4 andyg666

andyg666
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 01 September 2006 - 03:53 PM

changing the last two lines to this:

CreationDate datetime NOT NULL default '0000-00-00 00:00:00',
ModifyDate timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
)";
did the trick.

thanks.

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 01 September 2006 - 04:47 PM

Glad you got it working.. it would have worked without the parentheses as well.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users