LOAD DATA INFILE help? :)

Anon

Hey guys, I'm new to php/mysql and I can't seem to find any working code or info on load data infile.

does anyone have any working code that I can put into a php page to load a txt file on my harddrive into a mysql table on a 3rd party server?

thanks a million

[deleted]

load data infile is one single MySQL query, read the MySQL manual about the syntax. There is nothing special you have to do in PHP, just create a connection and run the query.

It helps if you try it out on the MYSQL command line first.

Anon

I've read the syntax and tried it verbatum, except for the file name and table. Im getting syntax errors when trying to enter it in.

I was hoping someone could paste actual code that they have proven to work.

[deleted]

the syntax in the manual works.

what did you try exactly?

Anon

Uhm, ok: this is my code that has proven to work:

function ActionImport()
{

   echo"<br><b><font color=red>Het importeren van de bestanden</b></font><br><br>";

        $Ins1 = "LOAD DATA LOCAL INFILE 'dbtxt/qrya001_exportlid.txt' REPLACE INTO TABLE $dbname.tblLid FIELDS TERMINATED BY ';'  LINES TERMINATED BY '\n' ";

        $Ins2 = "LOAD DATA LOCAL INFILE 'dbtxt/qrya002_exportinstelling.txt' REPLACE INTO TABLE $dbname.tblInstelling FIELDS TERMINATED BY ';'  LINES TERMINATED BY '\n' ";

        $Ins3 = "LOAD DATA LOCAL INFILE 'dbtxt/qrya003_exportlocatie.txt' REPLACE INTO TABLE $dbname.tblLocatie FIELDS TERMINATED BY ';'  LINES TERMINATED BY '\n' ";

        $Ins4 = "LOAD DATA LOCAL INFILE 'dbtxt/qrya004_exportlidwerkgroep.txt' REPLACE INTO TABLE $dbname.tblLidGroep FIELDS TERMINATED BY ';'  LINES TERMINATED BY '\n' ";

        $Ins5 = "LOAD DATA LOCAL INFILE 'dbtxt/qrya005_exportwerkgroep.txt' REPLACE INTO TABLE $dbname.tblGroep FIELDS TERMINATED BY ';'  LINES TERMINATED BY '\n' ";


        $import1=mysql_query("$Ins1");
        $import2=mysql_query("$Ins2");
        $import3=mysql_query("$Ins3");
        $import4=mysql_query("$Ins4");
        $import5=mysql_query("$Ins5");

        echo"$Ins1<br>$Ins2<br>$Ins3<br>$Ins4<br>$Ins5<br>"; //controlemiddel om te kijken wat de werkelijke database-actie is.
        echo"<br><b><font color=red>Het importeren van de tekstbestanden was succesvol!</b></font><br><br>
        <a href=\"jaargang.php\">Verder</a>";

}

this replaces the fields that are in the MySQL tables.

Hope you'll work it out.

Jim wrote:

I've read the syntax and tried it verbatum, except for the file name and table. Im getting syntax errors when trying to enter it in.

I was hoping someone could paste actual code that they have proven to work.

Anon

Hope you can translate the dutch ;-)

Actually all you need is a database connection, a $dbname and some lines like $Ins5 = "LOAD DATA LOCAL INFILE 'dbtxt/qrya005_exportwerkgroep.txt' REPLACE INTO TABLE $dbname.tblGroep FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' ";

And the fields from Access (?) need to be divided by ; ofcourse. If you are just trying to convert Access into MySQL, have a look at http://www.accessmysql.com for a nice Access Addin.

[deleted]

Note that when using REPLACE, the current content of the table will be overwritten when a record of the new data (from the textfile) has the same value in the primary key column as a record in the current table.

Dangerous to say the least.