Reading values from Database, new lines giving issues

[SharkBait]

Ok I am still trying to figure this out (http://www.phpfreaks.com/forums/index.php/topic,232747.0.html) and let's see if I can better explain what is going on.

 

When I read in a field from a MySQL database that contains \n characters <textarea> or nl2br() does not work on it properly. If the string is hard coded into the script (not coming from a database) it works as expected.

 

This is my test code:

<?php
$str = "SELECT * FROM test LIMIT 1";
$qry = $MySQL->DoQuery($str, $DBLINK);
$r = $MySQL->FetchArray($qry);

$line2 = "This is a fun thing to do, because \nif it didnt work then where would we be right?";

$line = $r['value']; // this is EXACTLY same string as $line2 but just in the table instead
?>

<p>
From MySQL DB<br />
<textarea name="test" cols="50" rows="5"><?php echo $line;?></textarea></p>
<p>Hard Coded String<br />
<textarea name="test2" cols="50" rows="5"><?php echo $line2;?></textarea></p>

<table>
<tr>
<th>From MySQL DB</th>
</tr><tr>
<td><?php echo nl2br($line);?></td>
</tr>
</table>
<br />
<table>
<tr>
<th>Hard Coded String</th>
</tr><tr>
<td><?php echo nl2br($line2);?></td>
</tr>
</table>
<?php

The MySQL class is nothing more than mysql_query and mysql_fetch_array just simplified a bit more so I don't have to type in things like MYSQL_ASSOC etc. I have even replaced the MySQL class I wrote with php's mysql_query and mysql_fetch_array to ensure my class wasn't doing anything funny.

 

Anything to do with Magic Quotes is turned off.

 

What is happening to the data when it's read in from a database? Why doesn't nl2br() recognize the \n character? Why does the \n show up in the <textbox> when it technically shouldn't ?

[premiso]

It really depends on how the data was entered. If the magic quotes was on at one point and you escaped it again then it will show the "\n" character instead of a break. Do you see the "\n" ?

 

If so that was the problem and you can try a stripslashes before the nl2br function and see if that works. If it does that was your original problem:

 

   <td><?php echo nl2br(stripslashes($line));?></td>

 

If that does not do it, something else is going on. Let me know if that works or not and I will post information on how to fix your DB data.

[SharkBait]

When I strip slashes it removes the slash so just the n shows up.

 

When I manually run the query in MySQL via the shell this is the output

 

mysql> select * FROM test;
+----+-------------------------------------------------------------------------------------+
| id | value                                                                               |
+----+-------------------------------------------------------------------------------------+
|  1 | This is a fun thing to do, because \nif it didnt work then where would be be right? |
+----+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 

So there is no crazy escapes or anything funky happening, or at least I can see.

[premiso]

Echo out that to your browser without formatting and then view source and see if it is split at the \n character on a new line.

[SharkBait]

This is what I get when I 'View Source'

 

<p>
From MySQL DB<br />
<textarea name="test" cols="50" rows="5">This is a fun thing to do, because \nif it didnt work then where would be be right?</textarea></p>

<p>Hard Coded String<br />
<textarea name="test2" cols="50" rows="5">This is a fun thing to do, because 
if it didnt work then where would we be right?</textarea></p>

<table>
<tr>
<th>From MySQL DB</th>
</tr><tr>
<td>This is a fun thing to do, because \nif it didnt work then where would be be right?</td>
</tr>
</table>
<br />

<table>
<tr>
<th>Hard Coded String</th>
</tr><tr>
<td>This is a fun thing to do, because <br />
if it didnt work then where would we be right?</td>
</tr>
</table>

[trq]

You should not literally have \n stored in your database, something is wrong with your insert most likely.

[SharkBait]

You should not literally have \n stored in your database, something is wrong with your insert most likely.

 

So \n should not be going into the database and perhaps they should be replaced with <br /> and then changed back to \n when going into a value for the <textarea> ?

[trq]

So \n should not be going into the database and perhaps they should be replaced with <br /> and then changed back to \n when going into a value for the <textarea> ?

 

No, thats not the right approuch either. You shouldn't be storing the literal string \n in the first place. Its just a string.

[SharkBait]

Ok so.. perhaps I am confused about how to properly store a block of text in a database then.

 

If someone fills out a textarea with multiple 'paragraphs' how should that value be stored in the database then so that when it's retrieved it can be properly displayed?

 

If I view source for this thread there are <br /> between paragraphs. Are the <br /> stored in the database?

 

What I want is if the user enters data into a textarea box with linebreaks/newlines whatever they are will be shown when the data is is retrieved back from the database properly. But at the same time I'd want the 'format' to be saved so that when it goes back into a textarea to be edited, the line breaks/new lines are correct too.

[trq]

If someone fills out a textarea with multiple 'paragraphs' how should that value be stored in the database then so that when it's retrieved it can be properly displayed?

 

Apart from escaping bad characters you store the data as it is.

 

If I view source for this thread there are <br /> between paragraphs. Are the <br /> stored in the database?

 

No, the data is stored raw.

[premiso]

Ok so.. perhaps I am confused about how to properly store a block of text in a database then.

 

If someone fills out a textarea with multiple 'paragraphs' how should that value be stored in the database then so that when it's retrieved it can be properly displayed?

 

If I view source for this thread there are <br /> between paragraphs. Are the <br /> stored in the database?

 

What I want is if the user enters data into a textarea box with linebreaks/newlines whatever they are will be shown when the data is is retrieved back from the database properly. But at the same time I'd want the 'format' to be saved so that when it goes back into a textarea to be edited, the line breaks/new lines are correct too.

 

Post where the data is being entered into the DB.

 

And this site uses nl2br for displaying posts You should not store the <br> in the DB.

[SharkBait]

This is the script that inserts the values into the database

<?php
if(isset($_POST['submit'])) {
$errField = array();
foreach($_POST as $field => $value) {
	if($field != "submit" ) {
		switch ($field) {	
			// any special dealings?
			case 'id': 
				if(!isset($_GET['id'])) {
					// New entry skip id field
				}
				break;

			case 'reason':
			case 'summary':
				if(!empty($_POST[$field])) {
					$ECO[$field] = mysql_real_escape_string(trim($value));
				} else {
					$errField[$field] = 1;
				}
				break;
			default:
				if(!empty($_POST[$field])) {
					$ECO[$field] = mysql_real_escape_string(trim($value));
				} else {
					$errField[$field] = 1;
				}
				break;
		}
	}
}

// showPOST($ECO);
// exit();
if(count($errField) > 0) {
	$errMsg = "<div class=\"error\">(". count($errField) .") Errors were found, please ensure all fields are filled out properly.</div><br />\n";
} else {
	// Submission sucessful - INSERT into DB
	if(isset($_GET['action']) && $_GET['action'] == "edit") {
		$str = "UPDATE main_eco_data SET id = '{$ECO['id']}', title = '{$ECO['title']}', reason = '{$ECO['reason']}', cost ='{$ECO['cost']}', ".
				"Expires_On='{$ECO['Expires_On']}', implimentation_date = '{$ECO['implimentation_date']}', summary = '{$ECO['summary']}', PartNumber = '{$ECO['partnumber']}', date_last_modified = NOW() WHERE id = '{$ID}'";		} else {
		$str = "INSERT INTO main_eco_data (id, title, originator, reason, cost, PartNumber, implimentation_date, summary) ".
			"VALUES (NULL, '{$ECO['title']}', '{$_SESSION['ecos']['username']}', '{$ECO['reason']}', '{$ECO['cost']}', '{$ECO[partnumber]}', '{$ECO['implimentation_date']}', '{$ECO['summary']}')";
	}
	//echo "<p>{$str}</p>";
	$qry = $MySQL->DoQuery($str, $DBLINK);
	header("Location: {$_SERVER['PHP_SELF']}");
	exit();
}
}
?>

 

[trq]

I don't see anything out of the ordinary in there.