another file import question

General PHP Help
#1

ok, I have this script that runs off of a cronjob. it’s supposed to grab a list of files, figure out what goes where and then import it into the given db table. it does work, to an extent. I don’t know why, but it keeps saying the file was unlinked when its sitting right there in the folder. below is the script.
[php]<?php
ini_set(“magic_quotes_gpc”, 0);
mysql_connect(‘localhost’, ‘xxxx’, ‘xxxx’) or die(mysql_error());
mysql_select_db(‘venzo_primary’) or die(mysql_error());

define(‘APP_PATH’, ‘/home/venzo/public_html/admin/’);

function left($string, $count){
return substr($string, 0, $count);
}

//$file_list=;
foreach(glob(APP_PATH.“files/*.txt”) as $file) {
$filen = basename($file);

if(left($filen, 12) == "S_W_80045583") {
	$path = APP_PATH.'trends/itunes';
	$companyTbl = 'venzo_itunes_trends';
	$ReportFile = APP_PATH."files/".$filen;
	copy($ReportFile, $path."/".$filen);
	$process = 1;
} elseif(left($filen, 12) == "S_D_80045583") {
	$path = APP_PATH.'trends/itunes';
	$companyTbl = 'venzo_itunes_dtrends';
	$ReportFile = APP_PATH."files/".$filen;
	copy($ReportFile, $path."/".$filen);
	$process = 1;
} elseif(left($filen, 12) == "S_W_85409067") {
	$path = APP_PATH.'trends/app';
	$companyTbl = 'venzo_app_trends';
	$ReportFile = APP_PATH."files/".$filen;
	copy($ReportFile, $path."/".$filen);
	$process = 1;
} elseif(left($filen, 12) == "S_D_85409067") {
	$path = APP_PATH.'trends/app';
	$companyTbl = 'venzo_app_dtrends';
	$ReportFile = APP_PATH."files/".$filen;
	copy($ReportFile, $path."/".$filen);
	$process = 1;
}
//echo $ReportFile."<br />";
if($process == 1) {
	
	$fcontents = file($ReportFile, FILE_IGNORE_NEW_LINES); //line 44
    
	/*check the table exists*/
	$qry = current(mysql_fetch_row(mysql_query("SELECT DATABASE()")));
	$qry = mysql_query("SHOW TABLES FROM `{$qry}` LIKE '{$companyTbl}'") or die(mysql_error());
	if(mysql_num_rows($qry) < 1) {
		unlink($path."/".$file);
		die("Table `{$companyTbl}` for '{$company}' does not exist!");
	}
    
	/*pull the field list out of the DB*/
	$qry = mysql_query("SHOW FIELDS FROM `{$companyTbl}`") or die(mysql_error());
	//echo $company."<br />";

	$sqlFieldNum = mysql_num_rows($qry);
	while($field = mysql_fetch_assoc($qry)) {
		$sqlFields[] = strtolower($field['Field']);
	}
	
	/*check the field list in the report and the field list in the database match*/
	if($sqlFieldNum != count($fieldArr = explode("\t", $fcontents[0]))) {
		foreach($fieldArr as $key => $value) {
			$fields[0][$key] = trim(str_replace('/', '_', strtolower($value)));
			$fields[1][$key] = trim(str_replace(array('/', ' '), '_', strtolower($value)));
			$fields[2][$key] = trim(str_replace(array('/', ' '), array('_', ''), strtolower($value)));
		}
		$tfields = $fields[0];
     
		/*check for fields in the DB that are not in the report*/
		for($a = 0; $a < count($sqlFields); $a++) {
			if($sqlFields[$a] != $fields[0][$a] && $sqlFields[$a] != $fields[1][$a] && $sqlFields[$a] != $fields[2][$a]) {
				$error = true;
			} else {
				unset($tfields[$a]);
			}
		}
     
		/*check for fields in the report that are not in the DB*/
		if(count($tfields) > 0) {
			$error = true;
		}
	}
	/*if there is an error, stop here and delete the file*/
	if($error == true) {
		unlink($path."/".$filen); //line 88
		die();
	}
   
	/*for each record in the report...*/
	for($i = 1; $i < count($fcontents)-3; $i++) {
		$line = $fcontents[$i];
		$arr = explode("\t", $line);
      
		/*santitise the values*/
		foreach($arr as $key => $value) {
			$arr[$key] = trim(mysql_real_escape_string($value));
		}
     
		if(strtolower($arr[0]) != "total") {
		/*number of fields in the DB exceeds number of fields in the record*/
			if($sqlFieldNum > count($arr)) {
				$error[$i] = true;
				for($a = 0; $a < count($sqlFields); $a++) {
					$oops[0] = "{$a}) MYSQL: {$a} => {$sqlFields[$a]} - Report: {$a} => {$arr[$a]}";
				}
				$oops[1] = "# of fields in the table (" . $sqlFieldNum . ") is greater than the # of fields in the report (" . count($arr) . ")!";
         	
				/*number of fields in the record exceeds number of fields in the DB*/
			} else if($sqlFieldNum < count($arr)) {
				$error[$i] = true;
				for($a = 0; $a < count($arr); $a++) {
					$oops[3] = "{$a}) MYSQL: {$a} => {$sqlFields[$a]} - Report: {$a} => {$arr[$a]}";
				}
				$oops[4] = "# of fields in the report (" . count($arr) . ") is greater than the # of fields in the table (" . $sqlFieldNum . ")!";
			}
        
			/*if there is no error insert the record into the table else continue onto the next record*/
			if($error[$i] != true && !$oops) {
				$sql = "INSERT INTO {$companyTbl}(" . implode(', ', $sqlFields) . ") VALUES ('" . implode("', '", $arr) . "')";
				$ins = mysql_query($sql);
				
				if($ins) {
					unlink($file); //line 126
				} else {
					echo "Problem with $filen";
				}
				
			} else {
				$oops[5] = "Record {$i} not inserted!";
				foreach($oops AS $mes) {
					echo $mes."<br />";
				}
			}
		}
	}

	unset($sqlFields);
	unset($fcontents);
	unset($line);

	/*if there is an error, stop here and delete the file*/
	if(count($error) > 0) {
		unlink($path."/".$file);
		die();
	}
}

}
?>[/php] and these are the errors i’m getting from the cronjob output.


