[RESOLVED] I can't believe this cannot be done (within reason) MySQL Import issue

[dmacman1962]

Hi Everyone,

To start off, I have a co-worker who is an Access and Vbscripting fan, and is constantly questioning (doubting) that PHP and MySQL is a good combo.

Now, I have a test table import I had him setup for me to import into MySQL.

I have my table 'list' with 6 fields.

ID (autoincrement) - Video - State - Grade - Class - Description

And he gave me the first few records, so I could ensure the import will go OK.

INSERT INTO list VALUES (314, 1, 39, 1, 1, '6.1.3.A- AAAA.');
INSERT INTO list VALUES (315, 1, 39, 1, 1, '6.1.3.B- BBBB');

The problem is his imports are for 50 states and will be thousands of records. The ONLY way I can import it is if he/I set the autoincrement values in advance! (the first value in these samples are the autoincrement IDS - 314 315)

Not to sound lame, but isn't that why its called 'autoincrement'?

I thought I could set the import files up like such...

INSERT INTO list VALUES (1, 39, 1, 1, '6.1.3.A- AAAAA.');
INSERT INTO list VALUES (1, 39, 1, 1, '6.1.3.B- BBBBBB');

...and it would assign the ID's 314, 315, etc.

I have tried demo apps like Navicat, etc, and had little results.

I have seen references to command line scripts in MySQL and writting a PHP solution, but you would think that would not be needed.

I would REALLY appreciate some help here, and to prove the PHP/MySQL combo is a worthy one. I have staked my 'reputation' on it. 🙂

Thanks,
Don

[cgraz]

dmacman1962 wrote:

The ONLY way I can import it is if he/I set the autoincrement values in advance! (the first value in these samples are the autoincrement IDS - 314 315)

That's not true. Use the full INSERT syntax

By doing this:

insert into <table> values('v1', 'v2', 'v3')

you are inserting based on the order of the columns in your table. So v1 would go to your primary key (assuming that it is the first field), and v2 + v3 would go to your second and third field respectively.

You could avoid this by doing:

insert into <table> (Video, State, Grade, Class, Description) VALUES(1, 39, 1, 1, 'description here');

This way, you specify which fields you are insert into. MySQL will populate the auto_increment field for you, because, as you said, that's why it's called an auto_increment 😃

[dmacman1962]

Hi cgraz,

That worked. Thanks for the input.

With a minimal amount of 'messing around' I got it to import and autoincrement.

Owe !

Don