PHP/MySQL field recognize carriage returns?

Report · May 17, 2007

I'm a novice PHP/MySQL database driven site builder. I need to have users enter text into a field with carriage returns and have the database recognize and store those carriage returns so it displays when the data is displayed on a PHP page.

How do I do this and please keep it simple?
Do I need to use a particular type of MySQL datafield?
Will I be able to use the same code on an "update" page?

Report · May 17, 2007

'\n' is the character return escape sequence (think \newline).

Generally you will need to escape this a varying number of times depending on how many times the string is going to be parsed. For example, when writing java regular expressions, you need to double or even triple-escape some characters. For example, a literal backslash is encoded as:
"\\\\"
After this is parsed to a string, the Pattern class sees:
"\\"
Which is translated to:
"\"

It's been a while since i've done LAMP work, but I would hazard a guess that you need to double-escape your newlines when building your query.

Using concatenation, this would look like:
$query .= $rest_of_query . '\\n';

This way the ACTUAL STRING passed to MYSQL is something like "Line 1.\nLine 2.", with the characters '\' and 'n', not a newline - you want this because MYSQL itself will probably parse the string again.

Play around with it and see what happens. You may need to apply a similar process when extracting it - something like:
str_replace($result, '\n', '\\n');
to get it to display properly.

Report · May 17, 2007

>>
so it displays when the data is displayed on a PHP page
>>

if you just need to replace the \n - type line breaks generated by multiline textareas with <br /> on a page, just use PHP´s native "nl2br" (new line to break) function:

<?php echo nl2br($row_queryname['field_name']); ?>

Report · May 18, 2007

quote:

Originally posted by: geschenk
>>
so it displays when the data is displayed on a PHP page
>>

if you just need to replace the \n - type line breaks generated by multiline textareas with <br /> on a page, just use PHP´s native "nl2br" (new line to break) function:

<?php echo nl2br($row_queryname['field_name']); ?>

That works... sort of...

When I use the update page to modify the entry it puts the proper "br" code in it's proper place. However when I return to that page to edit the text again (as my customer likely will), it displays the "br" code in the text field, and if I submit the update with the "br" code in place, it duplicates the "br" code again, so I get double "br" statements. How do I get the update field to read the "br" code back as carriage returns?

In addition, my insert page uses the following code to write the original entry, and I'm not sure where to place the "nl2br" statement in the following query (generated by WebAsssist DW extensions):

<?php
// WA Application Builder Insert
if (isset($_POST["Insert_x"])) // Trigger
{
$WA_connection = $nm_connect;
$WA_table = "tb_news";
$WA_sessionName = "WADA_Insert_tb_news";
$WA_redirectURL = "tb_news_Results.php";
$WA_keepQueryString = false;
$WA_indexField = "id";
$WA_fieldNamesStr = "sort|s_head|s_text|head|subhead|bodytext|photoid|photocaption|name|phone|email|linktitle|linkaddress|show_pfm|show_arch|show_cab";
$WA_fieldValuesStr = "".((isset($_POST["sort"]))?$_POST["sort"]:"") ."" . "|" . "".((isset($_POST["s_head"]))?$_POST["s_head"]:"") ."" . "|" . "".((isset($_POST["s_text"]))?$_POST["s_text"]:"") ."" . "|" . "".((isset($_POST["head"]))?$_POST["head"]:"") ."" . "|" . "".((isset($_POST["subhead"]))?$_POST["subhead"]:"") ."" . "|" . "".((isset($_POST["bodytext"]))?$_POST["bodytext"]:"") ."" . "|" . "".((isset($_POST["photoid"]))?$_POST["photoid"]:"") ."" . "|" . "".((isset($_POST["photocaption"]))?$_POST["photocaption"]:"") ."" . "|" . "".((isset($_POST["name"]))?$_POST["name"]:"") ."" . "|" . "".((isset($_POST["phone"]))?$_POST["phone"]:"") ."" . "|" . "".((isset($_POST["email"]))?$_POST["email"]:"") ."" . "|" . "".((isset($_POST["linktitle"]))?$_POST["linktitle"]:"") ."" . "|" . "".((isset($_POST["linkaddress"]))?$_POST["linkaddress"]:"") ."" . "|" . "".((isset($_POST["show_pfm"]))?$_POST["show_pfm"]:"") ."" . "|" . "".((isset($_POST["show_arch"]))?$_POST["show_arch"]:"") ."" . "|" . "".((isset($_POST["show_cab"]))?$_POST["show_cab"]:"") ."";
$WA_columnTypesStr = "none,none,NULL|',none,''|',none,''|',none,''|',none,''|',none,''|',none,''|',none,''|',none,''|',none,''|',none,''|',none,''|',none,''|none,none,NULL|none,none,NULL|none,none,NULL";
$WA_fieldNames = explode("|", $WA_fieldNamesStr);
$WA_fieldValues = explode("|", $WA_fieldValuesStr);
$WA_columns = explode("|", $WA_columnTypesStr);
$WA_connectionDB = $database_nm_connect;
mysql_select_db($WA_connectionDB, $WA_connection);
if (!session_id()) session_start();
$insertParamsObj = WA_AB_generateInsertParams($WA_fieldNames, $WA_columns, $WA_fieldValues, -1);
$WA_Sql = "INSERT INTO " . $WA_table . " (" . $insertParamsObj->WA_tableValues . ") VALUES (" . $insertParamsObj->WA_dbValues . ")";
$MM_editCmd = mysql_query($WA_Sql, $WA_connection) or die(mysql_error());
$_SESSION[$WA_sessionName] = mysql_insert_id();
if ($WA_redirectURL != "") {
if ($WA_keepQueryString && $WA_redirectURL != "" && isset($_SERVER["QUERY_STRING"]) && $_SERVER["QUERY_STRING"] !== "" && sizeof($_POST) > 0) {
$WA_redirectURL .= ((strpos($WA_redirectURL, '?') === false)?"?":"&").$_SERVER["QUERY_STRING"];
}
header("Location: ".$WA_redirectURL);
}
}
?>

