automated FTP of .csv file to server

[showman13]

Good Evening All...

I have a need to do something and have no clue where to start, so Here I am asking for some direction or even a solution if you happen to have one.

Here is the situation...
I have a member base that orders a product that is on a subscription basis, and I need to send a .csv file to our product provider on a nightly basis via a CRON job...

I have a secure ftp site to upload it to with username and password.

Where do I start?

I use FTP all the time, by logging in manually, but have never had a situation where I needed to access it in an automated fashion.

Any direction or assistance in this matter would be greatly appreciated.

Thanks in advance
Douglas

[veedeoo]

Hi,

This is pretty easy task in PHP.

First, get yourself acquainted with the ftp_login PHP function.

Second, test that function in a more meaningful application utilizing the ftp_put PHP function as shown here.

Lastly, please allow me to add my own personal opinion about these PHP functions in just ONE short sentence. It is pretty SLOW.

Other solution that is a lot faster than what I have mentioned above is to use cURL or remote uploads server to server. This can be very fast..

[showman13]

Thank you for your response.. That is the kind of information I love to get from people in the know... which is why I come to DaniWeb...

I never thought about cURL for this functionality. I know I've used it for other purposes in the past, but not for a while, so will need to go relearn how to utilize it.

Any suggestions along those lines would be welcome as well.

Would make much more sense to use cURL in this instance as long as I can figure out how to do it.

Thanks Again
Doublas

[diafol]

If you're just uploading one file at night when it's quiet (is it?), I don't suppose that cURL / ftp will make too much difference. I'm making the assumption that the file isn't huge. If you want to use curl to upload to the ftp server: http://www.web-development-blog.com/archives/tutorial-ftp-upload-via-curl/

[showman13]

Thanks diafol for your response...

Yes, it will be sent using a cron job in the middle of the night, and initially it won't be huge, but does have the potential of being thousands of records on any given day. each record having 20 fields in it with an average of 15 characters per field (300 char per record +-)

I don't know if you would consider that large or not...

I will check out the link that you provided... I always appreciate educational resources. Just wish I had the time to take off and do nothing but study for a few months... I might begin to feel like I'm not always behind the 8 ball searching for answers. LOL

Will let you know what I come up with.

thanks again
Douglas

[diafol]

I haven't used the ftp_* functions in years, but I make regular use of curl. Seems it can make you breakfast, give you a massage and turn water into wine. ;)

Mind you, I have to check the manual every time - all the various bits and bobs - I can never remember them.

[showman13]

OK, I'm at a sticking point, and hopefully someone can help me past it...

Most likely missing some simple, like a malformed directory or something.

Here is the script that I have that gathers the data required and generates the .csv file, but I can't seem to get it someplace that I can access it unless I use the headers and it shows up in my downloads file.

What I need is to have it someplace that I can access, to use cURL to FTP it to the secure server of the vendor, so they can process it.

So I'm assuming it would need to be saved in a folder on my server, which I have created called 'enroll_updates' which is located in the main directory, same directory that this script is running from.

Any feedback would be greatly appreciated.

Thanks
Douglas

<?php  // script name vendor_order_gen.php - used to generate .csv file of
        // enrollment records and ftp to secure server.
session_start();
include "all_inc/config_site.php";
include "all_inc/functions.php";
// still to do:
//  do some sort of check to see if file for yesterday has already been created
// if it has, then redirect someplace else...
$created = date("Y-m-d H:i:s", time()) ;
$Group_ID='333';
$SubgroupID='555';
$GroupNum='777';
$demographics='1';// always set to update Vendor files
// query to get list of mem_ids from prod_enroll belonging to Primary members
  // that were modified or added yesterday
$sql = "
  SELECT mem_id
  FROM prod_enroll
  WHERE  date_sub( last_update, INTERVAL 1 DAY )
  AND rel_code = 'E'
