After the insert string into the MySQL, character '\n' (newline) disappears

Question

I want to insert a similar string into DB, but after inserting the characters "\n" disappear and in DB I see it as new line.

The string which I send:

[{"Index":0,"Title":"headline","Content":"first line\nsecond line","Class":"colour1"}]

For insert to DB, I use this PHP code and string I send via HTML form with method POST.

if ( $action == "save") {
  $notes = $_POST['notes'];
  $sql = "INSERT INTO notes (notes) VALUES ('$notes')";
  if (mysqli_query($conn, $sql)) {
    $saved = "Saved...";
  } else {
    $saved =  "Error: " . mysqli_error($conn);
  }
}

When I print variable "$_POST['notes']" or "$notes" before sending via the form, the string is OK, after sending the form and before inserting into DB, the string is OK too.

Try: `str_replace('\n', ' ', $_POST['notes']);` http://stackoverflow.com/questions/8627902/new-line-in-text-area — Jethro Hazelhurst, Mar 09 '17 at 10:56

score 1 · Answer 1 · answered Mar 09 '17 at 10:50

1

While fetching results from database , use nl2br()

answered Mar 09 '17 at 10:50

Bader

825
1
9
26

Function nl2br() inserts HTML line breaks before all newlines in a string, but I want to get back same string which I inserting, without new lines, with "\n" character. Is there a similar function which replaces new lines to character \n? – omfo Mar 09 '17 at 11:01

Abhishek · Answer 2 · 2017-03-09T11:30:02.203

The problem arises due to interpretation of '\n' as '\\n' in MySql. To avoid this conversion,try this approach: Change your query string before making an insert to database as following:

    $notes = $_POST['notes'];
    //This line will replace '\n' with HTML Coded Character Set, but will be interpreted in HTML as \n itself. 
    $notes = str_replace('\n', '&#92;&#110;', $notes);
    $sql = "INSERT INTO notes (notes) VALUES ('$notes')";

score 0 · Answer 3 · answered Mar 09 '17 at 11:47

0

"\n" is a next line statement, it is correct that MYSql take it as next line. Gotta use nl2br($value) to make it display as next line in html webpage.

answered Mar 09 '17 at 11:47

user3581646

33
1
6

score 0 · Answer 4 · answered Sep 28 '22 at 07:26

You Should use Textarea while inserting data and when you want to show/echo the data use this PHP function to preserve the line breaks echo nl2br($string);

For example

        <textarea class="form-control" name="short_desc" required></textarea>

and for frontend echo use following function

    <?PHP echo nl2br($short_desc);?>

It will preserve the line break and show content with breaks on frontend,

Also keep the Mysql data type text for it.

After the insert string into the MySQL, character '\n' (newline) disappears

4 Answers4