SQL error while executing the query

Question

4 errors were found during analysis.

Ending quote ' was expected. (near "" at position 510)

A comma or a closing bracket was expected. (near "', `email` varchar(100) NOT NULL, `active` int(1) NOT NULL default ‘0'" at position 181)

Unexpected beginning of statement. (near "20" at position 268)

Unrecognized statement type. (near "NOT NULL" at position 272)

SQL query:

CREATE TABLE IF NOT EXISTS `users`
(
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL,
  `online` int(20) NOT NULL default ‘0',
  `email` varchar(100) NOT NULL,
  `active` int(1) NOT NULL default ‘0',
  `rtime` int(20) NOT NULL default ‘0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

INSERT INTO `users` (`id`, `username`, `password`, `online`, `email`, `active`, `rtime`) 
VALUES (1, ‘testing’, ‘testing’, 0, ‘[email protected]’, 0, 0);

You have curly single quotes in the question.

Gordon Linoff
– Gordon Linoff

2016-07-09 12:56:36 +00:00
Commented Jul 9, 2016 at 12:56 — Gordon Linoff
– Gordon Linoff, Commented Jul 9, 2016 at 12:56

juergen d · Accepted Answer · 2016-07-09 13:25:54Z

2

You use a different kind of quote character for instance

`online` int(20) NOT NULL default ‘0', 
         here --------------------^

Replace it with a normal one every time you use it

CREATE TABLE IF NOT EXISTS `users`
(
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL,
  `online` int(20) NOT NULL default '0',
  `email` varchar(100) NOT NULL,
  `active` int(1) NOT NULL default '0',
  `rtime` int(20) NOT NULL default '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

But since the type of those columns is int you don't need quotes around the default values at all.

edited Jul 9, 2016 at 13:25

answered Jul 9, 2016 at 12:56

juergen d

205k40 gold badges303 silver badges377 bronze badges

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

1 Comment

dnoeth Over a year ago

Those columns are INTEGERs, thus there's no need for quotes at all.

Tanmay Majumder · Accepted Answer · 2016-07-09 12:57:06Z

1

Just remove '' quotes from 0 for all Int datatype.

CREATE TABLE IF NOT EXISTS users ( id int(11) NOT NULL auto_increment, username varchar(32) NOT NULL, password varchar(32) NOT NULL, online int(20) NOT NULL default 0, email varchar(100) NOT NULL, active int(1) NOT NULL default 0, rtime int(20) NOT NULL default 0, PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

answered Jul 9, 2016 at 12:57

Tanmay Majumder

3721 gold badge4 silver badges18 bronze badges

Comments

DirtyBit · Accepted Answer · 2016-07-09 13:01:40Z

You are using different types of quotes together ' with ‘

Replace this:

CREATE TABLE IF NOT EXISTS `users`
(
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL,
  `online` int(20) NOT NULL default ‘0',
  `email` varchar(100) NOT NULL,
  `active` int(1) NOT NULL default ‘0',
  `rtime` int(20) NOT NULL default ‘0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

INSERT INTO `users` (`id`, `username`, `password`, `online`, `email`, `active`, `rtime`) 
VALUES (1, ‘testing’, ‘testing’, 0, ‘[email protected]’, 0, 0);

With this:

CREATE TABLE IF NOT EXISTS `users`
(
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL,
  `online` int(20) NOT NULL default '0',
  `email` varchar(100) NOT NULL,
  `active` int(1) NOT NULL default '0',
  `rtime` int(20) NOT NULL default '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;



INSERT INTO `users` (`id`, `username`, `password`, `online`, `email`, `active`, `rtime`) 
VALUES (1, 'testing', 'testing', 0, '[email protected]', 0, 0);

train_fox · Accepted Answer · 2016-07-09 13:07:15Z

0

Try this:

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `username` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL,
  `online` int(20) NOT NULL DEFAULT '0',
  `email` varchar(100) NOT NULL,
  `active` int(1) NOT NULL DEFAULT '0',
  `rtime` int(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

And:

INSERT INTO users (id, username, password, online, email, active, rtime) VALUES (1, 'testing', 'testing', 0, '[email protected]', 0, 0);

answered Jul 9, 2016 at 13:07

train_fox

1,5371 gold badge12 silver badges32 bronze badges

Comments

Saibal Roy · Accepted Answer · 2016-07-09 17:03:14Z

0

Sql Executed

Please correctly select the Database first:

Copy the below given SQL in the SQL editor and execute it. Hope this solves your problem. Thanks.

CREATE TABLE IF NOT EXISTS `users`
(
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL,
  `online` int(20) NOT NULL default '0',
  `email` varchar(100) NOT NULL,
  `active` int(1) NOT NULL default '0',
  `rtime` int(20) NOT NULL default '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

INSERT INTO `users` (`id`, `username`, `password`, `online`, `email`, `active`, `rtime`) 
VALUES (1, 'testing', 'testing', 0, '[email protected]', 0, 0);

answered Jul 9, 2016 at 17:03

Saibal Roy

4412 silver badges5 bronze badges

Comments

Arulkumar · Accepted Answer · 2016-07-11 07:11:07Z

No need of any quote around the default value zero
You need to replace the single quote ' instead of the ‘, ’ quotes in the INSERT query.

The working code will be

CREATE TABLE IF NOT EXISTS `users` (
  `id`       int(11)      NOT NULL auto_increment,
  `username` varchar(32)  NOT NULL, 
  `password` varchar(32)  NOT NULL, 
  `online`   int(20)      NOT NULL default 0, -- here
  `email`    varchar(100) NOT NULL, 
  `active`   int(1)       NOT NULL default 0, -- here
  `rtime`    int(20)      NOT NULL default 0, -- here
  PRIMARY KEY (`id`) 
) ENGINE = MyISAM DEFAULT CHARSET=utf8; 

INSERT INTO `users` (`id`, `username`, `password`, `online`, `email`, `active`, `rtime`) 
VALUES (1, 'testing', 'testing', 0, '[email protected]', 0, 0); -- here

SQL Fiddle DEMO: http://sqlfiddle.com/#!9/286728/1

Collectives™ on Stack Overflow

SQL error while executing the query

6 Answers 6

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related