sql syntax error while executing my query

Question

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 'CREATE TABLE IF NOT EXISTS administracia( userID INT(255) NOT NULL AUTO_INCREME' at line 12

This is the query more or less also more readable version

$data = "
CREATE TABLE IF NOT EXISTS zamestnanci(
userID INT(255) NOT NULL AUTO_INCREMENT,
osobnecislo VARCHAR(50) NOT NULL,
meno VARCHAR(50) NOT NULL,
priezvisko VARCHAR(50) NOT NULL,
nick VARCHAR(50) NOT NULL,
heslo CHAR(128) NOT NULL,
previerka VARCHAR(15) NOT NULL,
PRIMARY KEY (userID,nick)) 
ENGINE=INNODB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS administracia(
userID INT(255) NOT NULL AUTO_INCREMENT,
osobnecislo VARCHAR(50) NOT NULL,
meno VARCHAR(50) NOT NULL,
priezvisko VARCHAR(50) NOT NULL,
nick VARCHAR(50) NOT NULL,
heslo CHAR(128) NOT NULL,
previerka VARCHAR(15) NOT NULL,
PRIMARY KEY (userID,nick)) 
ENGINE=INNODB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS navrhy(
navrhID INT(255) NOT NULL AUTO_INCREMENT,
nazov VARCHAR(50) NOT NULL,
text VARCHAR(505) NOT NULL,
schvalenie VARCHAR(15) NOT NULL,
datum VARCHAR(50)  NOT NULL,
pridal VARCHAR(50) NOT NULL,
hlasovali VARCHAR(8000) NOT NULL,
kategoria VARCHAR(50) NOT NULL,
stroj VARCHAR(50) NOT NULL,
ano VARCHAR(1000) NOT NULL,
nie VARCHAR(1000) NOT NULL default '0',
PRIMARY KEY (navrhID)) 
ENGINE=INNODB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS problemovyartikel(
artikelID INT(255) NOT NULL AUTO_INCREMENT,
artikelcisloVARCHAR(50) NOT NULL,
text VARCHAR(255) NOT NULL,
datum VARCHAR(50) NOT NULL,
pridal tinytext NOT NULL,
problem_cislo VARCHAR(100) NOT NULL,
PRIMARY KEY (artikelID)) 
ENGINE=INNODB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS kategorie(
kategoriaID INT(255) NOT NULL AUTO_INCREMENT,
Nkategoria VARCHAR(50) NOT NULL,
Pkategoria VARCHAR(50) NOT NULL,
PRIMARY KEY (kategoriaID)) 
ENGINE=INNODB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS novinky(
novinkaID INT(255) NOT NULL AUTO_INCREMENT,
novinka mediumtext NOT NULL,
PRIMARY KEY (novinkaID)) 
ENGINE=INNODB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS stroje(
strojID INT(255) NOT NULL AUTO_INCREMENT,
stroj text NOT NULL,
PRIMARY KEY (strojID)) 
ENGINE=INNODB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS kontakt(
kontaktID INT(255) NOT NULL AUTO_INCREMENT,
pole1 text NOT NULL,
pole2 text NOT NULL,
PRIMARY KEY (kontaktID)) 
ENGINE=INNODB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS problem_cislo(
problem_cisloID INT(255) NOT NULL AUTO_INCREMENT,
problem_cislo VARCHAR(100) NOT NULL,
PRIMARY KEY (problem_cisloID)) 
ENGINE=INNODB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS stranky(
strankaid INT(255) NOT NULL AUTO_INCREMENT,
stranka mediumtext NOT NULL,
stranka_obsah longtext NOT NULL,
subor mediumtext NOT NULL,
PRIMARY KEY (strankaid)) 
ENGINE=INNODB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS chyby(
strankaid INT(255) NOT NULL AUTO_INCREMENT,
stranka CHAR(128) NOT NULL,
stranka_obsah VARCHAR(15) NOT NULL,
PRIMARY KEY (strankaid)) 
ENGINE=INNODB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1;

