How do I store line-breaks from textarea properly in mySQL database?

Question

I've been having this issue with submitting and storing textarea content, to which I've been unable to find a proper solution.

I have a form, with a textarea field, called "description".

When I submit the form with multiline text, like

Line 1
Line 2

Line 3

It gets stored in the mySQL db with much more new lines. It becomes :-

Line 1

Line 2


Line 3

when I check the db with phpmyadmin.

I've tried both VARCHAR and TEXT types, its the same. What do I need to do to store the textarea content accurately?

The relevant codeigniter lines are aRow[$strField] = strip_tags($this->input->post($strField)); $this->db->insert($this->strTable, $aRow);

$strField will be my textarea field, and the insert is a CI function.

Solution 1

Okay I've found the answer. I'm using CI 2.0, and it is an existing bug that has already been reported. https://bitbucket.org/ellislab/codeigniter/issue/332/newlines-in-textareas-are-duplicated

The quick fix is as below (look for the file in /system/core/Input.php)

//$str = str_replace(array( "\r\n", "\r"), PHP_EOL, $str);
$str = preg_replace('/(?:\r\n|[\r\n])/', PHP_EOL, $str);

Hope this helps someone out there.

Solution 2

It seems like a newline is being added after each new line. This has to do with how the data is inserted into the database. Care to share that code?