Replacing quotes within arrays

[Kudose]

Hello all. Here is my problem. I am breaking down a CSV file and some of the columns contain HTML. It seems as if they HTML portions are fine, but the single quote ' ' marks cause SQL syntax errors. I am trying to replace them with something that wont cause the error in my insert loop.

If someone could pleace guide me on how to replace these error causers and where to put the fixes, it would be very much appreciated.

Thanks!

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

	//echo "<table>";
	$row_num = 1;
	$err_no = 0;
	foreach($csv as $row){
		//echo "<tr>";
			foreach($row as $col){
				//echo "<td> <b>";
				//echo $col;
				$col = addslashes($col); //****DOESNT SEEM TO DO ANYTHING***\\
				$col = str_replace("'", "\"", $col); //****DOESNT SEEM TO DO ANYTHING***\\
				//echo "</b> </td>";
			}
		if($row_num != 1){
			mysql_query("INSERT INTO ".$prefix."store_wholesale VALUES ('','".implode("','", $row)."')");
		}
		if(mysql_error()){ 
			echo implode("<b> :: </b>", $row);
			echo "<br /><br /><b>Line: ".$row_num." was not inserted due to structure error.<br />MySQL says: ".mysql_error()."</b><br /><br />"; $err_no++;
		}
		$row_num++;
		//echo "</tr><tr><td>&nbsp;</td></tr>";
	}
	//echo "</table>";		

	$end_2 = microtime_float();
	echo "<br />Read and inserted ".($row_num-$err_no)." rows in ".($end_2 - $start_2)." seconds with ".$err_no." errors.<br /><br />";
	$msg = $file_name." imported.";
?>

[tsinka]

Hi,

mysql_escape_string escapes a string so that it will not break up a query.

Example:

mysql_query("INSERT INTO ".$prefix."store_wholesale VALUES ('','".implode("','", mysql_escape_string($row))."')"); 

Thomas

[Kudose]

Now I get this error.

MySQL says: Column count doesn't match value count at row 1

And a Bad Arguments warning for implode.

Now, I checked the file. It's not it. I even switched from CSV to a tab delimited file to see.

Any dieas?

Thanks.

🙂

[tsinka]

Ok,

echo the queries and post one. The message means that the number of values doesn't match the number of columns in the table (e.g. you try to insert values into more columns than exist.

INSERT INTO atable VALUES ('val1','val2','val3');

will cause that error if atable has only two columns.
Always specify the columns like:

INSERT INTO atable (column1,column2,column3) VALUES ('val1','val2','val3');

But I think that this doesn't solve the problem in your case. Something might be wrong with the implode/mysql_escape_string combination in that case. I need to know how the query looks like.

Thomas

[Kudose]

If you have AIM...IM me at effextc

Using the code below, I printed the number of columns it was trying to insert, and it returned 18, which is the same as the columns in the database, minus the one for a tmp_id.

I can shoot you a mock of the tab delimited file and a mock csv if you would like, and a screen shot of my TB if you email me or IM me.

Thanks.

<edit>The return of this script is included below</edit>

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

	$row_num = 1;
	$err_no = 0;
	foreach($csv as $row){
		if($row_num != 1){
			mysql_query("INSERT INTO ".$prefix."store_wholesale VALUES ('','".implode("','", mysql_escape_string($row))."')");
		}
		if(mysql_error()){ 
		$num = count($row);
		echo "<b>".$num."</b>";
			echo implode("<b> -> </b>", $row);
			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++;
		}
		$row_num++;
	}

Warning: implode(): Bad arguments. in /home/xxxxxx/public_html/wholesale/admin/csv_upload.php on line 94

cnt:18

84584 -> AB-001 -> CBlock, 90 tabs -> [url]www.wholesalemarketer.com/images/products/15/AB-001.jpg[/url] -> Absolute Nutrition -> Prevent any disease. -> -> 0.55 -> -> N -> N -> N -> Absolute Nutrition CBlock, 90 tabs -> 0 -> 19.02 -> 29.95 -> 78 ->

Line: 2 was not inserted due to structure error.

MySQL says: Column count doesn't match value count at row 1

[tsinka]

My fault ... I oversaw that $row is an array 🙁

foreach($csv as $row) { 
  $arrCol = array();
  foreach($row as $col) {
    $arrCol[] = mysql_escape_string($col);
  } 
  if($row_num != 1){ 
    mysql_query("INSERT INTO ".$prefix."store_wholesale VALUES ('','".implode("','", $arrCol)."')"); 
  } 
  if(mysql_error()){  

    echo implode("<b> :: </b>", $arrCol); 
    echo "<br /><br /><b>Line: ".$row_num." was not inserted due to structure error.<br />MySQL says: ".mysql_error()."</b><br /><br />"; $err_no++; 
  } 
  $row_num++; 
} 

Thomas

[Kudose]

🙁

Now it reads 15 lines, and errors out the rest of the lines, claiming they are one column shorter than the rest.

The files look clean though!

Grr...

Any suggestions?

[Kudose]

Okay, I fixed the errors and the column problem...kinda.

Now...It inserts 15 lines and stalls the page...

Sucks.