INSERT INTO administracia (userID,osobnecislo,meno,priezvisko,nick,heslo,previerka) VALUES('1','admin','admin','admin','admin','59db9e61d015935901442385d992dd34346dbe3439174fee700b56f27ad445ee42e052c0c014892a70cea63b912c7a3ee55c7c42e9e36e0f169ab7e6b886fd21','admin');
INSERT INTO kategorie (kategoriaID,Nkategoria) VALUES('1','kategoria1');
INSERT INTO novinky (novinkaID,novinka) VALUES('1','novinka1<br>novinka2');
INSERT INTO stroje (strojID,stroj) VALUES('1','stroj1');
INSERT INTO kontakt (kontaktID,pole1,pole2) VALUES('1','testificate','0912 345 678');
INSERT INTO problem_cislo (problem_cisloID,problem_cislo) VALUES('1','problem1');
INSERT INTO chyby (strankaid,stranka_obsah,stranka) VALUES('1','Registrácia prebehla úspešne!','úspech'),('2','Návrh bol úspešne pridaný!','úspech'),('3','Problémový artikel bol úspešne pridaný!','úspech'),('4','Aktivácia prebehne čo najskôr alebo ste zadali nesprávne údaje!,'totok')";

Edit:

Using the following function to split and execute the queries:

$queries = preg_split("/;+(?=([^'|^\\\']*['|\\\'][^'|^\\\']*['|\\\'])*[^'|^\\\']*[^'|^\\\']$)/",$data); 
foreach ($queries as $query) 
  if(strlen(trim($query)) > 0) 
     mysql_query($query); { 
     $spracovat = mysql_query($data,$prepojenie) or die(mysql_error());

I know it's not an answer to your question :-) but you shouldn't be needing to do this in PHP... — Ben
– Ben, Commented Aug 19, 2012 at 22:43

Jocelyn · Accepted Answer · 2012-08-19 22:47:11Z

6

You appear to be stuffing this whole thing into a PHP variable. Most PHP MySQL APIs will only accept one statement per execution, so you cannot combine all these into a single statement unless you are using something like MySQLi::multi_query().

Instead of packing all of these into a single variable and attempting to parse out individual queries with preg_split(), I recommend creating an array of queries and looping over it to execute them all. That makes for much cleaner and less error-prone code.

$queries = array(

     'CREATE TABLE IF NOT EXISTS zamestnanci(
    userID INT(255) NOT NULL AUTO_INCREMENT,
    osobnecislo VARCHAR(50) NOT NULL,
    meno VARCHAR(50) NOT NULL,
    priezvisko VARCHAR(50) NOT NULL,
    nick VARCHAR(50) NOT NULL,
    heslo CHAR(128) NOT NULL,
    previerka VARCHAR(15) NOT NULL,
    PRIMARY KEY (userID,nick)) 
    ENGINE=INNODB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1;',

    'CREATE TABLE IF NOT EXISTS administracia(
    userID INT(255) NOT NULL AUTO_INCREMENT,
    osobnecislo VARCHAR(50) NOT NULL,
    meno VARCHAR(50) NOT NULL,
    priezvisko VARCHAR(50) NOT NULL,
    nick VARCHAR(50) NOT NULL,
    heslo CHAR(128) NOT NULL,
    previerka VARCHAR(15) NOT NULL,
    PRIMARY KEY (userID,nick)) 
    ENGINE=INNODB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1;',
    ...
);

foreach ($queries as $query) {
   // Execute the query, test for errors, etc.
}

edited Aug 19, 2012 at 22:47

Jocelyn

11.4k11 gold badges47 silver badges63 bronze badges

answered Aug 19, 2012 at 22:31

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

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

9 Comments

user1176253 Over a year ago

I have a function that treats every query as single.

Dan Grossman Over a year ago

It doesn't appear your function is working. Notice how the error appears at the first character of the second query. You would never get an error there in any other case.

user1176253 Over a year ago

You may be correct on this version, but in my previous version link the function worked. I removed all the `` and added some new lines and it stopped working...

Michael Berkowski Over a year ago

@user1176253 That is not syntactically valid PHP. What is the spurious { after the first mysql_query() call?

nickb Over a year ago

There's also a mysql_query( $data); in it, which means you are attempting to execute all of these queries with one call to mysql_query().

|

Dan Grossman · Accepted Answer · 2012-08-19 22:31:20Z

4

You are likely issuing this with mysql_query, which can only send one query, not a string containing 18.

answered Aug 19, 2012 at 22:31

Dan Grossman

52.5k10 gold badges115 silver badges101 bronze badges

Collectives™ on Stack Overflow

sql syntax error while executing my query

Edit:

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Edit:

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest