Jump to content

Escape Comma


hackalive

Recommended Posts

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.

Link to comment
Share on other sites

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 by hackalive
Link to comment
Share on other sites

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 by hackalive
Link to comment
Share on other sites

$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 by hackalive
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 by hackalive
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by hackalive
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 by hackalive
Link to comment
Share on other sites

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.

×
×
  • 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.