CODEWITHSUNDEEP
mysqlsyntax-errorcreate-table
Chris 'Pig' Hill
Chris 'Pig' Hill

Reputation: 175

Creating table mySQL syntax error

I am trying to run the following query in PHP my admin:

CREATE TABLE IF NOT EXISTS 'ibn_table' (
'id' int(11) NOT NULL AUTO_INCREMENT,
'itransaction_id' varchar(60) NOT NULL,
'ipayerid' varchar(60) NOT NULL,
'iname' varchar(60) NOT NULL,
'iemail' varchar(60) NOT NULL,
'itransaction_date' datetime NOT NULL,
'ipaymentstatus' varchar(60) NOT NULL,
'ieverything_else' text NOT NULL,
PRIMARY KEY ('id')
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

I get this error:

#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 ''ibn_table' ( 'id' int(11) NOT NULL AUTO_INCREMENT, 'itransaction_id' varchar(' at line 1

Any help is appreciated.

Upvotes: 1

Views: 102

Answers (2)

Sathish D
Sathish D

Reputation: 5044

The column names should be in Backticks or just remove quotes. Backticks are to be used for table and column identifiers, but are only necessary when the identifier is a MySQL reserved keyword, or when the identifier contains whitespace characters or characters beyond a limited set (see below) It is often recommended to avoid using reserved keywords as column or table identifiers when possible, avoiding the quoting issue.

Try this:

CREATE TABLE `ibn_table` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `itransaction_id` VARCHAR(60) NOT NULL,
  `ipayerid` VARCHAR(60) NOT NULL,
  `iname` VARCHAR(60) NOT NULL,
  `iemail` VARCHAR(60) NOT NULL,
  `itransaction_date` DATETIME NOT NULL,
  `ipaymentstatus` VARCHAR(60) NOT NULL,
  `ieverything_else` TEXT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MYISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Upvotes: 0

Deepak Rai
Deepak Rai

Reputation: 2203

use backtick for quoting columns and table names

mysql> CREATE TABLE IF NOT EXISTS `ibn_table` (
    -> `id` int(11) NOT NULL AUTO_INCREMENT,
    -> `itransaction_id` varchar(60) NOT NULL,
    -> `ipayerid` varchar(60) NOT NULL,
    -> `iname` varchar(60) NOT NULL,
    -> `iemail` varchar(60) NOT NULL,
    -> `itransaction_date` datetime NOT NULL,
    -> `ipaymentstatus` varchar(60) NOT NULL,
    -> `ieverything_else` text NOT NULL,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.12 sec)

Upvotes: 2

Related Questions