Using this code. (I added a for loop to add cmissing columns,)

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

	$row_num = 1;
	$err_no = 0;
	foreach($csv as $row){
		$arrCol = array();
  foreach($row as $col) {
    $arrCol[] = mysql_escape_string($col);
   } 
 }
  $col_cnt = count($arrCol);
  if($col_cnt !=18){
  	for($col_cnt; $col_cnt=18; $col_cnt++){
		$arrCol[$col_cnt] = "";
	}
  if($row_num != 1){ 
    mysql_query("INSERT INTO ".$prefix."store_wholesale VALUES ('','".implode("','", $arrCol)."')"); 
  }
			if(mysql_error()){ 
			$num = count($row);
			echo "<b>".$num."</b>";
				echo implode("<b> -> </b>", $arrCol);
				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++;
			}
			$row_num++;
		}

	$end_2 = microtime_float();
	echo "<br />Read and inserted ".($row_num-$err_no)." rows in ".($end_2 - $start_2)." seconds with ".$err_no." errors.<br /><br />";
	$msg = $file_name." imported.";

[tsinka]

Hi,

look at the for loop:

for($col_cnt; $col_cnt=18; $col_cnt++){

$col_cnt=18 looks suspicious ... did you want something like $col_cnt<18 or $col_cnt<=18 ?

Additionally, the while loop seems to be closed too early in your code by the second closing bracket in

foreach($row as $col) {
$arrCol[] = mysql_escape_string($col);
}

}

The code looks a little bit confusing (indents).
Thomas

[Kudose]

Ok.

1 Problem left (I hope).

It isnt adding the columns up to 18 like I need it, to match the DB.

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

	$row_num = 1;
	$err_no = 0;
	foreach($csv as $row){
		$arrCol = array();
  foreach($row as $col) {
  	if($col == ""){ $col = "NULL"; }
    $arrCol[] = mysql_escape_string($col);
   } 

  $col_cnt = count($arrCol);
  if($col_cnt !=18){
  	for($col_cnt; $col_cnt<=18; $col_cnt++){
		$arrCol[$col_cnt] = "NULL";
		}
	}
  if($row_num != 1){ 
    mysql_query("INSERT INTO ".$prefix."store_wholesale VALUES ('','".implode("','", $arrCol)."')"); 
  }
			if(mysql_error()){ 
			$num = count($row);
			echo "<b>".$num."</b>";
				echo implode("<b> -> </b>", $arrCol);
				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++;
			}
			$row_num++;
		}

	$end_2 = microtime_float();
	echo "<br />Read and inserted ".($row_num-$err_no)." rows in ".($end_2 - $start_2)." seconds with ".$err_no." errors.<br /><br />";
	$msg = $file_name." imported.";

[tsinka]

Does the table have 18 columns or 19 columns ? Might sound silly, but if it has 18 columns then you need to add columns to the array up to 17 because the id field is a separate value in the query.

Thomas

[Kudose]

The table has 18 columns. I renumbered the for statement.

🙂

But....

It didnt work. The bold number I use to count the columns still says 17.

🙁

[tsinka]

I modified your code a little bit:

  $start_2 = microtime_float();
  $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);
} 

// add missing columns
$col_cnt = count($arrCol);
if($col_cnt != 18){
  for($cnt=$col_cnt; $cnt<18; $cnt++){
    $arrCol[] = "NULL";
  }
}

// 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>".$num."</b>";
  echo implode("<b> -> </b>", $arrCol);
  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++;
}
  }

  // decrease row counter by one
  // because of the header in the csv file
  // that doesn't get inserted into the database
  $row_num -= 1;


  $end_2 = microtime_float();
  echo "<br />Read and inserted ".($row_num-$err_no)." rows in ".($end_2 - $start_2)." seconds with ".$err_no." errors.<br /><br />";
  $msg = $file_name." imported.";

Thomas

[AllenGarner]

do you by chance happen to have a delimiter within a delimiter in your file such as

situation) ,"sometext"some more text"and even more text",
in your csv files this causes bad column count

fgetcsv uses double quotes by default as string enclosures so if
you have any double qoutes or the like in your data it causes
havec I realize your using tabs as delimns so just sub tabs for
commas in examples above

fgetcsv splits the array before the call to mysql_escape...... so......

this will work regardless of the number of columns
unless you run into the above situation

Note: mysql_escape_string() has been deprecated since PHP 4.3.0. Do not use this function. Use mysql_real_escape_string()
or addslashes() instead.

// Add your db connection info here

$uploadfile = "full_path_to_your_file";
$table = "your_table_name";
$row = 1;                        

$handle = fopen($uploadFile, "r");        

while ( ($data = fgetcsv($handle, filesize($uploadFile)+1, "\t")) != FALSE ) {  

   $num = count($data); 
   if($row == 1){
      $fields = " `id` , ";
      for($i=0; $i<$num; $i++){
         $fields .= "`" . addslashes(trim($data[$i])) . "` , ";
      }
      $f = substr($fields, 0, -1);
      $f = substr($f, 0, -1);
      $row++;
   }else{
      $values = " '' , "; 
      for($i=0; $i<$num; $i++){ 
         $values .= "'" . addslashes(trim($data[$i])) . "' , ";
      }      

      $v = substr($values, 0, -1);
      $v = substr($v, 0, -1);  

      $sql = "INSERT INTO $table ( $f ) VALUES ( $v )";
      mysql_query($sql) or die(print "Upload complete some entries may not have been loaded successfully!");
      $values = ''; 
      $row++;
   } 
}
fclose($handle);