Jump to content

importing multiple files into different mysql tables

richei

By richei
in PHP Coding Help

 Share
Go to solution Solved by mac_gyver,

Recommended Posts

richei Member

richei

Posted
Posted

ok, I've been at this for months and each time I think I get it fixed, it ends up broken again. i'm tired of looking at it, thinking I know what's wrong when I really don't have a clue.  This script was originally made to import files that were manually uploaded, then it was changed to handle multiple files in a designated folder, running from a cronjob.  I know it needs to be in a loop, but what I have isn't working and its giving me fits with all the error messages:

 

 

 

Notice: Undefined variable: error in /home/venzo/public_html/admin/import_files_trends.php on line 121

Notice: Undefined variable: error in /home/venzo/public_html/admin/import_files_trends.php on line 156
S_D_80045583_20130616.txt has been imported

Notice: Undefined variable: error in /home/venzo/public_html/admin/import_files_trends.php on line 156
S_D_80045583_20130616.txt has been imported

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

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

Notice: Undefined variable: error in /home/venzo/public_html/admin/import_files_trends.php on line 156
S_D_80045583_20130616.txt has been imported

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

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

Notice: Undefined variable: error in /home/venzo/public_html/admin/import_files_trends.php on line 156
S_D_80045583_20130616.txt has been imported

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

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

Notice: Undefined variable: error in /home/venzo/public_html/admin/import_files_trends.php on line 156
S_D_80045583_20130616.txt has been imported

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

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

Notice: Undefined variable: error in /home/venzo/public_html/admin/import_files_trends.php on line 156
S_D_80045583_20130616.txt has been imported

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

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

Notice: Undefined variable: error in /home/venzo/public_html/admin/import_files_trends.php on line 156
S_D_80045583_20130616.txt has been imported

Lots of those same errors.

 

I'm sure there's probably a much simpler way of doing this, but at this point, i'd just be happy if it worked as it should.  The files are tab delimited txt files.  Its important to me to have the error reporting since apple has been randomly changing the file formats on me, adding columns, removing columns, adding rows at the end, all without prior notice.

 

So here's what I have:

<?php
ini_set("magic_quotes_gpc", 0);
error_reporting(-1);
mysql_connect('xxxx', 'xxxx', 'xxxx') or die(mysql_error());
mysql_select_db('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';
  $tables[] = '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 "<pre>";
   print_r($tables);
   print_r($ReportFile);
   echo "</pre>";
   $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*/
   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();
   }
  }
 } //should end the foreach loop
}
?>

I'm not beneath begging, I wouldn't be here if I didn't need the help since I like to troubleshoot these scripts on my own, but i'm completely stumped and have another huge project to work on.  I thank you in advance for any direction!

PravinS Member

PravinS

Posted
Posted

You can disable notice error through php.ini file or you need to declare every variable at top.

 

And regarding warning errors (line 161,162), you need to give read, write permission to folder "/home/venzo/public_html/admin/files/"

 

 

may this will help you

mac_gyver Prolific Member

mac_gyver

Posted
Posted (edited)

you need to find and fix what is causing each error.

 

your undefined ... error messages are because you are referencing a variable you haven't initialized. you need to initialize the $error variable/array (you are using it two separate ways in the code) or you must test if it is set before you try to reference the value in it. fix these errors so that you only see the real errors your code produces.

 

the copy/unlink errors are because the file or path being referenced doesn't exist. you need to determine if it is the path that doesn't exist or if it is the filename that doesn't exist (and it may be that the filename contains new-line or other non-printing characters that is causing the error.)

 

one specific problem that jumps out is your code is referencing the wrong variables at times. one example is the $file variable. $file is set in the first foreach(){} loop, but you are trying to use it later in the code. it would only have the last value from the first foreach(){} loop, not any current value. make sure each functional block of code is doing what you intend. are the inputs, processing, and output/result correct for that block of code.

 

you also need to work on simplifying your code (the less code there is the easier it will be to see what your code is and what it is doing.) for example, your first loop is testing against four different values, but is repeating all the logic each time. just put those four values into an array and use in_array() to test if the result is one of those four values.

 

