“How do I differentiate a new post from updating a post on the save_post hook in WordPress?”

I’m sharing vacancy posts from a local website to a shared SQL database which other websites need to be able to read and update to as well.

I am able to successfully create and update posts locally and push them to the shared database with a pdo connection which is great, however I have a problem with the save_post hook, as it gets called multiple times (it triggers upon creation and update).

This causes duplications as I have an SQL insert query and an SQL update query. I need to be able to insert only the first time and every action after that to be an update.

I’m not sure if the save_post hook is the issue (but that seems to be the only applicable hook). I have looked at a few checks, but wp_is_post_revision didn’t prevent the duplication, and apparently the $update parameter doesn’t actually work for this purpose (which you would have thought it would!).

In pseudo code, I want to do this:

// function update_or_insert_vacancy( $post_id ) {
    
if (post does not exist) {
   //as well as the usual local creation, INSERT INTO wp_posts (post_name, post_content, post_title, post_excerpt , to_ping , pinged , post_content_filtered) VALUES ... to remote shared database
} else { 
    //exactly as on the local site 
    UPDATE wp_posts SET column1 = value1, column2 = value2...  on remote shared database too
}

// add_action( 'save_post', 'update_or_insert_vacancy' );

So to reiterate, I want to replicate what wordpress normally does, but share this to the shared database, and allow anyone the functionality to update a vacancy. At the moment, save post triggers when you click ‘add new’ and when you click ‘update’, so I get a two posts created when I only want one. I want to separate the creation from the update, but save_post doesn’t seem to allow for this. Is there a way? I would like to make it as similar to the usual workflow as possible. It’s like a second remote copy of the vacancy that can be read/written by the other site too. Ie, I want to be able to create a vacancy custom post type on one site and see it on another, and be able to update it on the other site as well, as if it was created on that site.

To make sure there are no conflicts in the shared database, I am not inserting with an id, but letting the database add one to the number programmatically. I don’t want to use SQL to check the last entry if it exists or to update as I find it gets messy and unreliable very quickly. That’s why I’m hoping for a wordpress php solution.

There must be some way to differentiate between a new post and updating a post so I can use different sql statements in each scenario to avoid duplication?

The duplication occurs when the insert is called twice, but an update wouldn’t insert a new post if there wasn’t one there– I can use the id of the post that has already been created to update it.

I hope that makes sense, thank you

Update

This function pretty much works but when I update a post it updates all the other posts in the remote database that preceded it, so I need one extra check to avoid that, either something in the sql statement or if post doesn’t exist or something like that? add post 1 [![add post 3][3]][3]. I added a post with post title ‘status check’ which updated everything behind it. Then I added Status Check 1 which did the same but correctly became post id 821. I want just the correct post to update its post_title correctly.

function insert_update_sql( $post_id, $post, $update ) {


// If an vacancy is being updated
if ( $update ) {
//exactly as on the local site

 $vacancies = get_posts( array( 'post_type' => 'vacancies', 'numberposts'    => -1,));

 $vacancy_title = $vacancies[0]->post_title;

$servername = "*";
$username = "*";
$password = "*";
try {
     $conn = new PDO("mysql:host=$servername;dbname=vacancies", $username, $password);
     // set the PDO error mode to exception
     $conn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, PDO::ERRMODE_EXCEPTION);

       $sql="
   UPDATE wp_posts

   SET guid = 'a', post_status = 'b', post_name = 'c', post_content = 'd', post_title = '$vacancy_title', post_excerpt = 'f', to_ping = 'g', pinged = 'e', post_content_filtered = 'f';";

  $conn->exec($sql);

  echo "New record created successfully. Last inserted ID is: " ; //can include $last_id;

   } catch(PDOException $e) {
     echo $sql . "<br>" . 'update' . $e->getMessage();
   }

   $conn = null;


} else {

   $servername = "*";
    $username = "*";
    $password = "*";
  try {
        $conn = new PDO("mysql:host=$servername;dbname=vacancies", $username, $password);
        // set the PDO error mode to exception
        $conn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, PDO::ERRMODE_EXCEPTION);

          $sql="
      INSERT INTO wp_posts (guid, post_status, post_name, post_content, post_title, post_excerpt , to_ping , pinged , post_content_filtered) VALUES ('$the_post_guid','$the_post_status','$the_post_name' , 'EMPTY', '$the_post_title', 'EMPTY', 'EMPTY', 'EMPTY', 'EMPTY');";

   $conn->exec($sql);

    echo "New record created successfully. Last inserted ID is: " ; //can include $last_id;

      } catch(PDOException $e) {
        echo $sql . "<br>" . 'insert' . $e->getMessage();
      }

      $conn = null;


}
}
add_action( 'save_post_vacancies', 'insert_update_sql', 10, 3 );