Escape Comma

[hackalive]

Hi guys,

 

I am using this code:

function importCsv($file, $table) {

$fieldseparator = ",";
$lineseparator = "\n";

$fileOpen = fopen($file, "r");
$fileSzie = filesize($file);
$fileContent = fread($fileOpen,$fileSize);
fclose($fileOpen);

$fileLines = 0;
$fileQuiries = "";
$fileLineArray = array();

foreach(explode($lineseparator,$fileContent) as $fileLine) {

$fileLines++;

$fileLine = trim($fileLine," \t");
$fileLine = str_replace("\r","",$fileLine);
$fileLine = str_replace("'","\'",$fileLine);

$fileLineArray = explode($fieldseparator,$fileLine);

$fileLineMySql = implode("','",$fileLineArray);

$query = "INSERT INTO `$table` VALUES(NULL,'$fileLineMySql')";
$mysqli->query($query); 

}
}

 

My issue is that the field contains a comma (",") and this stuffs up the INSERT.

 

How can I escape this comma so the code does not think it is a different mysql table column?

 

Thanks in advance.

[scootstah]

MySQL would only think it is another column if you ended the single quotes. So, in fact quotes are your problem, not commas.

 

Escape all input to the database with mysql_real_escape_string.

[hackalive]

Okay the code is now:

function importCsv($file, $table) {

$fieldseparator = ",";
$lineseparator = "\n";

$fileOpen = fopen($file, "r");
$fileSzie = filesize($file);
$fileContent = fread($fileOpen,$fileSize);
fclose($fileOpen);

$fileLines = 0;
$fileQuiries = "";
$fileLineArray = array();

foreach(explode($lineseparator,$fileContent) as $fileLine) {

$fileLines++;

$fileLine = trim($fileLine," \t");
$fileLine = str_replace("\r","",$fileLine);
$fileLine = str_replace("'","\'",$fileLine);
$fileLine = $mysqli->escape_string($fileLine);

$fileLineArray = explode($fieldseparator,$fileLine);

$fileLineMySql = implode("','",$fileLineArray);

$query = "INSERT INTO `$table` VALUES(NULL,'$fileLineMySql')";
$mysqli->query($query);

}
}

 

And the problem still exists.

 

I'm isolating it down to the code "

$fieldseparator = ",";

$fileLineArray = explode($fieldseparator,$fileLine);

". Because it appears to split/explode at all commas regardless.

 

Any thoughts/suggestions on how to better handle this?

Edited October 20, 2012 by hackalive

[scootstah]

You need to escape after the implode().

[hackalive]

Okay code now is:

function importCsv($file, $table) {

$fieldseparator = ",";
$lineseparator = "\n";

$fileOpen = fopen($file, "r");
$fileSzie = filesize($file);
$fileContent = fread($fileOpen,$fileSize);
fclose($fileOpen);

$fileLines = 0;
$fileQuiries = "";
$fileLineArray = array();

foreach(explode($lineseparator,$fileContent) as $fileLine) {

$fileLines++;

$fileLine = trim($fileLine," \t");
$fileLine = str_replace("\r","",$fileLine);
$fileLine = str_replace("'","\'",$fileLine);

$fileLineArray = explode($fieldseparator,$fileLine);

$fileLineMySql = implode("','",$fileLineArray);

$fileLineMySql = $mysqli->escape_string($fileLineMySql);

$query = "INSERT INTO `$table` VALUES(NULL,'$fileLineMySql')";
$mysqli->query($query);

}
}

 

This still does not work.

 

Sample of two rows (row 1 works - row 2 fails) - what they look like in the actual CSV (Comma) file:

 

2,1,1,Why will this work.,20,5,0

3,1,2,"And this seems to fail, so bad.",0,5,0

 

and Output from PHP code (of done just before INSERT)

2','1','1','Why will this work.','20','5','0

3','1','2','"And this seems to fail',' so bad."','0','5','0

Edited October 20, 2012 by hackalive

[scootstah]

echo $query and post the result in your next reply.

 

EDIT: You need to use $mysqli->real_escape_string(), not $mysqli->escape_string(). There is a difference.

Edited October 20, 2012 by scootstah

[hackalive]

$mysqli->real_escape_string()

INSERT INTO `tablea` VALUES(NULL,'\',\'\',\'1\',\'Why will this work.\',\'20\',\'5\',\'0')

INSERT INTO `tablea` VALUES(NULL,'\',\'\',\'2\',\'\"And this seems to fail\',\' so bad.\"\',\'0\',\'5\',\'0')

 

With adding escape_string and real_escape_string after implode - all fail now not just ones with commas in them.

 

$mysqli->escape_string()

 

