SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Addict mak-uk's Avatar
    Join Date
    Dec 2001
    Location
    Midlands, UK
    Posts
    284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question PHP/MySQL to insert data into DB

    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.

  2. #2
    No. Phil.Roberts's Avatar
    Join Date
    May 2001
    Location
    Nottingham, UK
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    THE INSTRUCTIONS BELOW ARE OLD AND MAY BE INACCURATE.
    THIS INSTALL METHOD IS NOT RECOMMENDED, IT MAY RUN
    OVER YOUR DOG. <-- MediaWiki installation guide

  3. #3
    SitePoint Addict mak-uk's Avatar
    Join Date
    Dec 2001
    Location
    Midlands, UK
    Posts
    284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Enthusiast
    Join Date
    Apr 2002
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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/

  5. #5
    SitePoint Addict mak-uk's Avatar
    Join Date
    Dec 2001
    Location
    Midlands, UK
    Posts
    284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Moved to a more appropriate forum

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  7. #7
    SitePoint Wizard silver trophy someonewhois's Avatar
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    6,364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    PHP Code:
    INSERT INTO country values("Afghanistan","AF"); 
    Would put ID as "Afghanistan".

    So you can do one of 2 things:

    PHP Code:
    INSERT INTO country values("","Afghanistan","AF"); 
    Or

    PHP Code:
    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

  8. #8
    SitePoint Addict mak-uk's Avatar
    Join Date
    Dec 2001
    Location
    Midlands, UK
    Posts
    284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  9. #9
    SitePoint Member
    Join Date
    Jul 2002
    Location
    Portland, OR
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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");
    ...
    Last edited by haironfire; Aug 11, 2002 at 10:01.

  10. #10
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That sql would screw it up. Do
    Code:
    INSERT INTO country (name, iso) VALUES("Afghanistan", "AF");
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*

  11. #11
    SitePoint Member
    Join Date
    Jul 2002
    Location
    Portland, OR
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Right. Forgot to show the () around the column names. But the point remains the same.

  12. #12
    SitePoint Addict mak-uk's Avatar
    Join Date
    Dec 2001
    Location
    Midlands, UK
    Posts
    284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  13. #13
    SitePoint Member
    Join Date
    Jul 2002
    Location
    Portland, OR
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  14. #14
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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..."
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  15. #15
    SitePoint Member
    Join Date
    Jul 2002
    Location
    Portland, OR
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  16. #16
    SitePoint Addict mak-uk's Avatar
    Join Date
    Dec 2001
    Location
    Midlands, UK
    Posts
    284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    PHP Code:
    $sql8-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.

  17. #17
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah, that should work fine. the only problem is that $sql8-1 isn't a valid variable name in PHP.

  18. #18
    SitePoint Addict mak-uk's Avatar
    Join Date
    Dec 2001
    Location
    Midlands, UK
    Posts
    284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking

    Hi

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

    Thanks.

    Mak.

  19. #19
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $sql8_1


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •