CSVの改行テキスト(\r\n)をPDOでMySQLにインサートしたいが\r\nが文字列として入ってしまう

Question

目的: 改行テキストをPDOでCSVからMySQLにinsertしたい。

問題: 改行箇所を"\r\n"としてPDOでMySQLにインサートすると、mysqlに\r\nが文字列として入ってしまう。

mysql: 8.0.22

csvテキスト

name1,サンプル1\r\nテキスト1
name2,サンプル2\r\nテキスト2

pdo

try {
		$pdo = new PDO("mysql:host=localhost;dbname=test;charaset=utf8","*","*",
				array(PDO::ATTR_EMULATE_PREPARES => false));
	} catch (PDOException $e){
		exit('データベース接続失敗.'.$e->getMessage());
	}

$fp = fopen("news.csv", "r");

while(! feof($fp)){
	$csv = fgets($fp);
	$csv = trim($csv,'"');
	$csv = mb_convert_encoding($csv, "UTF-8", "utf-8");
	$csv = str_replace('"','',$csv);
	$csv_array = explode(",",$csv);

	$stmt = $pdo->prepare("INSERT INTO news (name, body) VALUE (:name, :body)");

	$stmt->bindValue(":name", $csv_array[0], PDO::PARAM_STR);
	$stmt->bindValue(":body", $csv_array[1], PDO::PARAM_STR);
	var_dump($stmt);
	$stmt->execute();
	
}

mysql> select * from news;
+----+----------+----------------------------------+
| id | name     | body                             |
+----+----------+----------------------------------+
|  1 | name1   | サンプル1\r\nテキスト1
         |
|  2 | name2    | サンプル2\r\nテキスト2           |
+----+----------+----------------------------------+
2 rows in set (0.00 sec)

ちなみに、以下のように変数として書くと、\r\nが改行と認識されてmysqlで改行されてinsertされます。

	$text = "name1,サンプル1\r\nテキスト1";

	$stmt = $pdo->prepare("INSERT INTO news (name, body) VALUE (:name, :body)");

	$stmt->bindValue(":name", $csv_array[0], PDO::PARAM_STR);
	// $stmt->bindValue(":body", $csv_array[1], PDO::PARAM_STR);
	$stmt->bindValue(":body", $text, PDO::PARAM_STR);

mysql> select * from news;
+----+----------+------------------------------------+
| id | name     | body                               |
+----+----------+------------------------------------+
|  1 | name1   | name1,サンプル1
テキスト1         |
|  2 | name2    | name1,サンプル1
テキスト1         |
+----+----------+------------------------------------+
2 rows in set (0.00 sec)
コード

解決法を教えていただけないでしょうか？

Answer 1

CSVとしてちょっとおかしいですね
以下のようにして改行をきちんと処理してください

"name1","サンプル1
テキスト1"
"name2","サンプル2
テキスト2"

Comments

[DeepRoastBeans]

fgets($fp);で1行ずつ処理しているので、頂いた内容でfgetsしてinsertしようとすると、2行になってしまいます。

mysql> select * from news;
+----+----------------+----------------+
| id | name | body |
+----+----------------+----------------+
| 1 | name1 | サンプル1
|
| 2 | テキスト1
| NULL |
| 3 | name2 | サンプル2
|
| 4 | テキスト2 | NULL |
+----+----------------+----------------+
4 rows in set (0.00 sec)

[yambejp]

fgetcsvを利用してください
$fp=fopen("test.csv","r");
while(($data[]=fgetcsv($fp))!==false){}
fclose($fp);
print_r($data);

[DeepRoastBeans]

fgetcsvで改行をinsertきました。こちらだと、データ作成者が、"\r\n"を入れる必要がないので、オペレーション上、一番良いソリューションになりました。素晴らしいです。

$fp = fopen("hoge.csv", "r");
while(($data[]=fgetcsv($fp))!==FALSE){
}
fclose($fp);

var_dump($data);

foreach($data as $result){
$stmt = $pdo->prepare("INSERT INTO news (name, body) VALUE (:name, :body)");

$stmt->bindValue(":name", $result[0], PDO::PARAM_STR);
$stmt->bindValue(":body", $result[1], PDO::PARAM_STR);
$stmt->execute();
}
echo "done";

Answer 2

文字列としての"\r\n"は"\r\n"となります。
よって、"\r"→"\r", "\n"→"\n"に置き換えてやればいいです。

//  $stmt->bindValue(":body", $csv_array[1], PDO::PARAM_STR);
//  ↓
    $stmt->bindValue(":body", str_replace(["\r","\n"], ["\r", "\n"], $csv_array[1]), PDO::PARAM_STR);

Comments

[DeepRoastBeans]

なるほどっすね。エスケープされてるんですね。仕組みが理解しました。ありがとうございます。

Answer 3

name1,サンプル1\r\nテキスト1

と、\r\nという文字列なのだから、\r\nという文字列として扱われるのが当然です。
\r\nという文字列をCRLFに置換しましょう。

$csv = str_replace('\r\n',"\r\n",$csv);

PHP: 文字列 - Manual をよく読みましょう。

Comments

[DeepRoastBeans]

manual読みました。
> 文字列が二重引用符 (") で括られた場合、 PHP は、以下のエスケープシーケンスを特殊な文字として解釈します。
ダブルクオテーションで囲むと、CRLFと解釈されるんですね。改行も上手くいきました。大変ありがとうございます。