SQL trigger failes with post_content

Question

Let me tell you my story... I was having an issue with  char visible within the post's content. Instead of editing each one, I was passing a simple query:

UPDATE wp_posts SET post_content = REPLACE( post_content, binary 'Â', '' );

Everything was fine so far, however, after adding new posts the issue was back again, so I thought trigger would be the key! So I've written one as follows:

CREATE TRIGGER watch_posts 
  AFTER INSERT ON wp_posts 
  FOR EACH ROW 
  UPDATE wp_posts SET post_content = REPLACE( post_content, binary 'Â', '' );

From this point within most of the posts I'm having now blank edit page where word count is equal to 0. But the database still consist of post_content which is not empty. How to prevent existing posts to not be blank anymore?

Answer 1

I've written a filter for you. It will filter the character before the post get saved in the database and it's using the WordPress way. If you use this I think you're not gonna be needed the trigger. Here it is-

add_filter( 'wp_insert_post_data' , 'the_dramatist_filter_content_before_insert' , '99', 2 );

function the_dramatist_filter_content_before_insert( $data , $postarr ) {

    $search  = array('Â'); // You can add other replacement finding here also
    $replace = array(''); // The replacements

    $data['post_content'] = str_replace($search, $replace, $data['post_content']);

    // Now just return the data
    return $data;
}

Hope that thing helps.