CODEWITHSUNDEEP
phpmysqlcarriage-return
user6631314
user6631314

Reputation: 1966

PHP/MYSQL: UPDATE Statement containing carriage returns or new lines

I have some php script that is adding a new line to a string saved in MYSQL in a mychar field.

When I look at the database using mysqladmin, I can see fields that have multiple lines. They do not show any characters. Whatever is creating the new line is invisible. enter image description here

However, when I try to update an existing string with a string that includes an extra line, using \n, it updates the field as empty. I'm guessing this is because mysql does not accept \n in an update statement.

Can anyone suggest how to prepare a string in PHP so that when updating a field in MYSQL it updates properly, ie with new line. \n does not seem to work.

$sql = "UPDATE comments SET comment = 'firstline\nsecondline\nthirdline' WHERE id = 23"

When you run the above statement, it does update the line but the comment field is empty.

Thanks for any suggestions.

Upvotes: 1

Views: 1983

Answers (2)

Mathan Kumar
Mathan Kumar

Reputation: 942

Hi you can try like this

$data = 'firstline\nsecondline\nthirdline';
    $sql = "UPDATE comments SET comment = $data WHERE id = 23"

Upvotes: 1

BlobbyBob
BlobbyBob

Reputation: 477

The statement is parsed 2 times: First by PHP and then my MySQL. PHP replaces \n with the line feed character, but MySQL seems to ignore those whitespace characters. By double escaping \n to \\n you achieve, that the \n character sequences are parsed by MySQL and inserted as line feeds.

Upvotes: 1

Related Questions