also, your $process flag is probably not doing what you think. it is only reflecting the value from the last file that was looped over.

 

btw - you cannot set magic_quotes_gpc in your code, so that ini_set() statement isn't doing anything and is just adding clutter.

Edited by mac_gyver
mac_gyver Prolific Member

mac_gyver

Posted
Posted

here's another specific flaw in your logic -

 

you have corresponding entries in the $tables and $ReportFile arrays. you need to use ONE loop to access the same entry in both arrays. you are currently looping over everything in the $ReportFile array for every entry in the $tables array.

 

use something like -

foreach ($tables as $key=>$companyTbl) {
    $report = $ReportFile[$key];

   ...

}

or you could just build one overall data array, where all the needed values are stored as an array (you could for example store the $path, and $file values as entries in the array, which i suspect you need to do anyway.)

richei Member

richei

Posted
  • Author
Posted

Makes sense, hadn't really thought about the paths.  So it would be easier to just load everything into one array instead of trying to use 3 or 4 different ones?

I guess something like: $data = array("path"=>APP, "table"=>$table, "file"=>$ReportFile, "process"=>$process);

 

Then use mac_gyver's loop example?

richei Member

richei

Posted
  • Author
Posted

ok, well i made some progress, but created more errors.  I used the array that i have above, but something still isn't processing right.

 

This is sections from above that were changed.

 

 

} elseif(left($filen, 12) == "S_D_85409067") {
    $tables = 'venzo_app_dtrends';
    $ReportFile = APP_PATH."files/".$filen;
    if(is_file(APP."/$filen")) {
       $process = 0;
    }
    $process = 1;
    $data[] = array("path"=>APP, "table"=>$tables, "file"=>$ReportFile, "process"=>$process);
}
 

foreach($data as $key => $value) {
    foreach($value as $val) {
 
       $process = $value['process'];
       $report = $value['file'];
       $companyTbl = $value['table'];
       $path = $value['path'];

now at the bottom of the output, i get

 

Warning: unlink(/home/venzo/public_html/admin/files/S_D_85409067_20130615.txt): No such file or directory in /home/venzo/public_html/admin/import_files_trends.php on line 151
S_D_85409067_20130615.txt has been imported

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

Warning: Illegal string offset 'process' in /home/venzo/public_html/admin/import_files_trends.php on line 59

Warning: Illegal string offset 'file' in /home/venzo/public_html/admin/import_files_trends.php on line 60

Warning: Illegal string offset 'table' in /home/venzo/public_html/admin/import_files_trends.php on line 61

Warning: Illegal string offset 'path' in /home/venzo/public_html/admin/import_files_trends.php on line 62

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

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

 

The first 4 line numbers are the variables i listed above in the changes.  in the last 2 errors, 1 is the output from process, not even sure how its ending up as the path.  The best i can figure is that something is scrambling the array keys or something.

  • Solution
mac_gyver Prolific Member

mac_gyver

Posted
  • Solution
Posted (edited)

a) why do you have two nested foreach(){} loops? if $data contains the arrays of values, the first foreach(){} loop gives you each of those arrays in $value. you still have too much code that isn't doing anything towards your goal.

 

b) one reason the help given so far is just general things, is because, without knowing/having an example of what is in your .txt files, no one here can offer more specific help. posting an example of what your input is, so that someone can see what result your code should be producing, would get more specific help.

 

c) i still don't think your $process flag is doing what you expect, but since we don't know what the purpose of it is, cannot really help you. adding some comments to your code would be helpful. if it is to indicate that there is at least one new file that needs to be scanned and added to the database, why not just let the number of entries in the $data array tell you that? if the file needs to be scanned, add it to the $data array. if it doesn't need to be scanned, don't add it to the $data array?

 

d) cannot really help you with the current errors without the current code that corresponds to those errors. you are either un-setting elements in the $data array, accessing past the end of the $data array, overwriting $value with something that isn't an array, or perhaps storing an entry in $data that isn't an array.

 