INSERT INTO `classes` VALUES(NULL,'\',\'\',\'1\',\'Why will this work.\',\'20\',\'5\',\'0')

INSERT INTO `classes` VALUES(NULL,'\',\'\',\'2\',\'\"And this seems to fail\',\' so bad.\"\',\'0\',\'5\',\'0')

Edited October 20, 2012 by hackalive

[PFMaBiSmAd]

Because you are unconditionally exploding on the $fieldseparator, without regard to it being inside of a double-quoted string, you are getting an extra array element.

 

Your parser needs to take into account the double-quoted values that contain the separator character or since fgetcsv already does this, why not just use fgetcsv?

[hackalive]

function importCsv($file) {

$fileOpen = fopen($file, "r");
$fileSize = filesize($file);
$fileContent = fgetcsv($fileOpen,$fileSize);
fclose($fileOpen);
print_r($fileContent);
}

 

returns ONLY

Array ( [0] => 2 [1] => 1 [2] => 1 [3] => Why will this work. [4] => 20 [5] => 5 [6] => 0 )

Edited October 20, 2012 by hackalive

[scootstah]

why not just use fgetcsv?

 

Or better yet, LOAD DATA INFILE.

[hackalive]

Lets stick to fgetcsv() as I need to do some manipulations when we get this working (which as my post above shows I cant).

 

So how do I make fgetcsv(0 do what I want - anyone can tell me?

Edited October 20, 2012 by hackalive

[scootstah]

So what's wrong with the fgetcsv() result?

[hackalive]

Well 1 - a, I using fgetcsv instead of all the other code? and 2 - when I tried it it only out puted one row (see post #9).

[scootstah]

CSV's are typically read line-by-line. Check the manual for fgetcsv to see how you can loop it.

 

Also, you should be constructing an INSERT query with multiple rows and then executing it once, instead of executing it on each iteration. We're talking an absurdly large number of potential queries. Check the MySQL manual for multi-insert syntax.

[hackalive]

Okay so now if I do an implode to then do an insert it make it all one 'scentence'

 

How can i process the INSERT given I now have $data[0], $data[1] etc all then inside $arrResult again.

 

Using the example from php.net

[scootstah]

This is the basic idea:

$insert = '';

foreach(array(1,2,3,4,5) as $arr)
{
   $insert[] = "($arr)";
}

$query = "INSERT INTO table VALUES " . implode(',', $insert);

 

This example produces:

INSERT INTO table VALUES (1),(2),(3),(4),(5)

 

So, that would create 5 rows in one query.

[hackalive]

I have this code:

function importCsv($file) {

 global $mysqli;
 $arrResult = array();
 $handle = fopen($file, "r");
 $fileSzie = filesize($file);
 if($handle) {
  while(($data = fgetcsv($handle,$fileSzie,",")) !== FALSE) {

   // $data[x] manipulations will go here

   $arrResult[] = $data;
  } 
  fclose($handle);
 }
 // Mass MySQL insert goes here

}

 

How do I handle the mass mysql insert? How do I get the PHP to create the insert code nice, clean and easy (like I used to have it rather easy)?

 

Cheers

[hackalive]

Further developed the code:

foreach ($arrResult as $result) {
  $result[3] = '"'.$mysqli->escape_string($result[3]).'"';
  $result = implode(",",$result);
  $result = $mysqli->real_escape_string($result);
  $query = "INSERT INTO `table2` VALUES(NULL,'$result')";
  $mysqli->query($query);
  echo '<b>'.$mysqli->error.' </b>'.$query.'<br />';
 }

 

This is resulting in the same error as old code (Column count doesn't match value count at row 1)

Edited October 20, 2012 by hackalive

[scootstah]

I just showed you how to construct the query, and you did none of it.

 

$data is going to be an array. You can't insert an array into MySQL, you need to make it a string first. You also need to make sure the right values are going to the right columns. What does your table look like?

[hackalive]

See edit of post above.

 

Ive tried something in regards to inserting

 

 

 

It appears to now come down to the fact it is adding ' to the start and end of the $result when it outputs on the query.

Edited October 20, 2012 by hackalive

[hackalive]

Ive gone for this (seems to work for the moment)

$sql = array();

foreach ($arrResult as $result) {
$result[3] = '"'.$mysqli->real_escape_string($result[3]).'"';
$result = implode(",",$result);
$query = "INSERT INTO `table2` VALUES(NULL,$result)";
$mysqli->query($query);
echo '<b>'.$mysqli->error.' </b>'.$query.'<br /><br />';
}

 

Now just how to make on bulk query (using $sql)

Edited October 20, 2012 by hackalive

[fenway]

You'll need multiple field values for VALUES -- i.e. VALUES (one,two), (three,four).