While and FTP

[wiggst3r]

Hi guys

 

I'm doing a small project and I need some help.

 

I'm trying to query a database and find the last row that was inserted into the database determined by the date(e.g, 25th June - 2008-06-25) - which I've done (see code below)

 

$result = mysql_query("SELECT date FROM csv_files ORDER BY date DESC LIMIT 0, 1")
or die(mysql_error());

while($row = mysql_fetch_array( $result ))
{
$last_inserterd_date =  $row['date'];
echo "LAST INSERTED [$last_inserterd_date]<br>";

}

 

I'm then trying to loop through some files on an FTP server, which are in the format, File_2008_06_25.txt, for example.

 

From then, I want to check that the last inserted row, if it was 25th june, loops through the files on the FTP and finds then next file, File_2008_06_26.txt and runs a function I've written, that processes the txt files contents, once that's done, it will be inserted into the database. So then, the last inserted date/file, would be 26th june etc

 

This should loop around, until it reaches todays date, when it should exit.

 

so what I'm looking to do is:

 

1) Get last row which was inserted into the database (date)

2) Search the FTP and check the date of the file, if not in the database, download and run a function

3) Go to next file, if not in database, download and run the the function etc etc (the loop)

4) If the last file on the FTP server has a date that is greater than todays' date, then the script should stop.

 

Can anyone point me in the right direction?

 

I'm really stuck

 

Thanks

[mbeals]

this may not be a popular answer on a php forum, BUT....

 

do it in Perl.  Perl has both mysql and ftp modules that will allow for direct interaction with an FTP server... so you won't have to make system calls and hack apart the serialized return.

 

read: http://search.cpan.org/dist/libnet/Net/FTP.pm

 

I would use the ls() method to get a list of files into an array.  Then I'd iterate over that array, getting the last modified time using the mdtm(  ) method.... then test that time and perform whatever action it is you need.

 

I've done ftp in php, perl and BASH and by far, perl  is the simplest and most elegant way of handling it.  I can post one of my ftp scripts that uses mysql queries if you'd like to give you a head start

 

[wiggst3r]

That would be great, but I'd prefer to write in PHP.

 

Have you got an example similar to what I'm after but written in PHP?

 

Thanks

[mbeals]

my only good example is written perl, but take a look at

 

http://www.w3schools.com/PHP/php_ref_ftp.asp

 

It should give you enough info to put together a script.

[wiggst3r]

I know how to download a file from FTP

 

My only issue, is the loop so far, how to loop through the files on the ftp server, and check the date on them with the rows in the database.

 

My code so far is

 

while($row = mysql_fetch_array( $result ))
{
$last_inserterd_date =  $row['date'];
echo "LAST INSERTED [$last_inserterd_date]<br>";
$next_date = date('Y-m-d', (strtotime($last_inserterd_date)+ 86400));
echo " NEXT DATE IS [$next_date]";
}

 

So far $next_date only returns the date after, I need to use this to loop through.

 

I have a date in the database from 11th june, I have 6 files dated 13th june, 17th june, 18th june, 19th june, 24th june, 26th june, 27 june, 30th junand 1st july on the FTP server

 

I've got the last date in the database, which is the 11th june. So what I'm looking to do, is loop through and find the next date, if there's no 12th june, loops through and finds 13th june, downloads the file named File_20080613 and runs the function to process the contents and then this gets inserted into the database(the script does the insertion)

 

Once the 13th june has been processed, it loops through again, if 14th june is found downloads, if not, loops through, until it gets to 17th and the whole process starts again, until it gets to todays date. The loop should have someway of exiting, for example, if $next_date is greater than todays date, it should exit.

 

I know this sounds complicated, but i just need to be able to loop through.

 

Thanks

 

[mbeals]

okay,  your loop issue can probably be solved with unix time stamps.  Look up mktime and date.  It might require some trickery, but you should be able to make it work.  I would figure out the timestamp for the starting date and for the ending date (today), then run a for loop using 86400 as your interval step.  Time stamps are increasing integers, so it won't be phased by spanning months.  Since you aren't worried about H:M:S, just day, you might have to do some regex matching to only match the first so many digits of the time stamp, but it shouldn't be difficult.

 

