CODEWITHSUNDEEP
phpmysqlimport
CppLearner
CppLearner

Reputation: 17040

How do I load a text file to Database in PHP?

Originally $filepath is like this

$filepath=$_SERVER['DOCUMENT_ROOT']."/".SCRIPTFOLDER."somefile.txt";
$sqlquerynew = "LOAD DATA INFILE '".$filepath."' INTO TABLE mytable FIELDS TERMINATED BY ','";
mysql_query($sqlquery);

That works. It sits on the server.

But what if I want to put somefile.txt outside the same server? If I put that in dropbox, and reuse the code like the following, it doesn't do anything.

$sqlquerycreate = / ** Here insert DB scheme code **/
$filepath="http://example.com/somefile.txt";
$sqlquerynew = "LOAD DATA INFILE '".$filepath."' INTO TABLE mytable FIELDS TERMINATED BY ','";
mysql_query($sqlquery);

What should I do instead? The text file contains the data for the table.

Thanks.

$data_file = file_get_contents('http://dl.dropbox.com/u/14655573/john/nyccrash.txt');
file_put_contents('C:\xampp\htdocs\import\nyccrash.txt', $data_file);
$filepath = "C:\xampp\htdocs\import\nyccrash.txt";
echo $filepath;
$sqlquery = "LOAD DATA INFILE '".$filepath."' INTO TABLE nyccrash FIELDS TERMINATED BY ',' (crash_year, accident_type, collision_type,weather_condition,light_condition,x_coordinate,y_coordinate)";

Okay. I see this file now. But it didn't get import to the DB. This is XAMPP. But I will, eventually, move to Ubuntu. What's the problem with this code?

Upvotes: 0

Views: 2081

Answers (4)

kappa
kappa

Reputation: 1569

You can first import data in local file with 

//read data from remote file
$data_file = file_get_contents($http_url); 

// put data in a local file
$temp_file = tempnam(sys_get_temp_dir(),'TMP');
file_put_contents($temp_file,$data_file);

//now use $temp_file into the SQL query
....

//Delete temp file
unlink($temp_file);

Please note that PHP has functions to get the system's TEMP path, and to generate a temporary filename.

In the /tmp you should not have permissions issues.

Upvotes: 0

ComFreek
ComFreek

Reputation: 29414

Create first a temporary file on your server:

$tmpFile = tempnam('my-tmp-dir', 'TMP');
$ok = copy('http://example.com/somefile.txt', $tmpFile); 

if ( !$ok ) exit('ERROR!');

// Then use $filepath in your SQL query    
$sqlquerynew = "LOAD DATA INFILE '".$filepath."' INTO TABLE mytable FIELDS TERMINATED BY ','";
mysql_query($sqlquery);    

unlink($tmpFile);

Upvotes: 0

web-nomad
web-nomad

Reputation: 6003

Try this:

$file = file_get_contents("http://example.com/somefile.txt");

$filepath=$_SERVER['DOCUMENT_ROOT']."/".SCRIPTFOLDER."somefile.txt";
file_put_contents($filepath, $file);

$sqlquerycreate = / ** Here insert DB scheme code **/
$sqlquerynew = "LOAD DATA INFILE '".$filepath."' INTO TABLE mytable FIELDS TERMINATED BY ','";
mysql_query($sqlquery);

Hope it helps..

Upvotes: 1

CodeCaster
CodeCaster

Reputation: 151588

The LOAD DATA INFILE reads a file from the client or from the server, not from the internet.

You could use copy() to first download the file to a temporary directory, and then point LOAD DATA INFILE to that directory.

Upvotes: 0

Related Questions