[rant] i've been meaning to write this. i've been mentioning that people have too much code in a number of threads. programming is a little like Goldilocks and the three Bears. you must have just the right amount of code. if you have too much code, you have wasted your time writing, testing, and debugging code that doesn't have anything to do with your goal. if you have too little code, you have left out things like validation and error checking and your code will vomit or die at the least little thing that isn't prefect. [/rant]

Edited by mac_gyver
richei Member

richei

Posted
  • Author
Posted (edited)

ok, then here we go

 

entire code:

<?php mysql_connect('localhost', 'xxxx', '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);} $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")) {$process = 0;}$process[] = 1;$data[] = array("path"=>MUSIC, "table"=>$tables, "file"=>$ReportFile, "process"=>$process);} elseif(left($filen, 12) == "S_D_80045583") {$tables = 'venzo_itunes_dtrends';$ReportFile = APP_PATH."files/".$filen;if(is_file(MUSIC."/$filen")) {$process = 0;}$process = 1;$data[] = array("path"=>MUSIC, "table"=>$tables, "file"=>$ReportFile, "process"=>$process);} elseif(left($filen, 12) == "S_W_85409067") {$tables = 'venzo_app_trends';$ReportFile = APP_PATH."files/".$filen;if(is_file(APP."/$filen")) {$process = 0;}$process = 1;$data[] = array("path"=>APP, "table"=>$tables, "file"=>$ReportFile, "process"=>$process);} elseif(left($filen, 12) == "S_D_85409067") {$tables = 'venzo_app_dtrends';$ReportFile = APP_PATH."files/".$filen;if(is_file(APP."/$filen")) {$process = 0;}$process = 1;$data[] = array("path"=>APP, "table"=>$tables, "file"=>$ReportFile, "process"=>$process);}} foreach($data as $key => $value) {foreach($value as $val) { $process = $value['process'];$report = $value['file'];$companyTbl = $value['table'];$path = $value['path']; if($process == 1) { $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*/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(isset($error[$i]) != true) { $sql = "INSERT INTO {$companyTbl}(" . implode(', ', $sqlFields) . ") VALUES ('" . implode("', '", $arr) . "')";$ins = mysql_query($sql); if($ins) {echo "$filen has been imported<br>";copy($report, $path."/".$filen);unlink($file);} else {echo "Problem with $filen<br>";}} else {echo "Record {$i} not inserted!";}}} //unset($sqlFields, $fcontents, $line);//unset($process, $report, $companyTbl, $path); /*if there is an error, stop here and delete the file*/if(count($error) > 0) {unlink($path."/".$file);die();}}}}?>

 
txt file example:
Provider Provider Country Vendor Identifier UPC ISRC Artist / Show Title Label/Studio/Network Product Type Identifier Units Royalty Price Begin Date End Date Customer Currency Country Code Royalty Currency PreOrder ISAN Apple Identifier Customer Price CMA Asset/Content Flavor Vendor Offer Code Grid Promo Code Parent Identifier Parent Type Id
APPLE US USMAS1333307 859709159331 USMAS1333307 Orange Box IDGAF (In Style of YG) [Karaoke Version] Master Label Group H 21 .7 05/22/2013 05/22/2013 USD US USD     601819998 .99         859709159331_USMAS1333307     859709159331 17
APPLE US USBLU1322210   USBLU1322210 Blue Lust Do My Dance (feat. Cassie & Too Short) [Ringtone] Master Label Group PR 1 1 05/22/2013 05/22/2013 USD US USD     640917530 1.29         859744557826_USBLU1322210       
APPLE US USVGG1281844 859700908075 USVGG1281844 Mr. Games Gerudo Valley (From Legend of Zelda: Ocarina of Time) VG Group H -1 .7 05/22/2013 05/22/2013 CAD CA CAD     510978207 0 CMA-C   859700908075_USVGG1281844     859700908075 17
 
I see what you mean about the process and you're right, i just have to move the array inside the is_file if statement to solve that issue.
 
I had to use double foreach loops because the first one was giving me nested arrays, so i used another one to get just want i need.  But if it can be done with just 1 loop, then that's fine.  I'm all for keeping things simple :)
Edited by richei
richei Member

