MYSQL error

[unknowngiver]

hey
i am getting this error:

Documentation
#1064 - 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 'CREATE TABLE `messages` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`from` varchar' at line 7

for this MYSQL :

CREATE TABLE `keys` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`key` varchar(32) NOT NULL DEFAULT '',
PRIMARY KEY  (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `messages` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`from` varchar(255) NOT NULL DEFAULT '0',
`to` varchar(255) NOT NULL DEFAULT '0',
`message` longtext NOT NULL,
`title` varchar(255) NOT NULL DEFAULT '',
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`read` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) NOT NULL DEFAULT '',
`password` varchar(32) NOT NULL DEFAULT '',
`name` varchar(255) NOT NULL DEFAULT '',
`email` varchar(255) NOT NULL DEFAULT '',
`ip` varchar(255) NOT NULL DEFAULT '',
`admin` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

can anyone help me out? thanks

 

[Peter]

if you are trying to execute all 3 together which it looks like you are you need to put a ; after each query:-

 CREATE TABLE `keys` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`key` varchar(32) NOT NULL DEFAULT '',
PRIMARY KEY  (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `messages` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`from` varchar(255) NOT NULL DEFAULT '0',
`to` varchar(255) NOT NULL DEFAULT '0',
`message` longtext NOT NULL,
`title` varchar(255) NOT NULL DEFAULT '',
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`read` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) NOT NULL DEFAULT '',
`password` varchar(32) NOT NULL DEFAULT '',
`name` varchar(255) NOT NULL DEFAULT '',
`email` varchar(255) NOT NULL DEFAULT '',
`ip` varchar(255) NOT NULL DEFAULT '',
`admin` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;

This is so mysql can distinguish between each sql statement, if you do not do this it thinks it is just 1 statement.

 

[Kate]

hey
i am getting this error:

Documentation
#1064 - 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 'CREATE TABLE `messages` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`from` varchar' at line 7

:hi:
FROM is a reserved keyword in SQL.
Avoid using reserved keywords in field names or expressions and you'll be fine

 

[Peter]

:hi:
FROM is a reserved keyword in SQL.
Avoid using reserved keywords in field names or expressions and you'll be fine

It isn't that causing the problem. Run it through mysql and you will see they work when you do them separately but when together it fails because of the missing ;

The following is taken from THIS page from the mysql manual:-

A common problem stems from trying to use an identifier such as a table or column name that is a reserved word such as SELECT or the name of a built-in MySQL data type or function such as TIMESTAMP or GROUP.

Reserved words are permitted as identifiers if you quote them as described in Section 9.2, “Database, Table, Index, Column, and Alias Names”

In other words if you use the ticks around the field name as the op has already done.

 

[unknowngiver]

yup i ran it seprately and worked fine:D thanks