Warning: unlink(/home/venzo/public_html/admin/files/S_W_85409067_20130609.txt): No such file or directory in /home/venzo/public_html/admin/import_files_trends.php on line 126

Warning: unlink(/home/venzo/public_html/admin/files/S_W_85409067_20130609.txt): No such file or directory in /home/venzo/public_html/admin/import_files_trends.php on line 126

Warning: unlink(/home/venzo/public_html/admin/files/S_W_85409067_20130609.txt): No such file or directory in /home/venzo/public_html/admin/import_files_trends.php on line 126

Warning: unlink(/home/venzo/public_html/admin/files/S_W_85409067_20130609.txt): No such file or directory in /home/venzo/public_html/admin/import_files_trends.php on line 126

Warning: unlink(/home/venzo/public_html/admin/files/S_W_85409067_20130609.txt): No such file or directory in /home/venzo/public_html/admin/import_files_trends.php on line 126

Warning: unlink(/home/venzo/public_html/admin/files/S_W_85409067_20130609.txt): No such file or directory in /home/venzo/public_html/admin/import_files_trends.php on line 126

Warning: file(/home/venzo/public_html/admin/files/S_W_85409067_20130609.txt): failed to open stream: No such file or directory in /home/venzo/public_html/admin/import_files_trends.php on line 44