";
$result=mysql_query($sql);
$num_rows = mysql_num_rows($result);
if ($num_rows>0){
  $output='';
  while($row=mysql_fetch_array($result)){ // step through each Primary record
    $mem_id=$row[0];
    $static_1 =$Group_ID.'",'.'"'.$SubgroupID.'",'.'"'.$GroupNum.'",'.'"'.$mem_id;
    // contains GroupID / SubgroupID / GroupNum  from HY and / mem_id of primary
    //  this query is for the static information on each member/family to upload
    $sql = "
      SELECT email, addr1, addr2, city, state, zip, phone, plan
      FROM members
      WHERE mem_id = '$mem_id'
      LIMIT 1
    ";
    $result=mysql_query($sql);
    $row=mysql_fetch_array($result);
    //  $static_2 - contains addr1 / addr2 / city / state / zip / phone / email
    $static_2 =$row[1].'",'.'"'.$row[2].'",'.'"'.$row[3].'",'.'"'.$row[4].'",'.'"'.$row[5].'",'.'"'.$row[6].'",'.'"'.$row[0];

    // this is the query to get the variable information
    $sql = "
      SELECT enroll_id, rel_code, fname, lname, gender, birthdate, cover_start, cover_end, enroll_status
      FROM prod_enroll
      WHERE mem_id = '$mem_id'
      AND enroll_status!='A'
      ORDER BY enroll_id
    "; // get all NON-archived records for member/dependents
    $result=mysql_query($sql);
    $body_ct=0;

    while($row=mysql_fetch_array($result)){// walk through prod_enroll records
      $fam_mem[$body_ct]=$row;

      // create .csv record.
      // variable_1 contains fname / middle initial field blank / lname /
      $variable_1 =$fam_mem[$body_ct][2].'","'.'","'.$fam_mem[$body_ct][3];

      // variable_2 contains rel_code / dob / gender / start date / end date / demo-always 1
      $variable_2 =$fam_mem[$body_ct][1].'",'.'"'.$fam_mem[$body_ct][5].'",'.'"'.$fam_mem[$body_ct][4].'",'.'"'.$fam_mem[$body_ct][6].'",'.'"'.$fam_mem[$body_ct][7].'",'.'"'.$demographics.'","'.'"';

      $output .='"'.$static_1.'",'.'"'.$variable_1.'",'.'"'.$static_2.'",'.'"'.$variable_2."\n";
      ++$body_ct;
    }
  }

  // Download the file
  $directory = '/enroll_updates/';
  $filename =  'TEST_24houredocs_STANDARD_'.date('Ymd',(strtotime('-1 day',strtotime($created)))).'.csv';

// Using these headers it generates the .csv file in my local downloads file
  // where I can open it and view the contents to ensure they are correct
//header("Content-type: application/octet-stream");
//header("Content-Disposition: attachment; filename=$filename");
//header("Pragma: no-cache");
//header("Expires: 0");

  $fd = fopen ( $directory . $filename, "w");// open file to write to
  fputs($fd, $output);// write the data to the opened file
  fclose($fd); // Close the file

  print "$output"; // just to display what was in the output variable

  $to = $support_mail;
  $subject = "Generated vendor order";
  $message = "<p>Vender order for yesterday has been generated</p>
    <p>File name is : ".$filename."</p>
    <p>Here is the content</p>
    <p>".$output."</p>";
  // Send email
  mail_sender($to,$support_mail,$subject,$message) ;

  print "<br><br>Processing complete - File should be stored";
}else{
  print"<br> No file to process";
}
?>

[showman13]

I don't know if nobody has read the last update or just nobody has an answer...

Maybe someone would have an answer to this one...

I changed the open to go to the root directory, but still not writing to the file

  $directory = '/enroll_updates/';
  $filename =  'TEST_24houredocs_STANDARD_'.date('Ymd',(strtotime('-1 day',strtotime($created)))).'.csv';
  $fd = fopen ( $_SERVER['DOCUMENT_ROOT'].$directory.$filename, "w");// open file to write to
  fputs($fd, $output);// write the data to the opened file
  fclose($fd); // Close the file

