Creating table mySQL syntax error

Question

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.

Deepak Rai · Accepted Answer · 2014-03-06 13:07:57Z

2

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)

answered Mar 6, 2014 at 13:07

Deepak Rai

2,2113 gold badges24 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Strawberry Over a year ago

...or just leave them out altogether.

Deepak Rai Over a year ago

please use backtick for table name as well. I just executed it and it's working for me

Sathish D · Accepted Answer · 2014-03-06 13:10:11Z

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 ;

Collectives™ on Stack Overflow

Creating table mySQL syntax error

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related