Warning: unlink(/home/venzo/public_html/admin/trends/app/spotify-track-for-venzodigital-20134.txt): No such file or directory in /home/venzo/public_html/admin/import_files_trends.php on line 88
The error messages were truncated, the whole message is several lines long. I put the relevant line numbers in a comment so you'll know what's what.
#2

i’m not asking for someone to redo the script, just tell me what I’m doing wrong so I can get this thing fixed once and for all. i’m sick of having to repair it every month.

#3

from the look of your error report it looks like you’re trying to delete a file from /home/venzo/public_html/admin/files/ directory.

However, upon reading through your code, I would expect to find the file in /home/venzo/public_html/admin/files/trends/app as you appended this to the path.
[php]$path = APP_PATH.‘trends/app’;[/php]

Same for other files.

Let me know if that helps.
Red :wink:

#4

Thanks for replying :slight_smile:

To clarify the paths, there are to file locations.

$path = APP_PATH.‘trends/app’; is the save path, where the file is supposed to go once it imported.
APP_PATH.“files/” is where the file is currently at.

I have a cronjob running once a day and once a week that download trend data, it downloads it files. from there, another cronjob runs, and once the file has been imported, its supposed to copy it the right place and then delete it. The only time its supposed to delete it from the sales or trends folder is when something goes wrong, because it checks to see if the file exists.7

#5

Have you checked read permissions are adequate?

#6

Can’t be a permissions problem, its at 777. I just don’t get why its saying the file doesn’t exist when it does. I’ve looked at the folder while the file is being imported and it never gets deleted, so I shouldn’t be getting file not found errors.

#7

I’ve about had it with this script. This is what I have now [php]<?php
ini_set(“magic_quotes_gpc”, 0);
error_reporting(-1);
mysql_connect(‘localhost’, ‘xxx’, ‘xxxx’) or die(mysql_error());
mysql_select_db(‘venzo_test’) or die(mysql_error());

define(‘APP_PATH’, ‘/home/venzo/public_html/admin/’);
define(‘APP’, ‘/home/venzo/public_html/admin/trends/app’);
define(‘MUSIC’, ‘/home/venzo/public_html/admin/trends/itunes’);

function left($string, $count){
return substr($string, 0, $count);
}

$ReportFile = array();
$tables = array();

