Syntax error

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Xelmepa
Forum Commoner
Posts: 41
Joined: Sat Aug 24, 2002 3:02 pm
Location: Athens, Greece
Contact:

Syntax error

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you sent all those inserts at the same time through mysql_query ? it doesn't support multiple command queries.
User avatar
Xelmepa
Forum Commoner
Posts: 41
Joined: Sat Aug 24, 2002 3:02 pm
Location: Athens, Greece
Contact:

Post 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...
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

explode

Post 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.
redmonkey
Forum Regular
Posts: 836
Joined: Thu Dec 18, 2003 3:58 pm

Post 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).
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I don't think he has access to the command line on the server.
redmonkey
Forum Regular
Posts: 836
Joined: Thu Dec 18, 2003 3:58 pm

Post by redmonkey »

Good point fyed, but could always (at least attempt to) use one of the exec() style funcs to access the commandline utils.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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'), ...
Post Reply