Mysql not retaining line breaks from Jquery ajax post?

Question

I've got a PHP/Mysql app that lets users post text from a form. When I insert text from an HTML textarea into my mysql table, it's not keeping the carriage returns/line breaks. The text is not stored in the DB as "Hey SO, \n This is a new line". It's stored with white space in the column (exactly like it's typed), but there is no way for me to output it with nl2br() and keep the breaks. I'm escaping before inserting the text like so:

$foo_text = mysql_real_escape_string(ucfirst($_POST['foo_text']));

But even if I remove everything and just use the POST parameter, it still does the same thing. Would this have anything to do with me serializing and posting this form via ajax (I'm using JQUERY)? I found this on stackoverflow, but I don't really see a solution. I'm posting with:

$.ajax({
        type: "POST",
        url: "insertFooBar.php",
        data: $("#foo_form").serialize(),
        success: function(msg) {
            ETC...
        }
    })

Is there something really obvious I'm missing here? I'm stuck...

Thanks in advance for any help!

I don't think line breaks belong in the database. Those are a rendering concern, and operating system dependent to boot. — duffymo, Feb 21 '10 at 18:54
MySQL will store line breaks in a field, they're being removed by something else if you're sure they're not in the database — David Snabel-Caunt, Feb 21 '10 at 18:58
Hey duffymo, I'm not sure how else I would be able to store input as the user intended without saving the line breaks in the DB? David, thanks for the response, think you're right. I just checked firebug, and it looks like this is going to be Jquery specific... it's removed the "\n"s before it ever gets to my back-end script. — Adamjstevenson, Feb 21 '10 at 19:02

score 3 · Answer 1 · edited Dec 02 '20 at 21:47

The problem is that serialization should encode a line break as %D0%DA, but jQuery encodes it as %0A.

The only (graceless) solution i found was to get the form serialized string, then modify it with a replacement function such as :

function keepLB (str) {    
  var reg=new RegExp("(%0A)", "g");
  return str.replace(reg,"%0D$1");
}

Once the serialized string is modified, i send it using the $.post() function.

Hope it will help !

score 2 · Accepted Answer · answered Feb 21 '10 at 20:25

Thanks for the answers. I ended up removing the serialize() and sending each parameter manually as a string. I added $("#foo_bar").replace( /\n/g, '<br>' )) to my textarea as a workaround and now I'm getting my breaks. Wish I didn't have to hack this to make it work, but it gets the job done.

score 1 · Answer 3 · answered Feb 21 '10 at 19:12

I never got any problems inserting data from a HTML Form into database, either the form submitted normally or using AJAX.

Have you try to submit the form without AJAX? What is the result? I want to suggest you to use jQuery form plugins so you don't have to do the AJAX request manually.

I'm also want to suggest you to use AdoDB to save the data into database. This library provide a great cross-database abstraction. I haven't found any problems when insert/update the data, all value escaping is done automatically. Here is the example way to do update using AdoDB:

$data['foo_text'] = ucfirst($_POST['foo_text']);
$adodb->AutoExecute($tablename, $data, 'UPDATE', "id=$id");

I hope this libraries will help you.

Mysql not retaining line breaks from Jquery ajax post?

3 Answers3