Any Suggestions??

[iamthwee]

basically you're asking how to write a file to a directory right?

Do you have write permissions to the directory? Does the directory exist?

[showman13]

I have the permissions set at 770 on that folder, so should be able to write to it.

Yes, just trying to figure out why I am not getting the new .csv file into that folder that it is directed to.

[showman13]

OK, I decided to try the permissions at 777, and it worked just fine. But I can't leave it at that, so what is the solution?

Any ideas from anyone?

[veedeoo]

0755 is the recommended directory permission for linux servers. However, this can change depending on the server's server API. When you run a simple php info() script anywhere in your public directory, you will find what is on your server.

for example, if the Server API is an apache module, then your server write permission or CHMOD should be set to 0777 or 777. This I don't know of any other alternatives.

The truth is, pretty much nobody uses the apache module as server API. What are commonly use these days are Apache Handler 2.x and Fast/CGI which allows the recommended 0775 or 775 permission for directory permission and giving all files a persmisson of 644. Most hosting companies only allow maximum permissibility value of 0755 or 755.

Test your application if it can write with 755 permission.

Also, I noticed that you are using fputs() function. I know for sure PHP have a dedicated function in handling CSV files and it is called fputcsv() function.

[showman13]

755 didn't work

757 did, as well as 777

[veedeoo]

May I know which one do you have Unix or Linux?
Is phpsuExec or suExec installed?
After generating the CSV file, what is the permission given to this file?

Simple things about directory and file permissions.

We can view directory and file permission or CHMOD as it would apply to our valuable possessions. As the rightful owner, we can control access to these possessions at all times.

The access controls can be imposed on three major user classifications.
1. Owner = this is you
2. Group = user group
3. World = you, user group, and the entire world

you can be a member of a user group, which is pretty common in linux environment.

There are three permissions that can be given to the users.
1. permission to read
2. permission to write
3. permission to execute

Integer values of the permissions.
1. Read = 4
2. Write = 2
3. Execute = 1

The loosest permission is 7 for each user group. Which is pretty scary. Most hackers can exploit any file ( NOT directory) with write permission 777 for the file.

Illustration of your current settings

At 777, this is pretty scary scenario if this applies to a file.

+---------+--------+---------+-------+
+---------+  owner +  group  + world +
+---------+--------+---------+-------+
+ read    +    4   +    4    +   4   +
+ write   +    2   +    2    +   2   +
+ Execute +    1   +    1    +   1   +
+---------+--------+---------+-------+
+ CHMOD   +    7   +    7    +   7   +
+---------+--------+---------+-------+

At 757

+---------+--------+---------+-------+
+---------+  owner +  group  + world +
+---------+--------+---------+-------+
+ read    +    4   +    4    +   4   +
+ write   +    2   +    0    +   2   +
+ Execute +    1   +    1    +   1   +
+---------+--------+---------+-------+
+ CHMOD   +    7   +    5    +   7   +
+---------+--------+---------+-------+

Directory permission at 755 and as recommended

+---------+--------+---------+-------+
+---------+  owner +  group  + world +
+---------+--------+---------+-------+
+ read    +    4   +    4    +   4   +
+ write   +    2   +    0    +   0   +
+ Execute +    1   +    1    +   1   +
+---------+--------+---------+-------+
+ CHMOD   +    7   +    5    +   5   +
+---------+--------+---------+-------+

The reason 755 is the most preferred because of the write permission restriction on the group and the world. Which really make sense. Why would I let someone write on my file?

So, if the csv file is not CHMOD at 777, or 757 you should be okay. If possible it should be CHMOD to 644.

File permission as recommended at 644. The group are left out with only read permission. Even

+---------+--------+---------+-------+
+---------+  owner +  group  + world +
+---------+--------+---------+-------+
+ read    +    4   +    4    +   4   +
+ write   +    2   +    0    +   0   +
+ Execute +    0   +    0    +   0   +
+---------+--------+---------+-------+
+ CHMOD   +    6   +    4    +   4   +
+---------+--------+---------+-------+

