[Resolved] Array shifting

[Kudose]

Hello all.

In the code below, I have a script to fix a set of column numbers to 18 by adding or popping them off until they get to 18.

Now, my only problem is that when it inserts my data into my database, all of the columns are shifting by 1 to the right.

Now, I dont know where to use array_shift to get them right, or if that is even the right command to use.

Any assistance is great!

 $csv_read = fopen($path.$file_name, "r");
  while(($data = fgetcsv($csv_read, filesize($path.$file_name)+1, "\t")) !== FALSE){
    $csv[] = $data;
  }

  $row_num = 0;
  $err_no = 0;

  foreach($csv as $row) {
    // the id column
    $arrCol = array('');

// add values to column array
foreach($row as $col) {
  if ($col == "") { 
    $col = "NULL"; 
  }
  $arrCol[] = mysql_escape_string($col);
	if(sizeof($arrCol) > 18){
		while(sizeof($arrCol) > 18){ array_pop($arrCol); }
	} elseif(sizeof($arrCol) < 18) {
		  for($cnt=sizeof($arrCol); $cnt<18; $cnt++){
  			$cnt++;
   			 $row[$cnt] = "NULL";
			$cnt--;
		  }
	} 
} 


//echo "Row ".$row_num." has ".sizeof($arrCol)." columns.<br />";
// execute query (avoid header line of csv file)
if($row_num != 0){ 
  mysql_query("INSERT INTO ".$prefix."store_wholesale VALUES ('".implode("','", $arrCol)."')"); 
}

// increment row counter
$row_num++;

// if unsuccessful
if (mysql_error()) { 
  $num = count($arrCol);
  echo "<b>Columns in row: ".$num."</b>";
  echo "<br /><br /><hr><b>Line: ".$row_num." was not inserted due to structure error.<br /><br />MySQL says: ".mysql_error()."</b><hr><br /><br />"; 
  $err_no++;
}
  }

[Weedpacket]

Consider using array_unshift() or array_push(); instead of what you've got in the loop now. Probably array_push(), since that's the partner to array_pop().

[Kudose]

Weedpacket, Thanks for your reply.

I cannot figure this out, even after trial and error with just about all of the array function pertaining to this in the manuel. 🙂

Grr.

I am ready to scrap it all.

Would you have any suggestions on how to write a script that will take a tab delimited file and insert it into a database. Keeping in mind that some columns are null or have either 1 or 2 too many or too short of columns, so I have to fix it in the code.

i.e. the problem above.

Thanks!

[Kudose]

It seems after debuggin that the problem is the for statment which adds columns as you said.

How can I add columns to the end? I tried

$arrCol[] = mysql_escape_string($col);
		if(sizeof($arrCol) > 18){
			while(sizeof($arrCol) > 18){ array_pop($arrCol); }
		} elseif(sizeof($arrCol) < 18) {
			 while(sizeof($arrCol) < 18){
     array_push($arrCol, "NULL");
}
		} 

But all that did was turn a bunch of columns null that didnt need to be and jack everything up.

🙁

[Weedpacket]

So you want 18 array elements. No more, no less. If you have too many, you want to cut off the extras. If you have too few, you want to stick NULLs in. Right?

$the_array = //whatever

$the_array = array_pad($the_array, 18, 'NULL');
$the_array = array_slice($the_array, 0, 18);

And I think you'll want to put this outside your foreach($csv...) loop. Otherwise it will get filled up with "NULL"s after the first row is inserted and there wouldn't be any room for more.

Thinking further, another approach would be to start with an array of eighteen "NULL"s and replace them with rows, stopping (with break) if and when you get to eighteen. That way you don't waste time processing nineteen rows only to throw away the nineteenth.

[Kudose]

Thanks for all of that. 🙂

I got it figured out, now off to bigger and better things.

Like getting it to download a remote file and process / update the database by a cron job.

🙂

[Kudose]

Here is my code, although it doesnt implement Weedpacket's advice, for future users.

<?
$csv_read = fopen($path.$file_name, "r");
  while(($data = fgetcsv($csv_read, filesize($path.$file_name)+1, "\t")) !== FALSE){
    $csv[] = $data;
  }

$row_num = 1;
  foreach($csv as $row){
	  $arrCol = array();
  	foreach($row as $col){
		while(sizeof($row) > 17){ array_pop($row); }
		$arrCol[] = mysql_escape_string($col);
	}
	if($row_num != 1){	
  mysql_query("INSERT INTO table VALUES ('".implode("','", $arrCol)."')") or die(mysql_error());	
  	}
	$row_num++;
  }

?>