When i copy a text and upload it the output looks like that

PROLOGUE\n\n\nIF you leave the city by the Porta Maggiore and take the Via\nPrænestina, which leads east into the Sabine hills, at some thirty-six\nkilometers

i want to replace \n with a line break

i use this

`UPDATE messages SET `text` = REPLACE( `text` , '\r' OR '\n' OR '\r\n') `

The **n** goes but the **\** stays 

Any help?

The \ stays

IMO the problem lies in the script that does the insert. Can you show that?

<textarea cols="35" rows="5" name="update_text_descr" id="update_text_descr" ></textarea>

$query = mysql_query("INSERT INTO `messages` (message, text ) VALUES ('$update', '$text_sample')") or die(mysql_error());
Member Avatar for diafol

Hmm the replace function should have 3 params. This works for me:

UPDATE users SET `field` = REPLACE(`field`, '\n', '<br />')

I'm assuming you're using some sort of seerver-side language with this. You can avoid hassle with a function like php's nl2br()

i did that already diafol didnt work

nl2br()?

That could work. What functions are you using on your text variable before the insert query?

Member Avatar for diafol

I'm not keen on storing text with html (<br />) included. \n or its variants seems a much more flexible way to store a newline. You can decide how you need your output replaced (nl2br()) or something else or as is (\n).

The question may be: Are you storing \n as a literal '\n' instead of a "proper" newline - I think this is what Pritaeas is getting at. So show your INSERT query (along with the php code). When you view the text directly in the table using phpMyAdmin or other GUI, I don't think you should actually see the \n in the table cell.

Using your original code which is removing the n but not the \\, all you need to do is escape the backslash with another backslash.

`UPDATE messages SET `text` = REPLACE( `text` , '\\r' OR '\\n' OR '\\r\\n') `

I did that i am getting an error.

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

this is what its stored in the row

