'LOAD DATA INFILE' problems. PLEASE HELP!

[steven420]

I have tried using phpmyadmin to load files to a table and I can get it to work but only if the file is on my computer. If the file is on my server it won't work. after i load a file from my computer to a table the statement calls the file '/tmp/phpzYWh3x'. I don't understand why the file name changed. Is the file supposed to be name like this? I read somewhere that you have to use the absolute path to the file. I tried this and it didn't work. Can anyone tell me how to load a file from my server to a table using 'LOAD DATA INFILE'. All of the search results I can find are just syntax. Even if you can just help me load a file from server to a table with phpmyadmin using the load text file feature that would help alot.

Thanks,
Steven.

[skipwater]

Steven

I read somewhere that you have to use the absolute path to the file

Yes you need to call the file with the absolute path.

I have used this small php script to LOAD very large files into mysql database because of the limitations of phpmyadmin.

Hope this helps

<?php
// setup file and table
$source_file_01 = "/usr/home/sites/www.dotcom.net/web/data/tab_flatfile.txt";
$destination_table_01 = "db_table_name";

//call to function
$luinfo = load_file_data($source_file_01, $destination_table_01);

// LOAD DATA INFILE
// EMPTY TABLE BEFORE LOAD
function load_file_data($source_file, $destable){

// Connect to the DB
$db_host = "localhost";
$db_user = "#*$!x";
$db_pass = "#*$!#*$!";
$db_name = "#*$!#*$!x";

$connect = @mysql_connect($db_host,$db_user,$db_pass);
@mysql_select_db($db_name);

// Empty table
$query = "DELETE FROM $db_name.$destable";
$result = mysql_query($query);

// do the data import
$query = "LOAD DATA INFILE \"$source_file\" INTO TABLE $destable";

$result = mysql_db_query($db_name, $query, $connect)
or die(error_log ("Couldn't execute query \nINPUT FILE: $source_file \nTABLE: $destable \nERROR: ".mysql_error()."", 1,"[e-mail]"));

}
?>

[steven420]

Thank you for the help I will give that a try.

Thanks,
Steven.

[steven420]

I think the problem is that I'm not giving the correct path name. Above you have "/usr/home/sites/www.dotcom.net/web/data/tab_flatfile.txt" as the path to your file. The path to my file I think is "/var/www/vhosts/websitename/httpdocs/filename.txt" The file I want to use is located in my httpdocs area. I used "_SERVER[["DOCUMENT_ROOT"] to find this. I not sure if it is correct. Does the file have to be in the database? Using 'LOAD DATA INFILE' seems simple enough I'm just having problems with the path. Could someone tell me if I'm going about finding the path corectly?

[steven420]

Ok, I think I'm getting close but when I run the script I'm using I get an error message that says "insert failed: Access denied for user: 'username@localhost' (Using password: YES)" I'm not sure why access is being denied. Can anyone tell me why this is happening and maybe how to fix it.

Thanks,
Steven.

[skipwater]

Could be a couple of things:

Most likely password incorrect.

Link to mysql doc.
[dev.mysql.com...]

Best thing to do is echo the mysql error and track down the error number.

[steven420]

Ok, The problem was that I wasn't using LOCAL. Thank you to everyone who helped me through this problem.

Thanks,
Steven.

[steven420]

Well I have been able to load a regular text file but what I realy need to do is load a gzip file to my table. I can't seem to figure out how to do this. This is the code I have:

$feed = gzopen("path/filename.txt.gz","r");
$sql_1 = 'DELETE FROM example'; 
$sql_2 = 'OPTIMIZE TABLE example';
$sql_3 = "LOAD DATA LOCAL INFILE '$feed' INTO TABLE `example` FIELDS TERMINATED BY '¦' ENCLOSED BY '\"' ESCAPED BY '\\\\' LINES TERMINATED BY '\\n\\r'";
$query_1 = mysql_query($sql_1) or die('query_1 no good. '.mysql_error());
$query_2 = mysql_query($sql_2) or die('query_2 no good. '.mysql_error());
$query_3 = mysql_query($sql_3) or die('query_3 no good. '.mysql_error()); 

I have no idea how to make this work and after searching for hours I can't find anything of use. Please help.

Thanks,
Steven.

[skipwater]

The best I can do is refer you to Zlib Compression Functions [us2.php.net...]

It might be that you have to uncompress the file to a temp file read that and then trash the temp file. From what I see size of the file and the time of execution can create a error.