Duplicate posts when posting nulls in records in phpMyAdmin

The question:

I have a phpMyAdmin database with a table whose contents, when inputted from the database, automatically publish the related information.

Some of the details within the record may be NULL, and while testing I encountered the issue that if any of the content within any record is NULL, all the records are duplicated when reloading from wp-admin. The number of records within the database remains unchanged.

I am quite sure there is a way to deal with NULLS in this context but I am very inexperienced with WordPress and PHP in general and could not find a relevant topic on it.

Below is the relevant code and screenshots to better explain the context:

Publication-CPT.php (plugin)

if(!function_exists('add_action'))
{
    echo 'ERROR: ABSPATH UNDEFINED. Access to this file is not allowed.';
    exit;
}

function create_abstract_cpt() {

    $labels = array(
        'name' => _x( 'Abstracts', 'Post Type General Name', 'textdomain' ),
        'singular_name' => _x( 'Abstract', 'Post Type Singular Name', 'textdomain' ),
        'menu_name' => _x( 'Abstracts', 'Admin Menu text', 'textdomain' ),
        'name_admin_bar' => _x( 'Abstract', 'Add New on Toolbar', 'textdomain' ),
        'archives' => __( 'Abstract Archives', 'textdomain' ),
        'attributes' => __( 'Abstract Attributes', 'textdomain' ),
        'parent_item_colon' => __( 'Parent Abstract:', 'textdomain' ),
        'all_items' => __( 'All Abstracts', 'textdomain' ),
        'add_new_item' => __( 'Add New Abstract', 'textdomain' ),
        'add_new' => __( 'Add New', 'textdomain' ),
        'new_item' => __( 'New Abstract', 'textdomain' ),
        'edit_item' => __( 'Edit Abstract', 'textdomain' ),
        'update_item' => __( 'Update Abstract', 'textdomain' ),
        'view_item' => __( 'View Abstract', 'textdomain' ),
        'view_items' => __( 'View Abstracts', 'textdomain' ),
        'search_items' => __( 'Search Abstract', 'textdomain' ),
        'not_found' => __( 'Not found', 'textdomain' ),
        'not_found_in_trash' => __( 'Not found in Trash', 'textdomain' ),
        'featured_image' => __( 'Featured Image', 'textdomain' ),
        'set_featured_image' => __( 'Set featured image', 'textdomain' ),
        'remove_featured_image' => __( 'Remove featured image', 'textdomain' ),
        'use_featured_image' => __( 'Use as featured image', 'textdomain' ),
        'insert_into_item' => __( 'Insert into Abstract', 'textdomain' ),
        'uploaded_to_this_item' => __( 'Uploaded to this Abstract', 'textdomain' ),
        'items_list' => __( 'Abstracts list', 'textdomain' ),
        'items_list_navigation' => __( 'Abstracts list navigation', 'textdomain' ),
        'filter_items_list' => __( 'Filter Abstracts list', 'textdomain' ),
    );
    $args = array(
        'label' => __( 'Abstract', 'textdomain' ),
        'description' => __( 'List of all the abstracts', 'textdomain' ),
        'labels' => $labels,
        'menu_icon' => 'dashicons-text',
        'supports' => array('title', 'editor', 'excerpt', 'thumbnail', 'author', 'post-formats', 'custom-fields'),
        'public' => true,
        'show_ui' => true,
        'show_in_menu' => true,
        'menu_position' => 5,
        'show_in_admin_bar' => true,
        'show_in_nav_menus' => true,
        'can_export' => true,
        'has_archive' => true,
        'hierarchical' => false,
        'exclude_from_search' => false,
        'show_in_rest' => true,
        'publicly_queryable' => true,
        'capability_type' => 'post',
        'taxonomies' => array( 'category', 'post_tag' ),
    );
    flush_rewrite_rules();
    register_post_type( 'abstract', $args );

}
add_action( 'init', 'create_abstract_cpt', 0 );

add_action( 'wp', 'insert_into_cpt');

function verify_existing_abstract_in_cpt() 
{

    $id_arrays_in_cpt = [];
    // Query all autos
    $args = array(
        'post_type'      => 'abstract',
        'posts_per_page' => -1,
    );

    $loop = new WP_Query($args);
    while ( $loop->have_posts() ) {
        $loop->the_post();
        $id_arrays_in_cpt[] = get_post_meta( get_the_ID(), 'abstract_id', true);
    }
    return $id_arrays_in_cpt;
}

function query_abstract_post_table( $available_in_cpt ) 
{
    // Query Database
    global $wpdb;
    $table_name = $wpdb->prefix . 'abstract_posts';
    if ( NULL === $available_in_cpt || empty($available_in_cpt) || 0 === $available_in_cpt) {
        $sql = "SELECT * FROM $table_name";
    } else {
        $ids = implode( ",", $available_in_cpt);
        $sql = "SELECT * FROM $table_name WHERE abstract_id NOT IN ('$ids')";
    }

    $results = $wpdb->get_results( $sql );
    return $results;
}

function insert_into_cpt()
{
    //If the queried results from do not match the existing abstracts
    $available_in_cpt = verify_existing_abstract_in_cpt();
    $database_results = query_abstract_post_table( $available_in_cpt );

    //Insert into CPT
    foreach($database_results as $result) 
    {
        // Create post object
        $abstract_details = array(
            'post_title' => $result->title,
            'meta_input' => array(
                'abstract_id' => $result->abstract_id,
                'title' => $result->title,
                'author' => $result->author,
                'supervisor' => $result->supervisor,
                'cosupervisor' => $result->cosupervisor,
                'course' => $result->course,
                'categ' => $result->categ,
                'writeup' => $result->writeup,
                'firstabstractimage' => $result->firstabstractimage,
                'firstabstractimagecaption' => $result->firstabstractimagecaption,
                'secondabstractimage' => $result->secondabstractimage,
                'secondabstractimagecaption' => $result->secondabstractimagecaption,
                'ref' => $result->ref,
            ),
            'post_type'   => 'abstract',
            'post_status' => 'publish',
        );
        //Insert into wp_insert_post
        wp_insert_post( $abstract_details );
    }
}

wp_abstract_posts (Table) – Inclusion of NULL in any record causes this issue

Duplicate posts when posting nulls in records in phpMyAdmin

Abstracts CPT posts (duplication of all posts when refreshing wp-admin in any fashion)

Duplicate posts when posting nulls in records in phpMyAdmin

Any help would be greatly appreciated.

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

Problem is within function query_abstract_post_table where you have '$ids' wrapped in single quotes:

Problem:

$sql = "SELECT * FROM $table_name WHERE ID NOT IN ('$ids')";

In the above example, only the first ID returned in the concatenated string (from the array) will be recognized by the NOT IN clause.

Solution:

$sql = "SELECT * FROM $table_name WHERE ID NOT IN ($ids)";


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment