如何使用PDO绑定参数方法将多行文本插入mysql？

Question

I need to insert a multiline text data to Blob type column.

My table is:

CREATE TABLE `messages` (
  `id` int(11) NOT NULL,
  `msg` longblob NOT NULL
);

Addionally I post this text for saving into 'msg' column:

"This
is
Multiline
Text"

Inserting part is;

$mydata->mesaj = "This
is
Multiline
Text";
$sql =  "insert into messages(msg) values (:mesaj)";   
$stmt = $this->dbConnection->prepare($sql);     
$stmt->bindParam(':mesaj', $mydata->mesaj, PDO::PARAM_STR); /* Is it true for use PARAM_STR for longBlob type? */
try {
      $stmt->execute();
} catch (PDOException $e) {
//..blah error blah
}

It does insert the text with out newlines. When i try to open blob record, it shows:

ThisisMultilineText

I try replace with . Unfortunetely It was another unsuccesfull attempt too. Need an idea. i need to save with multiline text. Thank you, Deniz

Answer 1

I think the problem is in what you are using to display the text. On my server with PHP7 and MySQL5.6 I created your table and then ran the following code (essentially the same as yours but with some minor variable name changes):

echo "<pre>";
$mesaj = "This
is
Multiline
Text";
$sql =  "insert into messages(msg) values (:mesaj)";   
$stmt = $link->prepare($sql);     
$stmt->bindParam(':mesaj', $mesaj, PDO::PARAM_STR); /* Is it true for use PARAM_STR for longBlob type? */
try {
      $stmt->execute();
} catch (PDOException $e) {
//..blah error blah
}
$result = $link->query('SELECT msg FROM messages WHERE id = 1');
if ($result) $row = $result->fetch();
echo $row['msg'] . "
";
echo addcslashes($row['msg'], "
");

And the output I get is (as expected):

This
is
Multiline
Text
This
is
Multiline
Text