For the ftp server, I think you are going to have to pull in a list of files into an array, figure out which ones you want, then run a loop to fetch and process.  You could use ftp_nlist to get a list of files, then in your loop test the file with ftp_mdtm() to decide whether it needs to be downloaded and processes, or you could use ftp_rawlist to pull down a list of file name and info (including mod time) that you would have to process with regex to get a mod time you could test against.  I could see doing it either way.

[wiggst3r]

Ok.

 

I'm still stuck with this.

 

I've done a bit more code

 

$result = mysql_query("SELECT date FROM csv_files ORDER BY date DESC LIMIT 0, 1")
or die(mysql_error());


while($row = mysql_fetch_array( $result ))
{
$last_inserterd_date =  $row['date'];
//	echo "LAST INSERTED [$last_inserterd_date]<br>";
$next_date = date('Y-m-d', (strtotime($last_inserterd_date)+ 86400));
//	echo " NEXT DATE IS [$next_date]";
echo $last_inserterd_date_for_ftp = str_replace("-", "", $last_inserterd_date);

// connect to FTP
$ftp_server = "www.example.com";
$ftp_user_name = "username";
$ftp_user_pass = "password";
$fromDir = "folder/$last_file";
$toDir = "/Users/mack/workspace/test_csv/download/";

// Connected, else throw an exception
$conn_id = ftp_connect($ftp_server);
$login_result = ftp_login($conn_id, $ftp_user_name, $ftp_user_pass);

if((!$conn_id) || (!$login_result))
{
	echo "Could not Connect to the FTP Server";
	exit();
}

// array of the files
$contents = ftp_nlist($conn_id, $fromDir);
sort($contents);

// hard-coded until I get the file from the array from FTP
$string = 'File_20080611.txt';

if (strstr($string,$last_inserterd_date_for_ftp))
{
	// run the download funuction?
	echo 'Found';
}
else
{
	echo 'Not found';
}
}

 

Here, I'm trying to find a string, within the array that is created with all the files on the FTP.

Trying to see If I can find $last_inserterd_date_for_ftp (which outputs as 20080611) within the $contents array, which works.

But, I'll need to dynamically find $string as it can't be hard-coded. This is where i need to loop round, and find the next date, 20080612, and see if it matches within the array etc etc

 

 

[mbeals]

okay, i would pull that ftp out of the while loop.  Also, if all you are pulling from the database is the last date stored, you don't need to iterate over the results.  Try this (in pseudo code)

 

$result = mysql_query("SELECT date FROM csv_files ORDER BY date DESC LIMIT 0, 1")
or die(mysql_error());

$last_inserterd_date =  mysql_result($result,0,'date');
// echo "LAST INSERTED [$last_inserterd_date]<br>";

//turn  $last_inserterd_date into the same format as is in the file name


// connect to FTP
$ftp_server = "www.example.com";
$ftp_user_name = "username";
$ftp_user_pass = "password";
$fromDir = "folder/$last_file";
$toDir = "/Users/mack/workspace/test_csv/download/";

// Connected, else throw an exception
$conn_id = ftp_connect($ftp_server);
$login_result = ftp_login($conn_id, $ftp_user_name, $ftp_user_pass);

if((!$conn_id) || (!$login_result))
{
echo "Could not Connect to the FTP Server";
exit();
}

// array of the files
$contents = ftp_nlist($conn_id, $fromDir);
sort($contents);

