Page 1 of 1

Syntax error

Posted: Wed Sep 29, 2004 9:24 am
by Xelmepa
Hi,

I am having a syntax error and I really don't understand why I am getting it.

I have to make a copy of a table in a different webhost who unfortunately doesn't have phpmyadmin or anything similar, just mySQL support.

Anyway, I created my table using this code:

Code: Select all

CREATE TABLE `releases` (
  `key` int(4) NOT NULL auto_increment,
  `artist` varchar(64) NOT NULL default '',
  `album` varchar(128) NOT NULL default '',
  `price` varchar(6) NOT NULL default '',
  `relnum` varchar(10) NOT NULL default '',
  `notes` varchar(32) NOT NULL default '',
  `format` varchar(48) NOT NULL default '',
  `pid` varchar(4) NOT NULL default '',
  PRIMARY KEY  (`key`)
) TYPE=MyISAM ;
and then I tried to insert the rows using:

Code: Select all

INSERT INTO `releases` VALUES ('', 'NAER MATARON', 'Up From The Ashes', '0', 'BLR/CD003', 'sold out', 'CD' , '');
INSERT INTO `releases` VALUES ('', 'HORRIFIED', 'Animal', '10', 'BLR/CD004', '', 'Digipack' , '1');
INSERT INTO `releases` VALUES ('', 'DESCEND', 'Beyond The Realm Of Throes', '7', 'BLR/CD005', '', 'CD' , '2');
INSERT INTO `releases` VALUES ('', 'AFTERMATH', 'Eyes of tomorrow', '10', 'BLR/CD006', 're-release', 'CD' , '3');
...
...
which gave me the following error:
You have an error in your SQL syntax near 'INSERT INTO `releases` VALUES ('', 'HORRIFIED', 'Animal', '10', 'BLR/CD004', '',' at line 3

Am I not seeing something so obvious?

Any help appreciated.
Thanks in advance.

Posted: Wed Sep 29, 2004 9:39 am
by feyd
you sent all those inserts at the same time through mysql_query ? it doesn't support multiple command queries.

Posted: Wed Sep 29, 2004 9:46 am
by Xelmepa
Any way I could do it?
I have like 70 rows and I don't really feel like insterting them one by one...

explode

Posted: Wed Sep 29, 2004 9:53 am
by phpScott
you could write a little php script that will explode() on the ; and then for each element in the new array just run as a mysql_query it as it should contain the full insert;
That is providing of course that the ; isn't used in any of the values as that would really mess thing up.

Posted: Wed Sep 29, 2004 10:06 am
by redmonkey
Why not just use mysqldump to create the SQL dumpfile then use mysql commandline to dump that file straight into your new servers DB.

Using the commandline has always been my preferred method and consistantly proved to be far more reliable (in my experience anyway).

Posted: Wed Sep 29, 2004 10:08 am
by feyd
I don't think he has access to the command line on the server.

Posted: Wed Sep 29, 2004 10:10 am
by redmonkey
Good point fyed, but could always (at least attempt to) use one of the exec() style funcs to access the commandline utils.

Posted: Wed Sep 29, 2004 10:16 am
by feyd
your version of mysql may support the multiple insert trick:

Code: Select all

INSERT INTO tablename VALUES('new row 1'), VALUES('new row 2'), ...