foreach(glob(APP_PATH.“files/*.txt”) as $file) {

$filen = basename($file);

if(left($filen, 12) == "S_W_80045583") {
	$path = APP_PATH.'trends/itunes';
	$tables[] = 'venzo_itunes_trends';
	$ReportFile[] = APP_PATH."files/".$filen;
	if(is_file(MUSIC."/$filen")) {
		$process = 0;
	}
	$process = 1;
} elseif(left($filen, 12) == "S_D_80045583") {
	$path = APP_PATH.'trends/itunes';
	$tables[] = 'venzo_itunes_dtrends';
	$ReportFile[] = APP_PATH."files/".$filen;
	if(is_file(MUSIC."/$filen")) {
		$process = 0;
	}
	$process = 1;
} elseif(left($filen, 12) == "S_W_85409067") {
	$path = APP_PATH.'trends/app';
	$companyTbl = 'venzo_app_trends';
	$ReportFile[] = APP_PATH."files/".$filen;
	if(is_file(APP."/$filen")) {
		$process = 0;
	}
	$process = 1;
} elseif(left($filen, 12) == "S_D_85409067") {
	$path = APP_PATH.'trends/app';
	$tables[] = 'venzo_app_dtrends';
	$ReportFile[] = APP_PATH."files/".$filen;
	if(is_file(APP."/$filen")) {
		$process = 0;
	}
	$process = 1;
}

}

if($process == 1) {

foreach ($tables as $companyTbl) {
	foreach($ReportFile as $report) {

		echo $report."<br />";
		echo "table: $companyTbl<br />";
	
		$fcontents = file($report, FILE_IGNORE_NEW_LINES);
		
		//echo "<pre>";
		//print_r($fcontents);
		//echo "</pre>";
    
		/*check the table exists*/
		$qry = current(mysql_fetch_row(mysql_query("SELECT DATABASE()")));
		$qry = mysql_query("SHOW TABLES FROM `{$qry}` LIKE '{$companyTbl}'") or die(mysql_error());
	
		if(mysql_num_rows($qry) < 1) {
			unlink($path."/".$file);
			die("Table `{$companyTbl}` for '{$company}' does not exist!");
		}
    
		/*pull the field list out of the DB*/
		$qry = mysql_query("SHOW FIELDS FROM `{$companyTbl}`") or die(mysql_error());

		$sqlFieldNum = mysql_num_rows($qry);
		while($field = mysql_fetch_assoc($qry)) {
			
			$sqlFields[] = strtolower($field['Field']);
		}
	
		/*check the field list in the report and the field list in the database match*/
		if($sqlFieldNum != count($fieldArr = explode("\t", $fcontents[0]))) {
			foreach($fieldArr as $key => $value) {
				$fields[0][$key] = trim(str_replace('/', '_', strtolower($value)));
				$fields[1][$key] = trim(str_replace(array('/', ' '), '_', strtolower($value)));
				$fields[2][$key] = trim(str_replace(array('/', ' '), array('_', ''), strtolower($value)));
			}
			$tfields = $fields[0];
     
			/*check for fields in the DB that are not in the report*/
			for($a = 0; $a < count($sqlFields); $a++) {
				if($sqlFields[$a] != $fields[0][$a] && $sqlFields[$a] != $fields[1][$a] && $sqlFields[$a] != $fields[2][$a]) {
					$error = true;
				} else {
					unset($tfields[$a]);
				}
			}
     
			/*check for fields in the report that are not in the DB*/
			if(count($tfields) > 0) {
				$error = true;
			}
		}
		/*if there is an error, stop here and delete the file*/
		if($error == true) {
			//unlink($path."/".$filen);
			die();
		}
   
		/*for each record in the report...*/
		for($i = 1; $i < count($fcontents)-3; $i++) {
			$line = $fcontents[$i];
			$arr = explode("\t", $line);
      
			/*santitise the values*/
			foreach($arr as $key => $value) {
				$arr[$key] = trim(mysql_real_escape_string($value));
			}
     
			if(strtolower($arr[0]) != "total") {
			/*number of fields in the DB exceeds number of fields in the record*/
				if($sqlFieldNum > count($arr)) {
					$error[$i] = true;
					for($a = 0; $a < count($sqlFields); $a++) {
						echo "{$a}) MYSQL: {$a} => {$sqlFields[$a]} - Report: {$a} => {$arr[$a]}";
						
					}
					echo "# of fields in the table (" . $sqlFieldNum . ") is greater than the # of fields in the report (" . count($arr) . ")!";
    	     	
					/*number of fields in the record exceeds number of fields in the DB*/
					} else if($sqlFieldNum < count($arr)) {
						$error[$i] = true;
						for($a = 0; $a < count($arr); $a++) {
							echo "{$a}) MYSQL: {$a} => {$sqlFields[$a]} - Report: {$a} => {$arr[$a]}";
						}
						echo "# of fields in the report (" . count($arr) . ") is greater than the # of fields in the table (" . $sqlFieldNum . ")!";
					}
        
				/*if there is no error insert the record into the table else continue onto the next record*/
				if($error[$i] != true) {
					$sql = "INSERT INTO {$companyTbl}(" . implode(', ', $sqlFields) . ") VALUES ('" . implode("', '", $arr) . "')";
					$ins = mysql_query($sql);
					echo "$filen has been imported<br>";
					if($ins) {
						copy($report, $path."/".$filen);
						unlink($file);
					} else {
						echo "Problem with $filen<br>";
					}
				} else {
					echo "Record {$i} not inserted!";
				}
			}
		}

		unset($sqlFields);
		unset($fcontents);
		unset($line);
		unset($companyTbl);
		unset($report);

		/*if there is an error, stop here and delete the file*/
		if(count($error) > 0) {
			unlink($path."/".$file);
			die();
		}
	}
}

}
?> [/php]

