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.


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

Select Channel Fields from Admin

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

Update all Entries Option

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


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.

  • Thanks, that's quite a bit of explaining. However there seems to be something different going on. In the database there is still all the (original) whitespace. And when I switch back to textarea + xhtml formatting, everything is as it should be... – okenathan Feb 28 '13 at 19:18
  • Two things. First, if you didn't lose formatting that's great!! Second, the Expresso field isn't automatically adding the <p> tags for you? Also, see updated response. – nonprofit_tech Feb 28 '13 at 21:14
  • No Expresso field does not add the '<p>'. It that possible? – okenathan Mar 1 '13 at 11:07
  • You really helped me a lot! I found another way and am not sure if I would've found it without your help. – okenathan Mar 4 '13 at 16:06
  • Congrats! Glad you found something that worked. – nonprofit_tech Mar 4 '13 at 16:43

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.

  • Thanks, this does indeed change the text formatting on output. It doesn't affect the field data (in the database), so when I switch to the Expresso field type, I still don't have html tags. – okenathan Feb 28 '13 at 19:31

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.