[Resolved] A little desperate help please :)

[thehb]

I know this has to be possible, I'm hoping someone can give me a quick and easy solution. Here we go . .

1) I need to connect to an FTP site and download all the files in a specific folder on a weekly basis (Once I get the script I can have Cron do this). All the files are in CSV format.

2) I need to then insert the data in those files to our MySql database, but I dont want to upload the data in the files I've already uploaded . . the previous week for example.

Thats it. . any code, script, or advise is appreciated. Even simple code to download the files and upload to MySql would help.

[devinemke]

1. have you looked at PHP's FTP Functions functions? everything you need is there.

2. use [man]fopen[/man] and [man]fgetcsv[/man] to parse thru the files and build your INSERT queries. assuming that one of the CSV fields is the date, use that to determine what you need to INSERT and what you do not.

[bradgrafelman]

Well, what version of PHP are you running?

Is FTP support enabled? To check, do a phpinfo() and look for the 'ftp' table.

EDIT: Aaaand devinemike beat me to it.

[thehb]

Thanks for the quick responce!

FTP support is enabled on the server. I'm on a VPN using PHP5.

I've looked at PHP's FTP functions but I've never had to use PHP with FTP so I'm a little lost of them.

Also, the worse part of this is the files are not dates (or a field in them), the file names just have random names (go figure!). I was thinking if I could store the names of each file I've downloaded in a table and check that table before inserting a new one into MySQL the next time ?

Basically, since I've never done anything with FTP I've gotten to the point when I can download the files from the site, I just cant get them inserted into MySQL and make sure the same one dosent go in next time. None of the files have a field that can be made a primary key, already tried that!

[thehb]

The script I have created so far is this, it returns a list of the files available on the FTP site in the folder.

<?php
$ftp1 = ftp_connect('ftp.thedomain.com') or die("Couldn't connect to server");
ftp_login($ftp1,'MYUSERNAME','MYPASSWORD');
ftp_pasv($ftp1, TRUE); //Passive Mode is better for this

//Enable PASV ( Note: must be done after ftp_login() )

$mode = ftp_pasv($ftp1, TRUE);
//Get them file!
echo "Beginning to Collect Files<br>";
//Set defaults just in case. PHP complains anyway if we don't.
$dir = "";

function filecollect($dir,$filelist) {
global $ftp1; //Get our ftp
$files = ftp_nlist($ftp1,$dir); //get files in directory
foreach ($files as $file) {
 $isfile = ftp_size($ftp1, $file); 
 if($isfile == "-1") { //Is a file or directory?
  $filelist = filecollect($dir.'/'.$file,$filelist,$num); //If a folder, do a filecollect on it
 }
 else {
  $filelist[(count($filelist)+1)] = $file; //If not, add it as a file to the file list
 }
}
return $filelist;
}

$filelist = filecollect($dir,$filelist);

echo "<pre>";
print_r($filelist);
echo "</pre>";
?>

[thehb]

Ok, can anyone simply show me how to do one of these two things, I think it will help a lot.

1. Download an entire directory from an FTP site.
   (I figured out how to download one file but not the entire directory)

2. Insert those downloaded CSV files into MySQL table?

[devinemke]

[man]ftp_nlist[/man] returns an array of files in a remote directory. loop thru the array using the [man]foreach[/man] construct and use [man]ftp_get[/man] on each loop iteration.

[thehb]

Thanks devinemke!

I seem to be getting closer to my goal. I think using foreach will work I just have no experience with it, I was reading over it and still dont quite understand (it's something I've been meaning to learn)

This is where I'm at:

I can use nlist and get all the files in the directory into a variable called $file, from there I dont know what to do with it besides echo it.

Also, I created a script than can insert a CSV file into the MySql table, works great. Basically I only have two more problems.

Making sure the file is not inserted into MySQL more than once, and figuring out how to insert each file instead of just one! At this point I'm stuck. I guess the suggestion would be to understand foreach ?

[thehb]

Alright, I think I have this working for the most part but I'm getting an error. This is the code I'm using:

<?php
$ftp_host = "ftp.mysite.com";
$ftp_user = "myusername";
$ftp_password = "mypassword";
$directory = "cdr";
$ftp_server = "ftp.mysite.com";
$ftp_user_name = "myusername";
$ftp_user_pass = "mypassword";
$conn_id = "historyfiles";

//Connect
echo "<br />Connecting to $ftp_host via FTP...";
$conn = ftp_connect($ftp_host);
$login = ftp_login($conn, $ftp_user, $ftp_password);

//
//Enable PASV ( Note: must be done after ftp_login() )
//
$mode = ftp_pasv($conn, TRUE);

//Login OK ?
if ((!$conn) || (!$login) || (!$mode)) {
   die("FTP connection has failed !");
}
echo "<br />Login Ok.<br />";

//
//Now run ftp_nlist()
//
$file_list = ftp_nlist($conn, "cdr");
foreach ($file_list as $file)

// define some variables
$local_file = "testtextfile.txt";
$server_file = "$file";

// set up basic connection
$conn_id = ftp_connect($ftp_server);

// login with username and password
$login_result = ftp_login($conn_id, $ftp_user_name, $ftp_user_pass);
// try to download $server_file and save to $local_file
if (ftp_get($conn_id, $local_file, $server_file, FTP_BINARY)) {
   echo "Successfully written to $local_file\n";
} else {
   echo "There was a problem\n";
}

// close the connection
ftp_close($conn_id);

?>  

PHP is giving me this error, any ideas ?
NOTE: 41050627.txt is one of the files on the FTP site

Warning: ftp_get: '41050627.txt' : No Such File. in /home/webadmin/securetrans.wirelessprocessing.net/html/testfiles/test.php on line 50
There was a problem

[thehb]

Thanks for the help, this issue has been resolved after staying up until 3AM last night I was able to do the following:

Download all files from the FTP folder using a foreach loop.
Insert the data from those CSV files using a SQL query inside the same loop.

Thanks for all the help!