Its only doing 1 table when it should be doing all of them. I moved the filenames and table names into arrays since I couldn’t get it to stop giving duplicate file names. If I do a print_r of those variables, it gives me the correct data.

I’ve been at this for hours now, i’m really stressed out and ready to call it quits. I can’t see what i’m doing wrong or why i’m getting entirely to many unlink error messages when I should only get 1.

#8

ok, well I was able to get this thing about 98% fixed. I just have one more problem to solve. Now I don’t know what its doing it, but this is what its doing - its not processing the last file in the array. It’ll do 20 files, but the 21st file will fail every single time and I can’t figure out why, but I need to get this solved.

The updated code is below
[php]<?php
mysql_connect(‘localhost’, ‘xxxx’, ‘xxxx’) or die(mysql_error());
mysql_select_db(‘venzo_primary’) or die(mysql_error());

define(‘APP_PATH’, ‘/home/venzo/public_html/admin/’);
define(‘APP’, ‘/home/venzo/public_html/admin/trends/app’);
define(‘MUSIC’, ‘/home/venzo/public_html/admin/trends/itunes’);

function left($string, $count){
return substr($string, 0, $count);
}

$data = array();

foreach(glob(APP_PATH.“files/*.txt”) as $file) {

$filen = basename($file);

if(left($filen, 12) == "S_W_80045583") {
	$tables = 'venzo_itunes_trends';
	$ReportFile = APP_PATH."files/".$filen;
	if(!is_file(MUSIC."/$filen")) {
		$data[] = array("path"=>MUSIC, "table"=>$tables, "file"=>$ReportFile);
	} else {
		echo "$filen already exists<br />";
	}
} elseif(left($filen, 12) == "S_D_80045583") {
	$tables = 'venzo_itunes_dtrends';
	$ReportFile = APP_PATH."files/".$filen;
	if(!is_file(MUSIC."/$filen")) {
		$data[] = array("path"=>MUSIC, "table"=>$tables, "file"=>$ReportFile);
	} else {
		echo "$filen already exists<br />";
	}
} elseif(left($filen, 12) == "S_W_85409067") {
	$tables = 'venzo_app_trends';
	$ReportFile = APP_PATH."files/".$filen;
	if(!is_file(APP."/$filen")) {
		$data[] = array("path"=>APP, "table"=>$tables, "file"=>$ReportFile);
	} else {
		echo "$filen already exists<br />";
	}
} elseif(left($filen, 12) == "S_D_85409067") {
	$tables = 'venzo_app_dtrends';
	$ReportFile = APP_PATH."files/".$filen;
	if(!is_file(APP."/$filen")) {
		$data[] = array("path"=>APP, "table"=>$tables, "file"=>$ReportFile);
	} else {
		echo "$filen already exists<br />";
	}
}

}

foreach($data as $key => $value) {

echo "<pre>";
print_r($value);
echo "</pre>";

$report = $value['file'];
$companyTbl = $value['table'];
$path = $value['path'];
$filen = basename($report);

$fcontents = file($report, FILE_IGNORE_NEW_LINES);
		
/*check the table exists*/
$qry = current(mysql_fetch_row(mysql_query("SELECT DATABASE()")));
$qry = mysql_query("SHOW TABLES FROM `{$qry}` LIKE '{$companyTbl}'") or die(mysql_error());

if(mysql_num_rows($qry) < 1) {
	//unlink($path."/".$file);
	die("Table `{$companyTbl}` for '{$company}' does not exist!");
}
   
/*pull the field list out of the DB*/
$qry = mysql_query("SHOW FIELDS FROM `{$companyTbl}`") or die(mysql_error());

$sqlFieldNum = mysql_num_rows($qry);
while($field = mysql_fetch_assoc($qry)) {
	$sqlFields[] = strtolower($field['Field']);
}
	
/*check the field list in the report and the field list in the database match*/
if($sqlFieldNum != count($fieldArr = explode("\t", $fcontents[0]))) {
	foreach($fieldArr as $key => $value) {
		$fields[0][$key] = trim(str_replace('/', '_', strtolower($value)));
		$fields[1][$key] = trim(str_replace(array('/', ' '), '_', strtolower($value)));
		$fields[2][$key] = trim(str_replace(array('/', ' '), array('_', ''), strtolower($value)));
	}
	$tfields = $fields[0];
    
	/*check for fields in the DB that are not in the report*/
	for($a = 0; $a < count($sqlFields); $a++) {
		if($sqlFields[$a] != $fields[0][$a] && $sqlFields[$a] != $fields[1][$a] && $sqlFields[$a] != $fields[2][$a]) {
			$error = true;
		} else {
			unset($tfields[$a]);
		}
	}
    
	/*check for fields in the report that are not in the DB*/
	if(count($tfields) > 0) {
		$error = true;
	}
}
/*if there is an error, stop here and delete the file*/
// this should be deleting it out of the destination folder
if($error == true) {
	unlink($path."/".$filen);
	die();
}
  
/*for each record in the report...*/
for($i = 1; $i < count($fcontents); $i++) {
	$line = $fcontents[$i];
	$arr = explode("\t", $line);
   
	/*santitise the values*/
	foreach($arr as $key => $value) {
		$arr[$key] = trim(mysql_real_escape_string($value));
	}
   
	if(strtolower($arr[0]) != "total") {
		/*number of fields in the DB exceeds number of fields in the record*/
		if($sqlFieldNum > count($arr)) {
			$error[$i] = true;
			for($a = 0; $a < count($sqlFields); $a++) {
				echo "{$a}) MYSQL: {$a} => {$sqlFields[$a]} - Report: {$a} => {$arr[$a]}";
			}
			echo "# of fields in the table (" . $sqlFieldNum . ") is greater than the # of fields in the report (" . count($arr) . ")!";
  	     	
			/*number of fields in the record exceeds number of fields in the DB*/
		} else if($sqlFieldNum < count($arr)) {
			$error[$i] = true;
			for($a = 0; $a < count($arr); $a++) {
				echo "{$a}) MYSQL: {$a} => {$sqlFields[$a]} - Report: {$a} => {$arr[$a]}";
			}
			echo "# of fields in the report (" . count($arr) . ") is greater than the # of fields in the table (" . $sqlFieldNum . ")!";
		}
      
		/*if there is no error insert the record into the table else continue onto the next record*/
		if(isset($error[$i]) != true) {
			
			$sql = "INSERT INTO {$companyTbl}(" . implode(', ', $sqlFields) . ") VALUES ('" . implode("', '", $arr) . "')";
			$ins = mysql_query($sql) or die(mysql_error());
			
			if($ins) {
				echo "$filen has been imported<br>";
				copy($report, $path."/".$filen);
				if(is_file($file)) {
					unlink($file);
				}
			} else {
				echo "Problem with $filen<br>";
			}
		} else {
			echo "Record {$i} not inserted!";
		}
	}
}
	
/*if there is an error, stop here and delete the file*/
if(count($error) > 0) {
	unlink($path."/".$file);
	die();
}

unset($sqlFields, $fcontents, $line);
unset($process, $report, $companyTbl, $path, $filen, $file);

}
?> [/php]
When it fails, it’s doing so at the first $error detector, which is causing the other issues. I’ve already made sure the file doesn’t have any problems, so I know that’s not it.

Any help would be appreciated.

Sponsor our Newsletter | Privacy Policy | Terms of Service