CODEWITHSUNDEEP
phpmysql
CarlRyds
CarlRyds

Reputation: 217

Creating table sql failed

Im getting the following error with this sql

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 '), messageRead VARCHAR(3), messageReadDateTime DATETIME(), messageReplied VARCHA' at line 8

and cant work out why it fails

$sql = "CREATE TABLE messages (
messageID INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
yearID VARCHAR(6),
fromLevel VARCHAR(255),
fromID VARCHAR(11),
toLevel VARCHAR(255),
toID VARCHAR(11),
sentDateTime DATETIME(),
messageRead VARCHAR(3),
messageReadDateTime DATETIME(),
messageReplied VARCHAR(3),
messageRepliedDateTime DATETIME(),
submitDate TIMESTAMP
)";

Any ideas?

Upvotes: 0

Views: 40

Answers (3)

Panda
Panda

Reputation: 6896

DATETIME() does not require the brackets.:

messageRepliedDateTime DATETIME

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

Source: http://dev.mysql.com/doc/refman/5.7/en/datetime.html

Upvotes: 1

spencer7593
spencer7593

Reputation: 108400

Remove the parens after DATETIME. That's not valid syntax.

messageReadDateTime DATETIME(),
                            ^^

should be just 

messageReadDateTime DATETIME  ,

Looks like there's several occurrences.

For debugging an issue like this, it can be helpful to shorten the table definition, to just one column, and see if that works. Then drop the table and try it again with two columns, building up until you find the problem.

Or, just post a question on StackOverflow. Whichever.

Upvotes: 3

juergen d
juergen d

Reputation: 204766

Remove the () after the DATETIME data type

CREATE TABLE messages 
(
    messageID INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
    yearID VARCHAR(6),
    fromLevel VARCHAR(255),
    fromID VARCHAR(11),
    toLevel VARCHAR(255),
    toID VARCHAR(11),
    sentDateTime DATETIME,
    messageRead VARCHAR(3),
    messageReadDateTime DATETIME,
    messageReplied VARCHAR(3),
    messageRepliedDateTime DATETIME,
    submitDate TIMESTAMP
)

Upvotes: 1

Related Questions