There are cases where 777 permission cannot be avoided. A good example, is when static ffmpeg is used.

I hope I did not cause any confusion.

[showman13]

It is Linux...

phpsuExec and suExec are both installed according to the host

I have a decent understanding of the utility of the permissions, but just don't like having things wide open to the world...

the host for my dedicated server says I can't change permissions in an automated fashion after creating the .csv, so I wouldn't be able to set it to 644 apparently. But once the .csv file is created, it will never be updated or changed in any way, so would be good to have the permissions to protect that.

This is a multi-step learning process for me, as I'm doing several new things at the same time and having to research each individually and then combine the new knowledge into a single process.

Thanks for the follow up. Hopefully I will get this all done soon...

Basically what I'm doing is this:
I have members that enroll for the service that a vendor provides:
Those enrollment files need to be submitted to the vendor as .csv:
So, when the enrollment happens, the data is stored in my DBtable:
Nightly via Cron, I will take the previous days table data and create the csv.
Then I need to send the .csv to the vendor via FTP also through CRON job:

But, when I process the files I need to ensure that that days file hasn't previously been processed...

What I thought would be a simple task, continues to expand, but I guess that is how I learn on the fly.

Douglas

[showman13]

BTW, the permissions on the .csv file when it is created, are automatically set to 644. It just dawned on me to check that.

[iamthwee]

Not saying this is the best solution, but people kinda get around this by creating a folder with a sessionid as the name then saving this value in the db for reference.

You can be reasonably assured the 'general public' won't be able to guess this folder name or access it by accident...

[veedeoo]

@Douglas

You can pretty much add a column on your dbtable to accomodate the processed status.

for example, if we have a database table called prod_enroll, having the following columns. enroll_id, rel_code, fname, lname, gender, birthdate, cover_start, cover_end, enroll_status. We can easily add a column named process.

enroll_id, rel_code, fname, lname, gender, birthdate, cover_start, cover_end, enroll_status, process.

The process column value could be set to bolean false as default and then update it to true ONLY WHEN the CSV file creation was successful.

If we go this route, then on your query during the CSV file creation, we can add another WHERE clause.

something like this.

FROM prod_enroll
WHERE mem_id = '$mem_id'
AND enroll_status!='A'
AND process = false
ORDER BY enroll_id

So, if we are to use the above query then we should assign a default value of 0 to the column process.

We can then check if the csv file exist in the directory and if it exist, we can run the update query setting the "process" column value to true or 1 for all of the rows affected by the first query.

[diafol]

Ok, I realise this isn't an issue anymore but,

the host for my dedicated server says I can't change permissions in an automated fashion after creating the .csv, so I wouldn't be able to set it to 644 apparently.

Wouldn't ftp_chmod work for you (in an automated fashion):

http://www.php.net/manual/en/function.ftp-chmod.php

But as it's 644 by default, I assume you're OK.

[showman13]

Yep, it appears that would have done it... Thanks

I am still trying to work out getting it to the other company's secured server.

I got the script for the cron working just fine, but that just puts it on my server. Now I need to get cURL to move it over to their server, and having issues getting it to do anything... I'm a little perplexed with getting the remote directory which appears to be a requirement for it to work, but the only answer I get from the vendor is to just put it in the root directory.

Think I've just been too tired when I've tried to focus on that for the last couple of days, but maybe tomorrow will be a better day.

All of the documentation I have for remotely transferring files to another server requires a Directory to ftp into
i.e. $remote_file= “/public_html/remote/directory/”.$mydailyfilename;

Vendor says 'You can send it to the /root directory.', so I guess that means that I should be able to just define it as:

$remote_file as"/".$mydailyfilename;

I'll try it in the morning and hopefully it will work the first time.

But now I'm also not clear on if I can transfer it from my server to theirs or if it just needs to be written to their server directly...

LOL. I sound lost, even to myself.

Ah well, tomorrow will be a better day.

Thanks again for your response.
Douglas