scriptygoddess

I needed to grab a (CSV) file via FTP and import it into a database. The part that I thought would be the hardest (the FTP part) was actually very simple. Once again, PHP saves the day with incredibly powerful built in functionality. Here is the simple code that will connect to an FTP server and download a file:

<?
//FTP Hostname
$ftp_server = "WWW.DOMAIN.COM";
$ftp_username = "ENTER-FTP-USERNAME-HERE";
$ftp_userpassword = "ENTER-FTP-PASSWORD-HERE";

//This is the name and location of the file you want downloaded to your local server
$local_file = "/home/youraccount/public_html/foldername/file.txt";

//This is the name and location on your FTP server
/***
note that the starting directory is what would be available when you logged in via an FTP program. This was one thing that caused me a bit of confusion - because I had originally been placing the "full server" path here - but that's not what I see when I FTP in... notice the difference between this path and the "local path" above.
***/
$server_file = "/public_html/remotefolder/filename.txt";

//connect to the server
$conn_id = ftp_connect($ftp_server) or die ("I couldn't connect to the ftp server");
//login to the server
$login_result = ftp_login($conn_id, $ftp_username, $ftp_userpassword);
//get the file!
if (ftp_get($conn_id, $local_file, $server_file, FTP_ASCII)) {
//ftp_get can get files in ascii or binary,
//binary would have FTP_BINARY instead of FTP_ASCII
echo "got it!!";
} else {
echo "nope";
}

//close the FTP connection
ftp_close($conn_id);
?>

Also see the php manual pages regarding this:
ftp_connect, ftp_login, ftp_get, ftp_close

To then take that file and import it into a database – here is the code that does that:

I have a file outside the root directory that handles my database connection – all it has in it is this:

<?
$dbconnection = mysql_connect ("localhost", "DATABASE-USERNAME", "DATABASE-PASSWORD") or die ('I cannot connect to the database because: ' . mysql_error());
?>

Then the "meat" of the code is here:

<?php
include("/home/PATH/TOMY/DATABASECONNECTIONPAGE.php");
mysql_select_db ("DATABASENAME", $dbconnection) or die("Couldn't open database: ".mysql_error());
$CSVFile = "/home/youraccount/public_html/foldername/file.txt";
if (file_exists($CSVFile)) {
/**
Originally had it deleting everything in the table and replacing it with the contents of the CSV - it doesn't do that anymore, but just for argument's sake I've left it below but commented out the line
**/
//mysql_query("DELETE FROM TABLENAMEHERE");
$row = 1;
$handle = fopen ("$CSVFile","r");
fgetcsv ($handle, 1000, ",");
while ($data = fgetcsv ($handle, 1000, ",")) {
print $data[0] . "\n";
mysql_query("INSERT INTO TABLENAMEHERE(FIELDNAME) VALUES ('".$data[0]."')");
printf ("Last inserted record has id %d\n", mysql_insert_id());
echo "\n";
echo "\n";
//I have this printing stuff to the screen just so I know the it's working...
}
fclose ($handle);

//delete the local file when we're done
if (unlink($CSVFile)) {
echo "file successfully deleted";
} else {
echo "file could not be deleted!!";
}
} else {
echo "No file in folder!!";
}
?>