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

Question

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?

use php to download the file to your server and use a local path. — Nanne
– Nanne, Commented Apr 3, 2012 at 13:00
@Nanne There is permission problem. My original script is supposed to upload the script by the user, through Browse/Select. Since I am not a root user, I can't change the permission on files... so I am trying to avoid working directly on the server — CppLearner
– CppLearner, Commented Apr 3, 2012 at 13:02
if your script uploads the file, the script can change the permission. anyway, sql can't -as far as i know- read trough http. so you need a local path. php can read trough http, but if example.com is your domain that should not be necessary. just chmod it after upload? — Nanne
– Nanne, Commented Apr 3, 2012 at 13:05

web-nomad · Accepted Answer · 2012-04-03 13:04:15Z

1

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..

answered Apr 3, 2012 at 13:04

web-nomad

6,0133 gold badges37 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

CodeCaster · Accepted Answer · 2012-04-03 13:02:22Z

0

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.

answered Apr 3, 2012 at 13:02

CodeCaster

153k24 gold badges237 silver badges287 bronze badges

Comments

ComFreek · Accepted Answer · 2012-04-03 13:10:40Z

0

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);

edited Apr 3, 2012 at 13:10

answered Apr 3, 2012 at 13:03

ComFreek

29.5k18 gold badges107 silver badges157 bronze badges

Comments

kappa · Accepted Answer · 2012-04-03 13:24:02Z

0

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.

edited Apr 3, 2012 at 13:24

answered Apr 3, 2012 at 13:01

kappa

1,5798 silver badges19 bronze badges

6 Comments

CppLearner Over a year ago

where will this be created? Thanks.

kappa Over a year ago

have you check the content of file after downloading? It may contain some HTTP error ..or any

CppLearner Over a year ago

Thanks. Yes. it works. I am trying to test this on my Ubuntu server, but the permission to the files are "apache apache". I can't even use chown... Any option? It doesn't get imported.... that's why I am suspecting it has to do with permission.

kappa Over a year ago

You should use the temporary directory instead that your app subdir, or you have to play with SGID on your "import" dir

CppLearner Over a year ago

thanks. Hmmm... tempnam(...) seems to be the main issue. I get a blank page (I commented out the rest and did not receive blank page). I am already on PHP 5.1.6, I think it should support sys_get_temp_dir()

|

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

Comments

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

6 Comments

Related