PHP/MySQL to insert data into DB

[mak-uk]

Hi

What's the best/quickest way to insert large amounts of data (say, countries) into a MYSQL database?

Something like:

INSERT INTO Country VALUES (1,'Afghanistan'),(2,'Brazil'),.....Z ?

Or is there anything faster?

Thanks.

Mak.

[Phil.Roberts]

Theres only one way to get info into an SQL server, and thats to INSERT it one row at a time.... So no, there isnt any faster way.

[mak-uk]

Hi

I don't suppose anyone has countries/occupations (etc.) already typed up into SQL inserts? They are quite common so it would be a shame to re-do all that typing. If not, guess I will have to!

Thanks.

Mak.

[TheDude]

Why are some people too lazy to use Google?

this came up as the first result with "country code iso mysql insert"


http://27.org/isocountrylist/

[mak-uk]

Hey

Thanks for that.

I did use google.. not lazy to use it, just incompetent at using it, perhaps :P. Thanks for that.

The person who did this went about it in the following way:

----
# Create table for ISO country list
CREATE TABLE IF NOT EXISTS country (
id INT(11) NOT NULL auto_increment,
name VARCHAR(80) NOT NULL,
iso CHAR(2) NOT NULL,
PRIMARY KEY (id),
INDEX by_name (name,iso),
INDEX by_iso (iso,name),
);

# Data for ISO country list
INSERT INTO country values(NULL,"Afghanistan","AF");
----

I was wondering if someone could explain the following to me:

1. How the INDEX by is used?
2. Why is the first auto_increment value set to NULL instead of 1,2..n. Is that 'cos it is 'auto_increment' so why not let SQL fill it in?

Thanks.

Mak.

[seanf]

Moved to a more appropriate forum

Sean

[someonewhois]

Originally posted by mak-uk

2. Why is the first auto_increment value set to NULL instead of 1,2..n. Is that 'cos it is 'auto_increment' so why not let SQL fill it in?

I can answear th is part....


NULL is the same as leaving it blank, but since you're doing values, doing this:

INSERT INTO country values("Afghanistan","AF"); 

Would put ID as "Afghanistan".

So you can do one of 2 things:

INSERT INTO country values("","Afghanistan","AF"); 

Or

INSERT INTO country values(NULL,"Afghanistan","AF"); 

Null is easier, IMO, it'll still put the right ID in there.

It's becuase when you use VALUES() it doesn't know Afghanistan is supposed to be a country, it'll think if it's first it shouild be the ID

Hope I helped!
Post back if you still don't get it

Thanks,
~someonewhois

[mak-uk]

Hi

What about if instead of putting:

INSERT INTO country values(NULL,"Afghanistan","AF");

You put:

INSERT INTO country values(1,"Afghanistan","AF");
INSERT INTO country values(2,"Brazil","BR");
... down to 300 or something

Would that work the same? If it does, obviously it's easier putting NULL instead as it will ensure that u dont have any errors in numbering and it will automatically do the work for u... right? (I think!).

Thanks.

Mak.

[haironfire]

Originally posted by mak-uk
What about if instead of putting:

INSERT INTO country values(NULL,"Afghanistan","AF");

You put:

INSERT INTO country values(1,"Afghanistan","AF");
INSERT INTO country values(2,"Brazil","BR");
... down to 300 or something

Why not let the ID auto-increment like it is designed to do instead of entering values for it? If you specify the column names as part of the insert command you should be able to skip the ID column. It should look like this:

INSERT INTO country NAME, ISO values ("Afghanistan", "AF");
...

[Defender1]

That sql would screw it up. Do

INSERT INTO country (name, iso) VALUES("Afghanistan", "AF");

[haironfire]

Right. Forgot to show the () around the column names. But the point remains the same.

[mak-uk]

Hi

Would this work as well:

INSERT INTO country (name, iso) VALUES('Afghanistan', 'AF');

i.e. single quotes instead of speech marks.

Are single quotes just used in queries within PHP?

Thanks.

Mak.

[haironfire]

Originally posted by mak-uk
Are single quotes just used in queries within PHP?

Either is fine. I prefer using "" as a rule. If you have a string embedded within a string, use "" on the outside. However, if you only have one string either way is fine.

You could just play around with this to find workable combinations.

[DR_LaRRY_PEpPeR]

Originally posted by haironfire
Why not let the ID auto-increment like it is designed to do instead of entering values for it?

yeah, it's designed to auto increment if you want it to! dumps always have an AUTO_INCREMENT column's values explicitly in the dump file. you certainly DO NOT want the numbers to be generated for you with existing data! if numbers were deleted, they will get reused when renumbered sequentially. if you had references to a certain id in another table, which you most likely did, guess what? it will be pointing to the wrong thing now.

since these AUTO_INCREMENT columns usually serve as the PRIMARY KEY, its values should stay the same. there's a good chance that won't happen if you "let the ID auto-increment like it is designed to do..."

[haironfire]

Originally posted by DR_LaRRY_PEpPeR since these AUTO_INCREMENT columns usually serve as the PRIMARY KEY, its values should stay the same. there's a good chance that won't happen if you "let the ID auto-increment like it is designed to do..."

Thanks for the heads up.
I thought he was trying to insert fresh data, not a dump so it seems like it should be OK.

I am pretty new at this, but could you do the following:

1. create a new database in mysql.
2. compile the list of NAME, ISO values as a text file with each entry enclosed by quotes and a open / close paren around a whole line.

for example, ("Afghanistan", "AF")

3. Search and replace for the open paren to add the INSERT command

INSERT INTO county (names, iso) VALUE (

4. batch insert the resulting SQL file using:

mysql databasename < name_iso.sql

Does that make sense?

[mak-uk]

Hi

Hmm.. I am confused now.

What I am trying to do is write a PHP script to create my database, tables and put the values in there that I require.

So.. is it ok to do:

$sql8-1 = mysql_query("INSERT INTO Country (countryName) VALUES ('Afghanistan'),...('Zimbabwe')");

if (mysql_query($sql8-1)) {
    echo("<P>Query 8.1 OK: INSERT INTO Country</P>");
} 
else {
    echo("<P>Query 8.1 FAIL: INSERT INTO Country: " . mysql_error() . "</P>");
} 

Where '...' is not part of the query but all the countries in the middle.

Thanks.

Mak.

[DR_LaRRY_PEpPeR]

yeah, that should work fine. the only problem is that $sql8-1 isn't a valid variable name in PHP.

[mak-uk]

Hi

Silly me. What would you suggest as a valid alternative to express '8.1' in PHP?

Thanks.

Mak.

[DR_LaRRY_PEpPeR]

$sql8_1