• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 665
  • Last Modified:

Modify MySQL/PHP script to FTP .csv export?

I am using the following code to export and create records to a CSV file and it now prompts the user to download the script.  Instead of doing this, can someone show me how to take the file and upload it to a remote FTP site?  Basically I just want to upload to another directory on another server using supplied credentials.

Can this be done with PHP?

<?php
$host = 'localhost';
$user = 'mysqlUser';
$pass = 'myUserPass';
$db = 'myDatabase';
$table = 'products_info';
$file = 'export';

$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");

$values = mysql_query("SELECT COLUMN1, COLUMN2, COLUMN3 FROM ".$table."");
$row = 0;
while ($rowr = mysql_fetch_assoc($values)) 
{
  if ($row == 0)
  {
    foreach($rowr as $name => $value)
    {
      $csv_output .= $name . "; ";
    }
    $csv_output .= "\n";
  }
  $row++;

  foreach($rowr as $name => $value)
  {
    $csv_output .= $value . "; ";
  }
  $csv_output .= "\n";
}

$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");
print $csv_output;
exit;
?>

Open in new window

0
pda4me
Asked:
pda4me
  • 10
  • 7
1 Solution
 
xtermCommented:
Sure!  Try this
$my_local_file="/path/to/somefile";
$ftp_server = "my.ftp.server.com";

$conn_id = ftp_connect($ftp_server) or die("Couldn't connect to $ftp_server");
if (@ftp_login($conn_id, $ftp_user, $ftp_pass)) {
    echo "Connected as $ftp_user@$ftp_server\n";
    $fp = fopen($my_local_file, 'r');
    if (ftp_fput($conn_id, $file, $fp, FTP_BINARY)) {
      echo "Successfully uploaded $file\n";
    } else {
      echo "There was a problem while uploading $file\n";
    }
    fclose($fp);
    ftp_close($conn_id);
} else {
    echo "Couldn't connect as $ftp_user\n";
}

Open in new window

0
 
xtermCommented:
Whoops, I guess you'll want to populate the login credentials too, so add at the top with the other variables
$ftp_user="remote_ftp_username";
$ftp_pass="your_password";

Open in new window

0
 
Ray PaseurCommented:
Here is a little more flexible version of the script to create the CSV file.  If you wanted to restrict the columns, you might add an array of column names, or something like that.  I've never used PHP to FTP a file, but there is a whole FTP thing built in.  Maybe I'll try it!
http://us.php.net/manual/en/ftp.installation.php
http://us.php.net/manual/en/ftp.examples-basic.php
<?php // RAY_db_to_excel.php
error_reporting(E_ALL);
echo "<pre>\n";


// DEMONSTRATE HOW TO EXPORT A TABLE SO THAT IT CAN BE USED IN EXCEL


// SET YOUR TABLE NAME HERE - OR MAYBE USE THE URL GET ARGUMENT?
$table_name = '???';


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}

// OPEN THE CSV FILE - PUT YOUR FAVORITE NAME HERE
$csv = 'EXPORT_' . date('Ymdhis') . "_$table_name" . '.csv';
$fp  = fopen($csv, 'w');

// GET THE COLUMN NAMES
$sql = "SHOW COLUMNS FROM $table_name";
if (!$res = mysql_query($sql))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
if (mysql_num_rows($res) == 0)
{
    die("WTF? $table_name HAS NO COLUMNS");
}
else
{
    // MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
    while ($show_columns = mysql_fetch_assoc($res))
    {
        $my_columns[] = $show_columns["Field"];
    }
    // var_dump($my_columns); ACTIVATE THIS TO SEE THE COLUMNS
}

// WRITE THE COLUMN NAMES TO THE CSV
if (!fputcsv($fp, $my_columns)) die('DISASTER');

// GET THE ROWS OF DATA
$sql = "SELECT * FROM $table_name";
$res = mysql_query($sql);
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}

// ITERATE OVER THE DATA SET
while ($row = mysql_fetch_row($res))
{
    // WRITE THE COMMA-SEPARATED VALUES.  MAN PAGE http://php.net/manual/en/function.fputcsv.php
    if (!fputcsv($fp, $row)) die('CATASTROPHE');
}

// ALL DONE
fclose($fp);

// SHOW THE CLIENT A LINK
echo "<p><a href=\"$csv\">$csv</a></p>\n";

Open in new window

0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
pda4meAuthor Commented:
xterm, this looks good...can you show me where to insert it into the existing script and replace the existing functionality where it currently asks to download instead?
0
 
xtermCommented:
Sure, comment out your lines 34-37 and paste my code in immediately in it's place.

Then change my line #1 to:
$my_local_file=$filename".csv";

Open in new window

0
 
