Saving Special Characters along with Line Breaks

[rashid100]

Hi

I am using the php to inserting the records in mysql table. My table has following feilds

id int auto increament
data longtext

via php i am inserting the record

  $data         = isset($_REQUEST['data'])?$_REQUEST['data']:""; 

   if (isset($_REQUEST['submit']))       
   { 
    
      $data=htmlentities(mysql_real_escape_string($data));     
      $query=mysql_query(insert into special values($data)); 
} 

and when I echo this record after retrieving from table its shows me in Single Line whatever user input in multiple lines or in a paragraph with some special characters also.

           $result1=mysql_query("select * from special"); 
        if (mysql_num_rows($result1)){ 

                     while($row=mysql_fetch_array($result1)){ 

                $str=$row['data']; 
  
  
         $str=html_entity_decode(stripslashes($str)); 

         echo "<textarea>".$str."</textarea>"; 
         } 


          
          
         } 

I want to show in a original data whats the user actually insert.Can any body help me in this matter.

Thanks

[Frenz48]

To get your data on multiple lines use nl2br()
Struggling with special characters myself.

[rashid100]

Thanks for the reply but the problem is that when use the mysql_real_escape_string function its does not work did u use this and if yes then what format you are saving the data and then retrieved it.

[Frenz48]

Ah, i use prepared statements. With prepared statements there is no need to escape and thus remove the /n.(I Might be wrong! i have not yet actually hosted any project with DB!)
And i should mention mysql_* being old, insecure and will be deprecated soon!

Look at PDO or mysqli

I have found PDO easy to use myself. And prepared statements make things much easier to secure.

[Lemon Juice]

Multiple lines show as one because you are outputting them to html and in html every newline is treated as a space. nl2br() is the answer - however, nl2br() is for standard text display, it is not needed for <textarea>, as textareas don't collapse newlines, tabs and spaces into spaces, they display text with all whitespace characters as they appear in HTML.

However, you are complicating things too much with the encoding functions. When storing text data from user input don't use htmlentities() - this function doesn't belong there as its purpose is to format data for html output and not for db storage. Simply use:

$data=mysql_real_escape_string($data); 

Edit: I forgot one thing - if you have magic_quotes turned on on your server then you need to use stripslashes() before you store the text in the db. So if $data comes from $_REQUEST, then you should use this:

$data=mysql_real_escape_string(stripslashes($data)); 

Then when you want the exact string to appear in a textarea, use this:

$str=$row['data'];  // data from db
$str=htmlspecialchars($str); 
echo "<textarea>".$str."</textarea>"; 

This is all you need to do.

And use a unicode character set (like UTF-8) on your pages and you won't have to worry about entity encoding/decoding. Stripslashes() is also redundant.

[rashid100]

Thank you very much Lemon Juice for your reply well I did follow your instructions but unfortunately my result is same remain. I am using the these code

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

and for inserting the record

$data=mysql_real_escape_string($data); 

and for viewing

$str=$row['data'];  // data from db
$str=htmlspecialchars($str); 
echo "<textarea>".$str."</textarea>"; 

or even If I use this

echo "<textarea>" . htmlspecialchars($str, ENT_QUOTES, 'UTF-8') . "</textarea>"; 

User Input is

Hello how are you
and he is" is now " what' are

and the result in one single Line

Hello how are you\r\nand he is\\ is now \\ what\' are

[Lemon Juice]

This looks as if you are using mysql_real_escape_string() twice on the same data. Post all your code for inserting and displaying so we can see the larger picture.

One thing you can do now is to check if your insert code works properly. Connect to the database with a database management tool (like HeidiSQL, phpmyadmin, etc.) and look at the data that has been inserted. If your insert code works well then you should see the text exactly as entered in the form (in multiple lines without any special escape or other weird characters). This way you will be able to pinpoint where the problem is.