Report · May 18, 2007

well, maybe I should have explained this better :: nl2br is *only* to be used on the page that´s displaying the field´s contents *to the website visitors* -- not in your forms !

>>
However when I return to that page to edit the text again (as my customer likely will), it displays the "br" code in the text field
>>

...and that´s what should not happen -- it seems that you additionally applied nl2br to the textarea of your "update" form as well (e.g. <?php echo nl2br($row_queryname['fieldname']); ?>), true ?

If yes, please delete that -- means your textarea needs to look like this:

<textarea name="whatever" id="whatever" cols="50" rows="5"><?php echo $row_queryname['fieldname']; ?></textarea>

Report · May 18, 2007

Ok,
So are you saying the carriage returns actually make it into the database, but they are not recognized when pulled through the query to display on a page (i.e. the problem is not with the writing, but with the reading)? So I don't need to do anything special my "insert record" page to hold carriage returns? If that's the case, how do I get the info to display properly on my "update" page where it pulls data from the database and then rewrites it to the same record (see below) without having the looping problem I discussed earlier?

<textarea name="bodytext" cols="60" rows="6" id="bodytext"><?php echo $row_WADAtbnews['bodytext']; ?></textarea>

Report · May 18, 2007

>>
So are you saying the carriage returns actually make it into the database,
>>

using a regular textarea the "invisible "carriage return char '\n' usually make it into the database -- both on insert & update

>>
So I don't need to do anything special my "insert record" page to hold carriage returns?
>>

not on the insert and not on the update page -- the textarea´s '\n' line breaks are getting transmitted "in the background" without requiring you to do anything

>>
but they are not recognized when pulled through the query to display on a page
>>

the pulled '\n' chars don´t have any "visual effect", that´s why you need the "nl2br" function to replace them with HTML <br />

>>
If that's the case, how do I get the info to display properly on my "update" page where it pulls data from the database and then rewrites it to the same record (see below) without having the looping problem I discussed earlier?
><

you probably must have applied the nl2br function to your update form´s textarea, otherwise you wouldn´t have got the unwanted "it puts the proper "br" code in it's proper place" result -- please do this:

delete the nl2br() stuff from your insert & update forms (if applicable), save the pages, update your records and delete any <br> in there -- you shouldn´t see them in there the next time, but you should see real HTML line breaks on your public webpages by applying the "nl2br" - function to the output as described above. Works ?

Report · May 17, 2007

geschenk wrote:
> <?php echo nl2br($row_queryname['field_name']); ?>

nl2br() is now built into Dreamweaver CS3 in the Dynamic Text format menu.

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

Report · May 17, 2007

On Thu, 17 May 2007 21:37:49 +0000 (UTC), "VVebbie"
<webforumsuser@macromedia.com> wrote:

> Generally you will need to escape this a varying number of times depending on
>how many times the string is going to be parsed. For example, when writing java
>regular expressions, you need to double or even triple-escape some characters.
>For example, a literal backslash is encoded as:

You could stuff a hundred newlines into the string and the browser would
still collapse that whitespace. Use the nl2br() function to replace the
newlines that are stored in the field with <br /> tags as was suggested
in the other replies.

Gary

Report · May 18, 2007

Well, i've covered the storage aspect and you've covered retrieval, so it's all good.

Report · May 18, 2007

On Fri, 18 May 2007 14:59:09 +0000 (UTC), "VVebbie"
<webforumsuser@macromedia.com> wrote:

>Well, i've covered the storage aspect and you've covered retrieval, so it's all good.

Actually, no. You shouldn't need to do anything special when you store
the data. The only thing you need is to replace the newlines with <br>
tags when you want to display it in a web page.

Gary