homepage Welcome to WebmasterWorld Guest from 54.204.94.254
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Insert script failing
LinusIT



 
Msg#: 4251919 posted 3:52 pm on Jan 11, 2011 (gmt 0)

I am trying to enter data into a table via Webmin (CentOS) MySQL section but it keeps failing.

Here's the code to create the table (This works)

CREATE TABLE `type` (
`id` tinyint(4) NOT NULL auto_increment,
`type` varchar(20) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;


And here's the code for inserting the data (Doesn't work)

INSERT INTO `type` (type) VALUES ('Muay Thai'),
INSERT INTO `type` (type) VALUES ('Srivichai'),
INSERT INTO `type` (type) VALUES ('MMA'),
INSERT INTO `type` (type) VALUES ('K1'),
INSERT INTO `type` (type) VALUES ('Kickboxing Trousers');


I have tried all different variations and searching the net but to no avail. I'm sure it's something simple I'm doing wrong.

 

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4251919 posted 5:05 pm on Jan 17, 2011 (gmt 0)

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. :-)

LinusIT



 
Msg#: 4251919 posted 5:14 pm on Jan 17, 2011 (gmt 0)

Ah I see what you mean, thanks very much.

LinusIT



 
Msg#: 4251919 posted 9:43 am on Jan 27, 2011 (gmt 0)

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 :(

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4251919 posted 4:42 pm on Jan 27, 2011 (gmt 0)

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)?

LinusIT



 
Msg#: 4251919 posted 4:57 pm on Jan 27, 2011 (gmt 0)

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');

LinusIT



 
Msg#: 4251919 posted 5:10 pm on Jan 27, 2011 (gmt 0)

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

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4251919 posted 5:32 pm on Jan 27, 2011 (gmt 0)

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.

LinusIT



 
Msg#: 4251919 posted 5:41 pm on Jan 27, 2011 (gmt 0)

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.

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4251919 posted 5:47 pm on Jan 27, 2011 (gmt 0)

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

LinusIT



 
Msg#: 4251919 posted 9:37 am on Jan 28, 2011 (gmt 0)

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.

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4251919 posted 5:02 pm on Jan 28, 2011 (gmt 0)

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)));

LinusIT



 
Msg#: 4251919 posted 5:21 pm on Jan 28, 2011 (gmt 0)

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 ;

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4251919 posted 5:24 pm on Jan 28, 2011 (gmt 0)

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

LinusIT



 
Msg#: 4251919 posted 5:44 pm on Jan 28, 2011 (gmt 0)

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.

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4251919 posted 5:49 pm on Jan 28, 2011 (gmt 0)

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

LinusIT



 
Msg#: 4251919 posted 6:06 pm on Jan 28, 2011 (gmt 0)

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.

LinusIT



 
Msg#: 4251919 posted 6:07 pm on Jan 28, 2011 (gmt 0)

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

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4251919 posted 4:31 pm on Jan 31, 2011 (gmt 0)

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.

jalicia18



 
Msg#: 4251919 posted 9:42 am on Feb 4, 2011 (gmt 0)

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

LinusIT



 
Msg#: 4251919 posted 11:05 am on Feb 4, 2011 (gmt 0)

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

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved