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
Abstracts CPT posts (duplication of all posts when refreshing wp-admin in any fashion)
Any help would be greatly appreciated.
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
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