how can I safely change the empty lines /n in the database to </p><p> so they will be rendered correctly when I change the field type?

Question

On a website with many many entries i've recently changed a textarea field to an expresso field.

I now have 2 types of entries, entries with formatting, and without formatting.

My idea so far:

• Buy low_search, use find and replace
• Write some SQL code, try to find the all the saved <p> tags and remove them.

Am I looking at this the wrong way? Is there a way to (re)enforce a text formatting (auto <br /> or none) to all entries on a site without opening and resaving each entry?

UPDATE: I logged in to the database and found out that the formatting hadn't changed. So no need to add <p> or <br />. Expression Engine does recognize the empty lines (/n) and somehow adds the neccesary tags when the page is rendered.

This changes my question a bit: Is there a way to add html text formatting (</p><p>) to all entries in the database so I can switch to another field type without losing my line breaks?

The original question was: "Is there a way to (re)enforce a text formatting (auto <br /> or none) to all entries on a site without opening and resaving each entry?"

UPDATE2: It turns out there is a way to add text-formatting to Expresso, which is the field type i'm using. It's quite simple:

In the file ft.expresso.php find the line

text-format => 'none', And change it to text=format => 'xhtml',

However, since I got so much help figuring this out AND I changed the question, because I thought it was impossible to change in Expresso itself I'm gonna select the answer given by nonprofit_tech as the correct answer.

Answer 1

ExpressionEngine allows you to natively, enforce or re-enforce format changes on a field.

This should be done before the switch to Espresso field_type.

Go to Admin and Select Channel Fields

Go to the field you want to transition to Espresso. Switch the field's default formatting.

You'll note an update option appears in orange under the format type once you've switched. This option will allow you to propagate the change to all entries, using the field.

This is the simplest option.

Unfortunately, with Expresso field_type, you want to do some of these modifications, BEFORE you switch field_types. For example, if you where using XHTML formatting. You'd want to switch that to <br /> to preserve white space, before switching to Espresso. Why? Because the white space under the current XHTML formatting doesn't actually use <p> or <br /> tags and Espresso doesn't recognize new line \r or \n marks.

If you've ALREADY switched field types and lost all your white space, the easiest way to return it, is rollback your database, perform the switches to the field before changing field_type. Because restoring whitespace after the fact is pretty much a manual operation.

UPDATED If you want to add the paragraph tags to the field, you can do so by running a MySQL update query:

UPDATE exp_channel_data SET field_id_### = REPLACE(field_id_###, '\r', '</p><p>') WHERE channel_id IN('3');
UPDATE exp_channel_data SET field_id_### = REPLACE(field_id_###, '\n', '</p><p>') WHERE channel_id IN('3');

Basically, for the specified field, in the specified channel, you are finding return marks replacing them with a close than open paragraph tag, then doing the same for the new lines. This works fine, with one exception, 'It doesn't provide the initial 'open' or the last 'close' paragraph tag. You can of course, do so on the template, by enclosing the field like so

<p>{expresso-field}</p>

or, you can drop into PHP to do it. Since we didn't want to modify a number of templates, one of our consultants (Joe Chellman) whipped up this

$db = mysql_connect ('ip_address', 'account', 'password');
mysql_select_db ( 'database' );

mysql_set_charset ( 'utf8' );

$q = "SELECT entry_id, field_id_XXX FROM exp_channel_data WHERE channel_id='##' AND field_id_XXX like '%\n%' AND field_id_XXX not like '%<p>%'";

$result = mysql_query($q);

while ($row = mysql_fetch_object ( $result )) {
    $entry_id = $row->entry_id;
    $body = $row->field_id_XXX;

    // recut the text
    $body = mysql_real_escape_string(paraWrapper($body));

    // put it back
    $restoreQuery = "UPDATE exp_channel_data SET field_id_XXX = '$body' WHERE entry_id = $entry_id";

// print $restoreQuery . "\n";
//  print $query2 . "\n";

    $restoreResult = mysql_query($restoreQuery);
    if (mysql_errno () != 0) {
        print 'Error on update for ' . $entry_id . ':' . mysql_error() . "\n";
    } else {
        print "Update of $entry_id successful.\n";
    }
}

// will you convert?!
// $q_convert = 'UPDATE exp_channel_fields SET field_type = "expresso" WHERE field_id = XX';

function paraWrapper($text) {
    $text = str_replace (array('<br><br>', '<br /><br />'), array('', ''), $text);
    return "<p>" . implode( "</p>\n\n<p>", preg_split( '/\n(?:\s*\n)+/', $text ) ) . "</p>";
}

The XXX, XX, and ## are where field numbers, channel id's are needed. The last part of the script, also finishes the conversion of the field, from text to espresso. Of course, that's all in a PHP script.

Answer 2

You can do this SQL query:

UPDATE exp_channel_data SET field_ft_X = 'none' WHERE channel_id = Y;

Where X is the field_id of the field in question and Y is the channel_id of the channel in question.

Answer 3

Here's non-SQL way to do it:

Go to the field settings page and change it's formatting to the value that you don't need. You will be shown warning in red and a checkbox, marking which would update all entries with new formatting. Do not check it, hit save.

Then, open the field settings again. Now, select your desired formatting and mark the checkbox to update formatting for all entries.

Voila! - all your entries have same formatting for given field.