Wordpress trying to use wp_insert_post for new posts and wp_update_post for post updates - need post ID?

I am building a Wordpress site where we want to automatically populate wordpress posts from an EXTERNAL (not wordpress) database. I have already created a code that successfully checks the external database for post title and if it is not found, creates the post. This is great, but I am also trying to "update" the post if the title is found. I have attempted to update my code to do this, but when run the code in my plugin I get an error that says

Notice: Undefined variable: page_name in /var/www/html/dev2/wp-content/plugins/hello/hello.php on line 54 Fatal error: Cannot redeclare get_page_id() (previously declared in /var/www/html/dev2/wp-content/plugins/hello/hello.php:49) in /var/www/html/dev2/wp-content/plugins/hello/hello.php on line 49

Can anyone help me fix my code to insert NEW posts and update OLD posts? Here is my code, your expertise is much appreciated!!!

<?php
/*
Plugin Name: Post Updater
Plugin URI: http://wordpress.org/
Description: 
Author: Matt
Author URI: 
Version: 2.3
Text Domain: 
License: GPLv2 or later
License URI: http://www.gnu.org/licenses/gpl-2.0.html
*/

// Add Shortcode
function hello() {

    // Code
global $wpdb;

$sql = <<<SQL
SELECT cool_name, cool_content
FROM cool_test.cool_table 
SQL;




$rebates = $wpdb->get_results( $sql );
foreach ( $rebates as $rebate ){
    $new_post = array(
        'post_title' => $rebate->cool_name,
        'post_content' => $rebate->cool_content,
        'post_status' => 'publish',
        'post_date' => date('Y-m-d H:i:s'),
        'post_author' => 31,
        'post_type' => 'post',
        'post_category' => array(10)
    );
    $page_exists = get_page_by_title( $rebate->cool_name, OBJECT, 'post' );

    if( $page_exists == null ) {
        // Page doesn't exist, so lets add it
        $insert = wp_insert_post( $new_post );
        if( $insert ) {
            // Page was inserted 
        }
    } else {
        // Page already exists
        function get_page_id($page_name){
    global $wpdb;
    $page_name = $wpdb->get_var("SELECT ID FROM $wpdb->posts WHERE post_name = '".$page_name."'");
}
        $updatePost = array(
      'ID'           => $page_name,
      'post_content' => $rebate->cool_content
  );

// Update the post into the database
  wp_update_post( $updatePost );
    }


}


}
add_shortcode( 'hello', 'hello' );

?>

Solution 1:

First, interesting trick to use a shortcode for your one-off migration script :).

The error you see is because you are declaring function get_page_id($page_name) inside your foreach loop, which is why you are getting the error about it being undefined - it tries to define it once per loop, so it fails on the second iteration.

Although this is a migration script, there are some best practices to follow for generating SQL as well, such as using $wpdb->prepare(). That said, if you get a response from get_page_by_title() it will be a $post/$page object, so you can just reference $page->ID instead of making another query.

A cleaned up version of your code:

// Add Shortcode
function hello() {
    global $wpdb;
    $rebates = $wpdb->get_results( "SELECT cool_name, cool_content FROM cool_test.cool_table" );
    foreach ( $rebates as $rebate ){
        $page = get_page_by_title( $rebate->cool_name, OBJECT, 'post' );
        if( null === $page ) {
            // Page doesn't exist, so lets add it  
            $new_post = array(
                'post_title' => $rebate->cool_name,
                'post_content' => $rebate->cool_content,
                'post_status' => 'publish',
                'post_date' => date('Y-m-d H:i:s'),
                'post_author' => 31,
                'post_type' => 'post',
                'post_category' => array(10)
            );

            ;
            if( false !== ( $new_id = wp_insert_post( $new_post ) ) ){
                // Page was inserted, the new page ID is $new_id
            }
        } else {
            // Page already exists
            $updatePost = array(
                'ID'           => $page->ID,
                'post_content' => $rebate->cool_content
            );

            // Update the post into the database
            wp_update_post( $updatePost );
        }
    }
}
add_shortcode( 'hello', 'hello' );

Solution 2:

thank you for your help! For some reason, I was not able to get your code to work, however after some experimentation, I was able to get this code to work correctly. Any additional feedback or insight is much appreciated!

<?php
/*
Plugin Name: Incentive Post Updater
Plugin URI: http://wordpress.org/
Description: 
Author: Matt
Author URI: 
Version: 2.3
Text Domain: 
License: GPLv2 or later
License URI: http://www.gnu.org/licenses/gpl-2.0.html
*/

// Add Shortcode
function hello() {

    // Code
global $wpdb;

$sql = <<<SQL
SELECT cool_name, cool_content
FROM cool_test.cool_table 
SQL;




$rebates = $wpdb->get_results( $sql );
foreach ( $rebates as $rebate ){
    $new_post = array(
        'post_title' => $rebate->cool_name,
        'post_content' => $rebate->cool_content,
        'post_status' => 'publish',
        'post_date' => date('Y-m-d H:i:s'),
        'post_author' => 31,
        'post_type' => 'post',
        'post_category' => array(10)
    );
    $page_exists = get_page_by_title( $rebate->cool_name, OBJECT, 'post' );

    if( $page_exists == null ) {
        // Page doesn't exist, so lets add it
        $insert = wp_insert_post( $new_post );
        if( $insert ) {
            // Page was inserted 
        }
    } else {

    $updatePost = array(
                'ID'           => $page_exists->ID,
                'post_content' => $rebate->cool_content
            );

            // Update the post into the database
            wp_update_post( $updatePost );

    }


}
echo "cool";

}
add_shortcode( 'hello', 'hello' );

?>