for($i=0,$i<count($contents);$i++){

  #### extract date from file name #####
  $filename = $contents[$i];

  preg_match('/File_([\d]+)\.txt/",$filename,$file_date);

  $file_date = $file_date[1];

  ###check if file is newer then the last date #####
   if($last_inserterd_date lt $file_date) {    

       ### process if true
   }


} //end for 

 

 

 

Does that make sense?  Now you will have to play with it, but you may be able to get away with a natural language comparison there.  ie turn your last date from the DB into the form: File_date.txt and skip the preg_match.  You don't really need to know what they next date is, just if the file is newer then the first date.

 

[wiggst3r]

Hi thanks for that.

 

I've changed my code and got the FTP connection out of the loop.

 

<?php
require_once('config.php');
require_once('dbconn.php');
start_trace('run.php');
function run()
{
$fct_name = 'run';
function_start($fct_name);

$up_to_date = false;

// db select
$result = mysql_query("SELECT date FROM csv_files ORDER BY date DESC LIMIT 0, 1")
or die(mysql_error());


$row = mysql_fetch_array( $result );
$last_inserterd_date =  $row['date'];
debug("LAST INSERTED [$last_inserterd_date]");
$next_date = date('Y-m-d', (strtotime($last_inserterd_date)+ 86400));
debug(" NEXT DATE IS [$next_date]");
if ($next_date == date('Y-m-d'))
{
	$up_to_date = true;
}
else
{
	// connect to FTP
	$ftp_server = "www.example.com";
	$ftp_user_name = "password";
	$ftp_user_pass = "password";
	$fromDir = "folder/";
	$toDir = "/Users/mac/workspace/test_csv/download/";

	// Connected, else throw an exception
	$conn_id = ftp_connect($ftp_server);
	$login_result = ftp_login($conn_id, $ftp_user_name, $ftp_user_pass);

	if((!$conn_id) || (!$login_result))
	{
		echo "Could not Connect to the FTP Server";
		exit();
	}
}


while(!$up_to_date)
{

	$file_to_search_for = "File_" . date('Ymd', strtotime($next_date)) . ".txt";
	debug("The file to search for is [$file_to_search_for]");


	//check FTP for date $file_to_search_for
	//if file exists
		//download file and run
	//
	//set $next_date to be $next_date + 1 day

	// if $next_date > today
		$up_to_date = true;
	//end
}

function_end($fct_name);

}
run();

end_trace('run.php');

?>

 

I've added in some debug, so I can see what the date is of the last inserted date and the next date.

 

You can see what I'm trying to do, in the while loop. See the comments.

 

But I'm not sure how to do that.

 

Any ideas?

[wiggst3r]

Can anyone help?

 

Please

[mbeals]

if you are 100% sure of your file naming structure, then it's easy from here.

 

The ftp_fget() function returns true if success or false on failure.

 

so you could do

 

if(ftp_get($connection,$local_file,$remote_file,$mode){
     
   //process stuff here

}

 

That will try and download the file (regardless if it exists or not).  If the file is downloaded (meaning it exists) then it is processed.  Otherwise the loop continues on to the next file name.  This method will also mean that your script won't try to process a failed download.  You could make it a little more robust by:

 

if(ftp_size($connection,$file_name) != -1){

  if(ftp_get($connection,$local_file,$remote_file,$mode){
     
     //process stuff here

  }

}

 

That checks for the existence of the file first, allowing you to handle the condition of the file existing but the download failing.  The ftp_get function allows you to resume download with an final option that points to the spot in the file to resume.  I've never played with it, but it shouldn't be hard to implement.

 

The other way would be to do something like this (which I tested and should work)

<?php
########Get list of files from FTP server ########

//insert code here


$files = array('File_20080611.txt','File_20080612.txt','File_20080613.txt'); //fake return array

##### Get date from database ###############

$last_date = 20080611;   //pulled from database
$date_str = "File_$last_date.txt";


#####compare each file name pulled from ftp against date string from db ##########
for($i=0;$i<count($files);$i++){

if(strcmp($files[$i],$date_str) >= 0){
	//download $files[$i];
	//process files
	}

}




?>

 

there, you make the ftp transaction to get the file list once, then compare that list against your date from the database to determine if you need to download the file.  It's less ftp transactions and doesn't require you to calculate the 'next date' for comparison.

[wiggst3r]

Well I'd rather stay away from putting the file name explicitly in the array, as this script is going to be running daily, on a cron, so I'd rather not have need to keep adding file names into the array

 

I'll have a look at it, and keep trying.

[mbeals]

I'm not exactly sure what you mean by adding names to the array?

 

In the third example, that $files array I created was just so I didn't have to write the ftp code.  In the final script, you would use the ftp functions to get a list of file names and generate that array on the fly.  That's what would be in the "//insert code here" section.  Does that make sense?  The $last_date variable is the same situation.  I set it explicitly, but in reality you would make a database call and set it dynamically.

 

If you do have a lot of files on the ftp server, the first method might work faster.