Replacing data with SQL - Not updating in fields?

    I have a large number of pages in Perch, so need to do some bulk edits.


    I can do this with SQL queries no problem, however while it changes it in the database, and so on the live website, the fields in Perch will retain the old data.

    How can I make sure the fields in Perch in the back-end reflect what's in the database?


    Thanks!

    Byron Fitzgerald 's answer does not apply unfortunately.


    If I use an SQL query for example to find and replace content in regions in the database - the front-end site appears fine, however the data in the fields in Regions are not updated. Which means if I then go to edit a Region and publish it overwrites the change made in the SQL query.


    Can anyone suggest a way I can make changes using SQL queries in the DB and these changes be reflected in Regions? Thanks.

    Quote from Clive Walker

    if you are using perch_content in your pages, these will be cached. Are you saying that republishing the pages as mentioned above did not clear this cache and display your modified content?

    Thanks for the reply Clive. I am using perch_content. The issue is:

    • Content in a region is [a]
    • I use SQL to find and replace [a] with [b]
    • The front-end of the website now displays [b] in the browser
    • In perch, when I click into a page I still see [a] in the field for the region - so saving that page will just publish the original content again.
    Quote from Byron Fitzgerald

    What does you SQL script look like? It might not be changing all the necessary fields

    Very good point - I don't really know what I'm doing with SQL, but need to make changes in bulk this way as I have so many pages with content in regions.

    UPDATE `perch3_content_regions`

     SET `regionHTML` = replace(regionHTML, 'Original text', 'Replacement text')

  • So from your SQL query it looks like what you are doing is updating the cached HTML, which is why it shows correctly on the front end. In the admin site, perch ignores this so it will be showing the old content.


    I think you should be looking to update the itemJSON field in perch3_content_items. Then resaving will update the content_index as well as create the cached HTML for you

    Quote from Byron Fitzgerald

    So from your SQL query it looks like what you are doing is updating the cached HTML, which is why it shows correctly on the front end. In the admin site, perch ignores this so it will be showing the old content.


    I think you should be looking to update the itemJSON field in perch3_content_items. Then resaving will update the content_index as well as create the cached HTML for you

    Fantastic, thanks for checking for me!


    So would this be the correct statement to run?


    UPDATE `perch3_content_regions`

     SET `itemJSON` = replace(itemJSON, 'Original text', 'Replacement text')


    I can't actually see itemJSON anywhere.


  • You'll want the perch3_content_items table so your query will look something like

    SQL
    1. UPDATE
    2. `perch3_content_items`
    3. SET
    4. `itemJSON` = replace(`itemJSON`, 'Original text', 'Replacement text')

    It's worth noting that the field ID's are also stored in the itemJSON, so if the text you're replacing contains and ID of one of you're fields it will also overwrite that.

    Quote from Byron Fitzgerald

    You'll want the perch3_content_items table so your query will look something like

    SQL
    1. UPDATE
    2. `perch3_content_items`
    3. SET
    4. `itemJSON` = replace(`itemJSON`, 'Original text', 'Replacement text')

    It's worth noting that the field ID's are also stored in the itemJSON, so if the text you're replacing contains and ID of one of you're fields it will also overwrite that.

    Ahh I'm such an idiot. Thank you so much Byron! That's done the job. I really appreciate it :)