CODEWITHSUNDEEP
mysqlsql
down321
down321

Reputation: 39

what's wrong with the mysql command?

when i run the following command in phpmyadmin.

CREATE TABLE subscribers (
  'subscriber_id' int(11) NOT NULL auto_increment,
  'customers_id' int(11) default NULL,
  'email_address' varchar(96) NOT NULL default '',
  'email_format' varchar(4) NOT NULL default 'TEXT',
  PRIMARY KEY  ('subscriber_id')
) TYPE=MyISAM;

it shows me #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 ''subscriber_id' int(11) NOT NULL auto_increment, 'customers_id' int(11) defaul' at line 2

 INSERT INTO query_builder (query_id, query_category, query_name, query_description, query_string, query_keys_list) VALUES (6, 'email,newsletters', 'All Newsletter Subscribers', 'Returns name and email address of all Customer Account subscribers and all Newsletter-Only subscribers.', 'select c.customers_firstname, c.customers_lastname, s.email_address as customers_email_address from TABLE_SUBSCRIBERS as s left join TABLE_CUSTOMERS as c on c.customers_id = s.customers_id ', '')

it shows : 1062 - Duplicate entry '6' for key 1

i fell the command is right. i don't know how to correct it? thank you.

Upvotes: 1

Views: 1093

Answers (3)

Matt Healy
Matt Healy

Reputation: 18531

Just removing the quotes around the column name, and changing the last bit to Engine=MyISAM worked for me:

CREATE TABLE subscribers (
  subscriber_id int(11) NOT NULL auto_increment,
  customers_id int(11) default NULL,
  email_address varchar(96) NOT NULL default '',
  email_format varchar(4) NOT NULL default 'TEXT',
  PRIMARY KEY  (subscriber_id)
) Engine=MyISAM;

Upvotes: 0

Ivaylo Petrov
Ivaylo Petrov

Reputation: 1172

You should not use strings as column names. Also it is ENGINE not TYPE. It should look like:

CREATE TABLE subscribers (
  subscriber_id int(11) NOT NULL auto_increment,
  customers_id int(11) default NULL,
  email_address varchar(96) NOT NULL default '',
  email_format varchar(4) NOT NULL default 'TEXT',
  PRIMARY KEY  (subscriber_id)
) ENGINE=MyISAM;

Upvotes: 2

Shakti Singh
Shakti Singh

Reputation: 86476

You don't need the single quote around the column name. Remove that.

This should be 

CREATE TABLE subscribers (
  subscriber_id int(11) NOT NULL auto_increment,
  customers_id int(11) default NULL,
  email_address varchar(96) NOT NULL default '',
  email_format varchar(4) NOT NULL default 'TEXT',
  PRIMARY KEY  (subscriber_id)
) TYPE=MyISAM;

Although, You can enclose the column name in backticks if that is conflicting with the mysql reserved words.

Upvotes: 5

Related Questions