Life in a German Crack Regiment\n\nAuthor: Wolf Ernst Hugo Emil Baudissin\n\nRelease Date: September 2, 2014 [EBook #46755]\n\nLanguage: English

this is the table

CREATE TABLE IF NOT EXISTS `messages` (
  `msg_id` int(11) NOT NULL AUTO_INCREMENT,
  `message` text CHARACTER SET utf8,
  `description` text CHARACTER SET utf8,
  `uid_fk` int(11) DEFAULT NULL,
  `ip` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created` int(11) DEFAULT '1269249260',
  `uploads` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `text` text CHARACTER SET utf8,
  `type` enum('P','A','V','T','E') CHARACTER SET utf8 DEFAULT NULL,
  `believe_count` int(11) DEFAULT '0',
  `review_count` int(11) DEFAULT '0',
  `suggest_count` int(11) DEFAULT '0',
  `network_id` int(11) DEFAULT NULL,
  `views_count` int(11) NOT NULL DEFAULT '0',
  `country_id` int(11) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  `venue` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dategig` datetime DEFAULT NULL,
  `fan_count` int(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`msg_id`),
  KEY `uid_fk` (`uid_fk`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=556 

the js that does the ajax

var text_sample=$("#text_sample").val();

    var dataString = 'update_text='+ updateval+'&uploads='+X+'&update_text_descr='+update_text_descr+'&text_sample='+text_sample+'&country_id='+country_id+'&c_id='+c_id;

the html
<textarea name="text_sample" cols="45" rows="15" id="text_sample"></textarea>

the php that does the ajax

$text_sample=mysql_real_escape_string($_POST['text_sample']);
    $uploads=$_POST['uploads'];
    $country=$_POST['country_id'];
    $category=$_POST['c_id'];
    $type = 'T';
    $data=$Wall->Insert_Update($uid, $update, $uploads, $type, $text_sample);

is the mysql_real_escape_string wrong?

I don't think the error you're getting is due to the additional backslashes. Make sure there's a semicolon (;) after the SQL query.

You could try using the HTML reference instead of the backslash but I don't think that will work because the data in the database has a backslash.

The HTML reference for a backslash is &#92; so the query would be:

UPDATE messages SET `text` = REPLACE( `text` , '&#92;r' OR '&#92;n' OR '&#92;r&#92;n')

i think that the syntax is wrong because after the '&#92;r' needs the value that it will replace it with

Member Avatar for diafol

this is what its stored in the row
Life in a German Crack Regiment\n\nAuthor: Wolf Ernst Hugo Emil Baudissin\n\nRelease Date: September 2, 2014 [EBook #46755]\n\nLanguage: English

If you actually see the \n in the phpmyadmin window, it sounds as though you've used addslashes or escaped the \ character in some way. I'm assuming therefore that "\n" is what you need to replace, as suggested by Borzoi.

i dont want to change only the existing texts on the database i want the \n replaced with <br> for any future uploading so i will use the nl2br() in the HTML?

i use this

$text = $data['text'];
$stext= $text;
echo nl2br($stext);

still the same

Member Avatar for diafol

OK, if you have a literal \n in your text as you seem to, then nl2br() will not work as a literal \n is not the same as a \n (newline).

You need to sort out your input procedure as it's escaping newlines before storing them for some reason.

ok i knew that was wrong. I have created a function and then called it but still the same

function remove_text($text)
    {
        $a = array("\n");
        $b = array("</br>");
        return str_replace($a, $b, $text); 
    }




      Can you link me a sample diafol?
Member Avatar for diafol

I'm reasonably sure it's your ajax javascript. Please show how you're passing the data - all the code inside/outside your $.ajax or $.post - whichever you're using.

It may be making your newlines literal, which is the root cause of your problem. Updating literal \n to newline in the DB table will be a separate issue.

you mean something like this? still didnt work

function remove_text($text)
    {
        $a = array("\n");
        $b = array("</br>");
        return str_replace($a, $b, $text); 
    }

    echo remove_text($text);
Member Avatar for diafol

you mean something like this?

No. I'll repeat:

Please show how you're passing the data - all the code inside/outside your $.ajax or $.post - whichever you're using.

the post that recieves the text

<textarea name="text_sample" cols="45" rows="15" id="text_sample"></textarea>

the ajax - highlighted the text sample

$(".text_update_button").click(function() 
{

    var updateval = $("#update_text").val();
    var update_text_descr=$("#update_text_descr").val();
    var text_sample=$("#text_sample").val();
    var country_id=$("#country_id").val();
    var c_id=$("#c_id").val()

    var X=$('.preview_text_photo').attr('id');
    var dataString = 'update_text='+ updateval+'&uploads='+X+'&update_text_descr='+update_text_descr+'&text_sample='+text_sample+'&country_id='+country_id+'&c_id='+c_id;
    if($.trim(updateval).length==0)
    {
        alert("Please enter a Title for your Text");
    }
    else if($.trim(text_sample).length==0)
    {
        alert("Please enter your Text");
    }
    else
    {
        $("#flash").show();
        $("#flash").fadeIn(400).html('Loading Rove...');
        $.ajax(
        {
            type: "POST",
            url: "message_ajax.php",
            data: dataString,
            cache: false,
            success: function(html)
            {
                $("#flash").fadeOut('fast');
                $("#content").prepend(html);
                $("#update_text").val('').focus();  
                $('#preview_text_photo').html('');
                $('#uploadvalues_text').val('');
                $('#update_text_descr').val('');
                $('#textphoto').val('');
                $('#text_sample').val('');
                var c=$('#update_count').html();
                $('#update_count').html(parseInt(c)+1);
                parent.$.colorbox.close();
            }
        });
        $("#preview_text_photo").html();
        $('#imageupload').slideUp('fast');
    }
    return false;
});

the message_ajax.php

elseif(isset($_POST['update_text']))
{
    $update=mysql_real_escape_string($_POST['update_text']);
    $update_description=mysql_real_escape_string($_POST['update_text_descr']);
    $text_sample=mysql_real_escape_string($_POST['text_sample']);
    $uploads=$_POST['uploads'];
    $country=$_POST['country_id'];
    $category=$_POST['c_id'];
    $type = 'T';
    $dataDummy=$Wall->Insert_Update($uid, $update, $uploads, $type, $text_sample, $update_description, $country, $category, $venue, $datetime);
    if($dataDummy)
    {
        include("html_messages.php");
    }
}

the Insert

$query = mysql_query("INSERT INTO `messages` (message, description, uid_fk, ip, created, uploads, text, type, country_id, category_id, venue, dategig) VALUES ('$update', '$update_description', '$uid', '$ip', '$time', '$uploads', '$text_sample', '$type', '$country', '$category', '$venue', '$datetime')") or die(mysql_error());
Member Avatar for diafol

Well that was a dead end. It worked fine for me - \n stored as newline.

This is the simple php I used:

<?php
if($_POST){
    $c = mysql_connect("localhost","root","....");
    mysql_select_db("daniweb");
    $var = mysql_real_escape_string($_POST['text_sample']);
    $q = mysql_query("INSERT INTO `mytable` (`myfield`) VALUES ('$var')");
    echo $var; //to display in alert or whatever
}
?>

Almost killed me to use mysql_* !!

SOMETHING HAPPENED AND I CAN SEE THE POSTS.

Member Avatar for diafol

Not sure if that is good or bad. What are we supposed to do now?

sorry man. That was so stupid of me. You should laugh. i didnt figure out the paging here and i was only seeing the first page.

So what i understand i should sort the text before saving it on the database. I'll try that.

Sorry again diafol

Member Avatar for diafol

heh heh, no worries.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.