xtermCommented:
Bah, sorry, that should read:
$my_local_file=$filename.".csv";

Open in new window

0
 
pda4meAuthor Commented:
Awesome, so close! how do i cd to a directory like /httpdocs/csv/ prior to uploading the file?
0
 
xtermCommented:
After Line 6 of my code, insert the following:
if (ftp_chdir($conn_id, "/httpdocs/csv")) {
    echo "Current directory is now: " . ftp_pwd($conn_id) . "\n";
} else { 
    echo "Couldn't change directory\n";
}

Open in new window

0
 
pda4meAuthor Commented:
Okay, that worked...Now I'm getting a There was a problem while uploading export error?  Is it possible to get a better description of the problem?
0
 
xtermCommented:
if (ftp_fput($conn_id, $file, $fp, FTP_BINARY)) {
      echo "Successfully uploaded $file\n";
    } else {
      echo "There was a problem while uploading $file\n";
    }


The word $file in these debug statements should be changed to $my_local_file, my mistake.  That will at least show the problem a little more descriptively.   Are you sure you have upload permission on the FTP server?  What happens if you connect manually and try to upload that file?
0
 
xtermCommented:
I see the problem.

if (ftp_fput($conn_id, $file, $fp, FTP_BINARY)) {

should be

if (ftp_fput($conn_id, $my_local_file, $fp, FTP_BINARY)) {


Right now, it's attempting to put a file that doesn't exist.
0
 
pda4meAuthor Commented:
No such luck. Still getting, There was a problem while uploading export_2011-10-27_17-46.csv. Here is what I have:

$conn_id = ftp_connect($ftp_server) or die("Couldn't connect to $ftp_server");
if (@ftp_login($conn_id, $ftp_user, $ftp_pass)) {
    echo "Connected as $ftp_user@$ftp_server\n";
if (ftp_chdir($conn_id, "/httpdocs/")) {
    echo "Current directory is now: " . ftp_pwd($conn_id) . "\n";
} else { 
    echo "Couldn't change directory\n";
}	
    $fp = fopen($my_local_file, 'r');
    if (ftp_fput($conn_id, $my_local_file, $fp, FTP_BINARY)) {
      echo "Successfully uploaded $my_local_file\n";
    } else {
      echo "There was a problem while uploading $my_local_file\n";
    }
    fclose($fp);
    ftp_close($conn_id);
} else {
    echo "Couldn't connect as $ftp_user\n";
}

Open in new window

0
 
xtermCommented:
Okay, I see the problem - in your previous script you never actually wrote the file to disk, so there's nothing to upload.

Change this line:
$my_local_file="/tmp/".$filename.".csv";

Add these right after it:
$fp=fopen("$my_local_file","w");
fputs($filename,"$csv_output");
fclose($fp);

Now the file exists in /tmp so it can be uploaded.  Make sure that it is there, and that the name looks fine.

After you're sure it works, after the FTP upload you can put (after the Successfully uploaded line):

unlink($my_local_file);

That will clean up the filesystem.
0
 
pda4meAuthor Commented:
so close...I verified it is creating a file but the file is empty.  it then errors out with:

There was a problem while uploading /tmp/export_2011-10-27_18-39.csv
0
 
pda4meAuthor Commented:
here is the latest code:

$conn_id = ftp_connect($ftp_server) or die("Couldn't connect to $ftp_server");
if (@ftp_login($conn_id, $ftp_user, $ftp_pass)) {
    echo "Connected as $ftp_user@$ftp_server\n";
if (ftp_chdir($conn_id, "/httpdocs/")) {
    echo "Current directory is now: " . ftp_pwd($conn_id) . "\n";
} else { 
    echo "Couldn't change directory\n";
}	
    $fp = fopen($my_local_file, 'r');
    if (ftp_fput($conn_id, $my_local_file, $fp, FTP_BINARY)) {
      echo "Successfully uploaded $my_local_file\n";
	  unlink($my_local_file);
    } else {
      echo "There was a problem while uploading $my_local_file\n";
    }
    fclose($fp);
    ftp_close($conn_id);
} else {
    echo "Couldn't connect as $ftp_user\n";
}

Open in new window

0
 
xtermCommented:
Okay, first thing, see if you can manually ftp in and upload a file to /httpdocs - because even if the file is empty, it should've uploaded it if you have permissions on that remote folder.  Sounds like you do not.

Let me look at why the file might be empty (despite that not really being part of your initial question - I thought the file already existed, in which case what I've provided you should work fine.)
0
 
xtermCommented:
Can you also paste the code lines prior to the ftp connection?  aka, the ones that fopen the temporary file and then dump the csv_output into it?
0
 
pda4meAuthor Commented:
It was a server permissions issue, thanks for the help!
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 10
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now