[SOLVED] PHP Curl - download csv file, import to mysql

[quasiman]

I have several of these working correctly, but for some reason this one isn't....I'm probably missing something small, but since I can't figure it out - maybe there's some bad coding here that someone can point out?  At this point I feel lucky that the other 6 scripts are working fine!

The script truncates the db table, downloads a fresh copy of the csv, then imports it to the table, and emails me the results.

The table is truncated fine, a fresh copy is downloaded (I triple checked), but the table is never imported into.

 

As you can see, I'm displaying all errors for this...and I'm not getting any.  The file only has about 25 records.

Here's my code, I'll post the sql structure after it:

<?php
ini_set('display_errors', 1);
ini_set('log_errors', 1);
ini_set('error_log', dirname(__FILE__) . '/error_log.txt');
error_reporting(E_ALL);
$headers  = 'MIME-Version: 1.0' . "\r\n";
$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
$headers .= 'From: Section Imported<sections@myhost.com>' . "\r\n";

$to = "myemailaddress@myhost.com";
$subject = "Section Data Imported";

include ('dbconnect.php');
$conn = mysql_connect($host, $user, $pass) or die("Could not connect to host.");
mysql_select_db($database, $conn) or die("Could not find database.");
$sql = "TRUNCATE TABLE `zdata_sectionfile`";
mysql_query($sql);
$body = "Table emptied<br>";

$ch = curl_init('ftp://ftplocation/SectionFile.txt');//Here is the file we are downloading
$fp = fopen('SectionFile.txt','w');
curl_setopt($ch, CURLOPT_TIMEOUT, 50);
curl_setopt($ch, CURLOPT_USERPWD, "username:password");
curl_setopt($ch, CURLOPT_FILE, $fp);
curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
// Execute
curl_exec($ch);
// Check if any error occured
if(!curl_errno($ch))
{
$info = curl_getinfo($ch);
$body .= 'Took ' . $info['total_time'] . ' seconds to send a request to ' . $info['url'] . "<br>";
  // 'OK' status; format $output data if necessary here:
$query      = "LOAD DATA LOCAL INFILE 'SectionFile.txt' INTO TABLE zdata_sectionfile
		FIELDS TERMINATED BY '\t'
		OPTIONALLY ENCLOSED BY '\"'
		LINES TERMINATED BY '\r\n'
		(SectionShort,SectionLong)"; 
$result = mysql_query($query);
if (!$result) {
    die('Invalid query: ' . mysql_error());
} else {$body .= "import success";}
mysql_close($linkID);
}
// then return or display the single string $output
curl_close($ch);
fclose($fp);

if (mail($to, $subject, $body, $headers)) {
  echo("<p>Message successfully sent!</p>");
} else {
  echo("<p>Message delivery failed...</p>");
}
?>

 

And the sql from phpMyAdmin:

CREATE TABLE IF NOT EXISTS `zdata_sectionfile` (
  `ID` int(11) NOT NULL auto_increment,
  `SectionShort` varchar(40) NOT NULL,
  `SectionLong` varchar(100) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

[PFMaBiSmAd]

What exactly does the 'body' of the email contain so that we would have a clue where to look for a problem?

[quasiman]

It acts as if the script worked...

Table emptied
Took 16.77818 seconds to send a request to ftp://ftplocation/SectionFile.txt
import success 

[PFMaBiSmAd]

Based on that, the query is being executed without error.

 

The specification for the data -

 

        FIELDS TERMINATED BY '\t'

        OPTIONALLY ENCLOSED BY '\"'

        LINES TERMINATED BY '\r\n'

        (SectionShort,SectionLong)";

 

probably does not match what is in the file (I would guess that most likely the line ending is not \r\n)

[quasiman]

Thanks for the reply,

I tried following your thinking, and removed the LINES TERMINATED...

 

And added to the header,

ini_set('auto_detect_line_endings', true)

 

same results...

 

So then I thought maybe the table is corrupted somehow.  I deleted the table, and created it again....still the same results.  Next I tried using phpMyAdmin and imported the file with no problem.  It defeats the purpose of writing this automated script, but I wanted to be sure nothing else is going on.

Fields terminated by: \t

Fields enclosed by: ""

Fields escaped by: \

Lines terminated by: auto

Column names: SectionShort,SectionLong

 

What else can I be missing?

[quasiman]

I fixed it!

 

Removed everything except for the database import, and it worked fine.

include ('dbconnect.php');
$linkID = mysql_connect($host, $user, $pass) or die("Could not connect to host.");
mysql_select_db($database, $linkID) or die("Could not find database.");
$sql = "TRUNCATE TABLE `zdata_sectionfile`";
mysql_query($sql);

$query      = "LOAD DATA LOCAL INFILE 'SectionFile.txt' INTO TABLE zdata_sectionfile
		FIELDS TERMINATED BY '\t'
		OPTIONALLY ENCLOSED BY '\"'
		LINES TERMINATED BY '\r\n'
		(SectionShort,SectionLong)"; 
$result = mysql_query($query);
if (!$result) {
    die('Invalid query: ' . mysql_error());
} else {echo "import success";}
mysql_close($linkID);

 

So that said to me, the file is still in use, or unreadable probably...

So I moved the curl_close and fclose above the sql import script, and all is well!

 

$body .= 'Took ' . $info['total_time'] . ' seconds to send a request to ' . $info['url'] . "<br>";
curl_close($ch);
fclose($fp);
  // 'OK' status; format $output data if necessary here:
$query      = "LOAD DATA LOCAL INFILE 'SectionFile.txt' INTO TABLE zdata_sectionfile......