richei

Posted
  • Author
Posted (edited)

Oh wow, talk about a major brain fart.  I just realized what everyone was talking about with $process not working like i thought it would.  I forgot the bleeping else :) and the 0 one isn't part of the array.  I already got rid of it completely this morning before i left for work, didn't have time to test it though.

 

To clarify what the array gives me, its something like

Array(
    [0]
        Array(
             [path] = value
             [table] = value
             [file] = value
             [process] = value
        )
    [1]
        Array(
             [path] = value
             [table] = value
             [file] = value
             [process] = value
        )
)

To get just

Array(

    [path] = value

    [table] = value

    [file] = value

    [process] = value

)

I need that second foreach loop.  But if there's another way of doing it, maybe using another type of loop, then show me what to do and i'll give it ago :)

 

** I'll give you guys the actual output from the first loop when i get home this evening**

Edited by richei
richei Member

richei

Posted
  • Author
Posted

sweet, i got it.  Figured out where that unlink error was coming from - had the unset() in the wrong place, needed to be at the very end of the loop.

 

I have one more question though, i'm getting a lot of confirmations on the first 2 files, then progressively less as the number of files decreases.  Is there a way to only get 1 confirmation per file imported? i'd like to leave it up there for the email i get whenever the cronjob goes off.  I don't know if its just a matter of putting those into an array and moving it outside of the loop or if there's a way to avoid doing that.

richei Member

richei

Posted
  • Author
Posted

Its not fixed after all, but at this point, i really don't care.  I don't think its the script anymore since some of those text files are coming up wrong.  Below is what i ended up with, if you can see something wrong with it, please say something because i'm about ready to quit the company.

 

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);
}
boompa Newbie

boompa

Posted
Posted

You really should look into using indentation as an aid in writing code. This is what your code should look more like:

 

 

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);
}

 

What's the current problem, and can you attach a sample of your data file thast preserves the tabs that are separating the fields? Looks like what you pasted in here lost those.

richei Member

richei

Posted
  • Author
Posted

I do indent using tabs, just for whatever reason, they didn't get copied, the forum removed them, or i used the wrong bbcode, idk which.  If i use the php bbcode, everything gets doubled spaced.  I had copied a few lines of the file in reply #9, but i went ahead and attached a copy of one of the files.

 

I'm not sure what the problem is, when it hits the last file in the array, the script breaks.  These the error messages:

 

Array

(
    [path] => /home/venzo/public_html/admin/trends/itunes
    [table] => venzo_itunes_dtrends
    [file] => /home/venzo/public_html/admin/files/S_D_80045583_20130618.txt
)

</pre><br />
<b>Warning</b>:  file(/home/venzo/public_html/admin/files/S_D_80045583_20130618.txt): failed to open stream: No such file or directory in <b>/home/venzo/public_html/admin/import_files_trends.php</b> on line <b>65</b><br />
<br />
<b>Warning</b>:  unlink(/home/venzo/public_html/admin/trends/itunes/S_D_80045583_20130618.txt): No such file or directory in <b>/home/venzo/public_html/admin/import_files_trends.php</b> on line <b>110</b><br />

 

Line 65 is $fcontents = file($report, FILE_IGNORE_NEW_LINES); and line 110 is the unlink in

 

/*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); < line 110
   die();
}
 
The script shouldn't be entering that block unless there's an issue with the file, which i don't think there is.  But that error is actually correct, the file doesn't exist yet because the query never happened.

 

S_D_80045583_20130618.txt

richei Member

richei

Posted
  • Author
Posted

Turned out to not be an issue with the script, but with the actual file.  Noticed a trend after a couple days of running the script and watching the output, there's apparently an issue with something in the files for one particular file type.  

 

Thanks for the help :) You have no idea how long i've been struggling with this one script.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.