Inserting large amounts of data from flat-file into MySQL

[n00bfreak]

Hello,

 

I am trying to find the best and fastest way to insert data from a text file into a MySQL DB, currently I am using a very newbie technique:

 

<?php
function import_stuff($list) {
    $x = 0;
    $y = 0;
    $list = file($list);
    foreach($list as $line) {
        $y++;
        $line = str_replace("\n","",$line);
        $line = explode(":",$line);
        $stuff1 = $line[0];
        $stuff2 = $line[1];
        $q = mysql_query("SELECT `stuff1` FROM `stuff` WHERE `stuff1` = '$stuff1' and `stuff2` = '$stuff2'");
        if(mysql_num_rows($q) <= 0) {
            if($stuff1 != NULL && $stuff2 != NULL) {
            $stuff2 = mysql_real_escape_string($stuff2);
            $q = mysql_query("INSERT INTO `stuff` (`stuff1`,`stuff2`) VALUES ('$stuff1','$stuff2')");
            $x++;
            }
        }
    }
    echo "Inserted $x out of $y stuff\n";
}
?>

 

This would take a lot of time processing a file with 10,000 lines for example...

 

If you have any ideas of a better way, please guide me thro it, it needs to check if the information is in the DB before it inserts it, meaning all the information in DB needs to be unique.

 

Thanks!

[Mchl]

Fastest way:

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

[n00bfreak]

Say the flat-file has the following:

 

test:test2

test3:test4

test5:test6

test:test2

 

Can I have load-data make sure that there are no exact same data in the DB before it inserts? in that example, it would insert everything except the last line

 

One more thing, how would it know which bit to insert in which row?

 

Thanks!

[Mchl]

You can load that data into a temporary table, and then filter out duplicates for example and move data to final table. There are variety of ways. The LOAD DATA INFILE is just the fastest way to load large amounts of data into MySQL. Once it's there, you can do whatever you want with it.