SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have a database in text form, like:

    "Joe","Black","145","Some Road","USA"
    "Bob","Marley","456","Another Street","Albania"

    And there's a few hundred of them.

    How can use a PHP script to insert those fields to my MySQL database? Basically I have to explode them to an array by new line, but how does PHP know when there's a new line?

    The insert query will probably be something like:


    INSERT INTO maillist (first,last,number,street,country) VALUES ("joe","Black","145","Some Road","USA"), ("Bob","Marley","456","Another Street","Albania");


    Just couldn't quite figue out how to explode each line, and insert them to the query, I would like to do it in one query, instead of multiple ones like:

    while()
    {
    INSERT INTO maillist (first,last,number,street,country)
    VALUES ("joe","Black","145","Some Road","USA");
    }

    'cos that way it'll have to make a few hundred queries...


  2. #2
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You might want to split it up into maybe two queries that is gonna be a hefty query but the bnest way I could see woulf be to do

    $data = file("textfile.txt");
    for ($i=0;$i<count($data);$i++) {
    if ($i != count($data) - 1) {
    $tmp = ereg_replace('"', "'", $data[$i]);
    $sqldata .= "(".$tmp."),";
    }
    else {
    $tmp = ereg_replace('"', "'", $data[$i]);
    $sqldata .= "(".$tmp.")";
    }
    }

    mysql_query("INSERT INTO maillist (first,last,number,street,country) VALUES $sqldata");


    or alternately use builtin mysql functions from the command line

    mysql>LOAD DATA INFILE 'textfile.txt' INTO TABLE tbl_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  3. #3
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot! Just needed to do a few adjustments to the text file to make it work.


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
  •