Type is likely a reserved word, like date, insert, int, etc. . . . . you should really rename that column, but if you must, do this.

INSERT INTO `type` (`type`) VALUES ('Muay Thai'),

Note the backticks on the field name, and how it worked on your create only because you did that. :-)

Ah I see what you mean, thanks very much.

I still can't get this to work, here's the script I'm using.

INSERT INTO `manufacturer` (`manufacturer_name`) VALUES ('Alfa Romeo'),
INSERT INTO `manufacturer` (`manufacturer_name`) VALUES ('Audi'),
INSERT INTO `manufacturer` (`manufacturer_name`) VALUES ('BMW'),
INSERT INTO `manufacturer` (`manufacturer_name`) VALUES ('Chevrolet'),
INSERT INTO `manufacturer` (`manufacturer_name`) VALUES ('Chrysler'),
INSERT INTO `manufacturer` (`manufacturer_name`) VALUES ('Citoen'),
INSERT INTO `manufacturer` (`manufacturer_name`) VALUES ('Daewoo'),
INSERT INTO `manufacturer` (`manufacturer_name`) VALUES ('Daihatsu');

This is with a different database, the create table script works, just not able to enter any data.

Please help cos I don't want to type all this in by hand :(

What's the error?

Also, I don't know much about MySQL, but all the other DBs I use require a semicolon between statements.

What if you try just 1 INSET statement (instead of all at once)?

I got a generic error, not very clever! Just said I have a syntax error but didn't give any specifics. I've been looking on the net and found where I'm going wrong. You only need to specift INSERT INTO once, not every line. Here's the insert script I've just used and works perfectly.

INSERT INTO `manufacturer` (`manufacturer_name`) VALUES ('Alfa Romeo'), ('Audi'), ('BMW'), ('Chevrolet'), ('Chrysler'), ('Citoen'), ('Daewoo'), ('Daihatsu'), ('Fiat'), ('Ford'), ('Honda'), ('Hyundai'), ('Isuzu'), ('Jaguar'), ('Jeep'), ('Kia'), ('Lancia'), ('Land Rover'), ('Lexus'), ('Lotus'), ('Mazda'), ('Mercesdes Benz'), ('MG'), ('Mini'), ('Mitsubishi'), ('Nissan'), ('Peugeot'), ('Renault'), ('Rover'), ('Saab'), ('Seat'), ('Skoda'), ('Smart'), ('SsangYong'), ('Subaru'), ('Suzuki'), ('Toyota'), ('TVR'), ('Vauxhall'), ('Volkswagen'), ('Volvo');

OK, 5 minutes later, on the next step and I'm stuck again. Damn...

I'm not trying to add data to the "model" database using the following:

INSERT INTO 'model' ('manufacturer_id', 'model_name') VALUES ('1', '145'), ('1', '146'), ('1', '147'), ('1', '155'), ('1', '156'), ('1', '159'), ('1', '164'), ('1', '166'), ('1', '33'), ('1', '75'), ('1', '90'), ('1', 'Sprint'), ('1', 'Arna'), ('1', 'Brera'), ('1', 'Giulietta'), ('1', 'GT'), ('1', 'GTV'), ('1', 'Mito'), ('1', 'Spider'), ('1', 'SZ');

I can't see where the problem is, I'm getting failed : You have an error in your SQL syntax; errror message. Once I've got the above code working, I'll be able to do all the other manufacturers.

Thanks

Right, you can do multiple inserts in one statement like your second example (almost) but not the first (I think.)

I tihnk I see the error. Are those quotes or backticks?

INSERT INTO 'model' ('manufacturer_id', 'model_name')

Backtick object references (tables, fields), quote values - but notable to mention, it's a "good habit" *not* to quote integer fields. The reason is, if you have this

$myid=null;

insert into table (some_integer_field) values('$myid');

Which gives you

insert into table (some_integer_field) values('');

It will execute, but you will spend a lot of time trying to figure out why it's not inserting (this is one of the more common questions.) But if you do this for integer fields only,

insert into table (some_integer_field) values($myid);

It will give you

insert into table (some_integer_field) values();

Which will kick a mysql error, and alert you to the problem easier. This is especially true if you're parsing input form forms and somehow mistakenly do

$myid = 'Firstname';

Being text, mysql will always evaluate it to 0 when inserting into an integer field type.

Errors are not always bad, they can be useful tools.

The first insert script worked fine, it's the second one that's entering two values that doesn't work.

They are quotes, not backticks.

Should the insert script be something like:

INSERT INTO 'model' ('manufacturer_id', 'model_name') VALUES (1, '145'), (1, '146'), (1, '147'), (1, '155');

So the smallint field doesn't have any quotes but the varchar field does.

Right, and that's the problem. :-)

INSERT INTO 'model' ('manufacturer_id', 'model_name')

should be

INSERT INTO `model` (`manufacturer_id`, `model_name`)

Backtick object references (tables, fields), quote values

Thanks rocknbill :)

Now you've pointed it out and I've looked at my create table script, it's obvious. Although in notepad the two do look quite similar. I made a start with inserting all the different models last night, was all going swimmingly but now I'm getting this error message:

SQL insert into `model` values (? , ? , ?) failed : Duplicate entry '127' for key 1

Insert Code:

INSERT INTO `model` (`manufacturer_id`, `model_name`) VALUES ('9', '126'),('9', '500'), ('9', 'Barchetta'), ('9', 'Brava'), ('9', 'Bravo'), ('9', 'Cinquecento'), ('9', 'Coupe'), ('9', 'Croma'), ('9', 'Doblo'), ('9', 'Fiorino'), ('9', 'Grand Punto'), ('9', 'Idea'), ('9', 'Marea'), ('9', 'Multipla'), ('9', 'Panda'), ('9', 'Punto'), ('9', 'Punto Evo'), ('9', 'Qubo'), ('9', 'Regata'), ('9', 'Scudo'), ('9', 'Sedici'), ('9', 'Seicento'), ('9', 'Stilo'), ('9', 'Strada'), ('9', 'Tempra'), ('9', 'Tipo'), ('9', 'Ulysse'), ('9', 'Uno');

The strange thing is, it inserted the first two rows, 126 & 500 but won't go any further. I've looked at the code over and over but can't see why it's failing.

I've emailed my host to see if they can help.

Edited: I thought it was one thing but looking at your insert it appears to be something else. What was the create statement for this table? It should be something like

create table `model` (`id` int(11) primary key auto_increment, `manufacturer_id` int(11) not null, index(`manufacturer_id`), `model_name` varchar(30) not null, index(`model_name`(4)));

I used the following to create the table

CREATE TABLE `model` (
`model_id` tinyint(4) NOT NULL auto_increment,
`manufacturer_id` tinyint(4) default NULL,
`model_name` varchar(30) default NULL,
PRIMARY KEY (`model_id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

It looks like TINYINT has a maximum value of 127. INT would be a better choice for the model_id field.

I've changed model_id and manufacturer_id to INT fields without any data loss but I'm now getting a different error:

Duplicate entry '0' for key 1

when using:

INSERT INTO `model` (`manufacturer_id`, `model_name`) VALUES ('9', 'Brava'), ('9', 'Bravo'), ('9', 'Cinquecento'), ('9', 'Coupe'), ('9', 'Croma'), ('9', 'Doblo'), ('9', 'Fiorino'), ('9', 'Grand Punto'), ('9', 'Idea'), ('9', 'Marea'), ('9', 'Multipla'), ('9', 'Panda'), ('9', 'Punto'), ('9', 'Punto Evo'), ('9', 'Qubo'), ('9', 'Regata'), ('9', 'Scudo'), ('9', 'Sedici'), ('9', 'Seicento'), ('9', 'Stilo'), ('9', 'Strada'), ('9', 'Tempra'), ('9', 'Tipo'), ('9', 'Ulysse'), ('9', 'Uno');

Even though I get an error, it is putting the first value in but without the auto increment value. So I get model_id = 0, manufacturer_id = 9 and model_name = Brava.

However, I can add the data manually.

Have I stuffed it up by changing the type? If so I'll have to start again.

How did you change the type? It sounds like it may have changed it to INT without auto_increment.

The site is hosted on CentOS (I think) and uses Virtualmin & Webmin. There's section in the there for MySQL and I changed it using that.

Just checked and your right, it's lost it's auto-increment value. If I set this to true I highly doubt it will carry on where I left off, which means starting again! Glad I didn't enter all the data else I'd be right miffed.

Just what I thought:

Failed to save field : SQL alter table `model` modify `model_id` int(4) not null default NULL auto_increment failed : Duplicate entry '128' for key 1

Right, you're going to want to have duplicates for model id, Furthermore you're inserting into a field that's intended for auto increment so you will likely get unexpected results.

ford=6
focus (mfg 6)
explorer (mfg 6)
etc....

So you will need another auto increment field for the purpose of a unique id. something like

CREATE TABLE `model` (
`id` int(11) primary key auto_increment,
`model_id` tinyint(4) NOT NULL,
`manufacturer_id` tinyint(4) default NULL,
`model_name` varchar(30) default NULL,
) TYPE=MyISAM AUTO_INCREMENT=1;

This will allow you to keep tinyint for model_id and also allow duplicate values for that field, which is likely what you want.

remove the primary key with the id's. Maybe the error is found on your duplicate entry.

Thanks for your help, I've got it working now.

Will bookmark this to refer back to if I get any future problems :)