Import Error: You have an error in your SQL syntax;

Question

I'm having issues with my jquery code. I'm new to jquery so I'm not really sure where the problem lies.

-- phpMiniAdmin dump 1.9.150917
-- Datetime: 2019-10-31 23:14:13
-- Host:

--/*!40030 SET NAMES utf8 */;
--/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

DROP TABLE IF EXISTS `Hunter`;
CREATE TABLE `Hunter` (
  `Hunterid` char(3) NOT NULL,
  `Huntername` varchar(25) NOT NULL,
  `HunterWeapon` varchar(25) NOT NULL,
  `HunterSemblance` varchar(25) NOT NULL,
  PRIMARY KEY (`Hunterid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40000 ALTER TABLE `Hunter` DISABLE KEYS */;
INSERT INTO `Hunter`(Hunterid, Huntername, HunterWeapon, HunterSemblance) VALUES (333,'Ruby Rose','Crescent Rose', 'Speed'),(111,'Weiss Schnee','Myrtenaster', 'Glyphs'), (222,'Yang Xiao Long','Ember Celica', 'Deflection');
/*!40000 ALTER TABLE `Hunter` ENABLE KEYS */;

DROP TABLE IF EXISTS `Training Academy`;
CREATE TABLE `Training Academy` (
  `Training Academyid` char(7) NOT NULL,
  `Training Academyname` varchar(15) NOT NULL,
  `Training Academycountry` varchar(15) NOT NULL,
  PRIMARY KEY (`Training Academyid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40000 ALTER TABLE `Training Academy` DISABLE KEYS */;
INSERT INTO `Training Academy` (Training Academyid, Training Academyname, Training Academycountry) VALUES ('1111111','Beacon','Vale'),('2222222','Haven','Mistral'),('3333333','Shade','Vacuo');
/*!40000 ALTER TABLE `Training Academy` ENABLE KEYS */;

DROP TABLE IF EXISTS `Head Master`;
CREATE TABLE `Head Master` (
  `Head Masterid` char(3) NOT NULL,
  `Head Mastername` varchar(25) NOT NULL,
  `Training Academyid` char(7) NOT NULL,
  PRIMARY KEY (`Head Masterid`),
  KEY `Training Academyid` (`Training Academyid`),
  CONSTRAINT `Head Master_ibfk_2` FOREIGN KEY (`Training Academyid`) REFERENCES `Training Academyid` (`Training Academyid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40000 ALTER TABLE `Head Master` DISABLE KEYS */;
INSERT INTO `Head Master` (Head Masterid,Head Mastername) VALUES (777,'Ozpin'), (888,'Lionheart'), (444,'Ironwood');
/*!40000 ALTER TABLE `Head Master` ENABLE KEYS */;

DROP TABLE IF EXISTS `Villian Team`;
CREATE TABLE `Villian Team` (
  `Villian Teamid` char(4) NOT NULL,
  `Villian Teamname` varchar(25) NOT NULL,
  PRIMARY KEY (`Villian Teamid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40000 ALTER TABLE `Villian Team` DISABLE KEYS */;
INSERT INTO `Villian Team` (Villian Teamid, Villian Teamname) VALUES (7979,'White Fang'),(1452,'Cinders Team');
/*!40000 ALTER TABLE `Villian Team` ENABLE KEYS */;

DROP TABLE IF EXISTS `Hunter Team`;
CREATE TABLE `Hunter Team` (
  `Hunter Teamname` char(4) NOT NULL,
  `Hunter Teamid` char(5) NOT NULL,
  `Training Academyid` char(7) NOT NULL,
  PRIMARY KEY (`Hunter Teamid`),
  KEY `Training Academyid` (`Training Academyid`),
  CONSTRAINT `Hunter Team_ibfk_1` FOREIGN KEY (`Training Academyid`) REFERENCES `Training Academy` (`Training Academyid`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40000 ALTER TABLE `Hunter Team` DISABLE KEYS */;
INSERT INTO `Hunter Team` (Hunter Teamname, Hunter Teamid) VALUES (51515,'RWBY'), (78787,'JNPR'), (47155,'STRQ');
/*!40000 ALTER TABLE `Hunter Team` ENABLE KEYS */;

You pasted a bunch of code here, but did nothing to try to explain to us where/what the problem might be. — Tim Biegeleisen, Nov 01 '19 at 00:38
What does this have to do with jQuery? That's a JavaScript library, it has nothing to do with MySQL or databases. — Barmar, Nov 01 '19 at 00:39
If you get an error trying to load a file that was created with `mysqldump`, it's usually because you're trying to load into an incompatible version of MySQL than the one where it was created. — Barmar, Nov 01 '19 at 00:40

score 0 · Answer 1 · answered Nov 01 '19 at 00:43

Lines like this are the problem:

INSERT INTO `Training Academy` (Training Academyid, Training Academyname, Training Academycountry) VALUES ('1111111','Beacon','Vale'),('2222222','Haven','Mistral'),('3333333','Shade','Vacuo');

You have column names with spaces in them, you need to put backticks around them.

INSERT INTO `Training Academy` (`Training Academyid`, `Training Academyname`, `Training Academycountry`) VALUES ('1111111','Beacon','Vale'),('2222222','Haven','Mistral'),('3333333','Shade','Vacuo');

You have the same problem when inserting into Head Master, Villain Team, and Hunter Team.

For more information, see When to use single quotes, double quotes, and backticks in MySQL

If this was created using a database export command from phpMiniAdmin, I suggest you throw it out -- this is an unforgiveable bug. phpMyAdmin is more popular and probably less buggy.

score 0 · Answer 2 · answered Nov 01 '19 at 00:54

I found 3 issues here.

Missing backticks on column names.

Missing default values on foreign keys, so it failed during insert if not supplied

You've interchanged the column, so this line will fail.

INSERT INTO `Hunter Team` (`Hunter Teamname`, `Hunter Teamid`) VALUES (51515,'RWBY'), (78787,'JNPR'), (47155,'STRQ');

Here's your complete working script.

DROP TABLE IF EXISTS `Hunter`;
CREATE TABLE `Hunter` (
  `Hunterid` char(3) NOT NULL,
  `Huntername` varchar(25) NOT NULL,
  `HunterWeapon` varchar(25) NOT NULL,
  `HunterSemblance` varchar(25) NOT NULL,
  PRIMARY KEY (`Hunterid`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `Hunter`(Hunterid, Huntername, HunterWeapon, HunterSemblance) VALUES (333,'Ruby Rose','Crescent Rose', 'Speed'),(111,'Weiss Schnee','Myrtenaster', 'Glyphs'), (222,'Yang Xiao Long','Ember Celica', 'Deflection');


DROP TABLE IF EXISTS `Training Academy`;
CREATE TABLE `Training Academy` (
  `Training Academyid` char(7) NOT NULL DEFAULT '1111111',
  `Training Academyname` varchar(15) NOT NULL,
  `Training Academycountry` varchar(15) NOT NULL,
  PRIMARY KEY (`Training Academyid`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `Training Academy` (`Training Academyid`, `Training Academyname`, `Training Academycountry`) VALUES ('1111111','Beacon','Vale'),('2222222','Haven','Mistral'),('3333333','Shade','Vacuo');


DROP TABLE IF EXISTS `Head Master`;
CREATE TABLE `Head Master` (
  `Head Masterid` char(3) NOT NULL,
  `Head Mastername` varchar(25) NOT NULL,
  `Training Academyid` char(7) NOT NULL DEFAULT '1111111',
  PRIMARY KEY (`Head Masterid`),
  KEY `Training Academyid` (`Training Academyid`),
  CONSTRAINT `Head Master_ibfk_2` FOREIGN KEY (`Training Academyid`) REFERENCES `Training Academy` (`Training Academyid`)
);


INSERT INTO `Head Master` (`Head Masterid`,`Head Mastername`) VALUES (777,'Ozpin'), (888,'Lionheart'), (444,'Ironwood');


DROP TABLE IF EXISTS `Villian Team`;
CREATE TABLE `Villian Team` (
  `Villian Teamid` char(4) NOT NULL,
  `Villian Teamname` varchar(25) NOT NULL,
  PRIMARY KEY (`Villian Teamid`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `Villian Team` (`Villian Teamid`, `Villian Teamname`) VALUES (7979,'White Fang'),(1452,'Cinders Team');

DROP TABLE IF EXISTS `Hunter Team`;
CREATE TABLE `Hunter Team` (
  `Hunter Teamname` char(4) NOT NULL,
  `Hunter Teamid` char(5) NOT NULL,
  `Training Academyid` char(7) NOT NULL DEFAULT '1111111',
  PRIMARY KEY (`Hunter Teamid`),
  KEY `Training Academyid` (`Training Academyid`),
  CONSTRAINT `Hunter Team_ibfk_1` FOREIGN KEY (`Training Academyid`) REFERENCES `Training Academy` (`Training Academyid`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `Hunter Team` (`Hunter Teamid`,`Hunter Teamname`) VALUES (51515,'RWBY'), (78787,'JNPR'), (47155,'STRQ');

Import Error: You have an error in your SQL syntax;

2 Answers2