Insert all post IDs in new database table

3 Views

I have a plugin which creates a new database table. Upon activating the plugin is there a way to insert all my posts ids into that table?

Right now I’m using an action hook to immediately insert the id of new posts I publish.

Read More
function transfer_post_id($post_ID)  {
global $wpdb;
   if (!($wpdb->get_row("SELECT post_id FROM $my_table WHERE post_id = $post_ID" ) ) ) { //if post id not already added
      $wpdb->insert( $my_table, array( 'post_id' => $post_ID ) );      
   }
   return $post_ID;
}

add_action ( 'publish_post', 'transfer_post_id' );

However there are posts before activating the plugin that I need a way to insert without manually updating each one.

Post Views: 3

Related posts

Leave a Reply

3 comments

  1. Like so:

    INSERT INTO $mytable (post_id)
SELECT ID
FROM $wpdb->posts as posts
LEFT JOIN $mytable as dup_check
ON dup_check.post_id = posts.ID
WHERE dup_check.post_id IS NULL;

  2. You can design your table in such a way that an ID can only exists once by using a key. You can then run a insert into query that selects existing ID values directly with a subselect.

    Like so (credits Denis):

    INSERT INTO $mytable (post_id)
SELECT ID
FROM $wpdb->posts as posts
LEFT JOIN $mytable as dup_check
ON dup_check.post_id = posts.ID
WHERE dup_check.post_id IS NULL;

    Check the MySql manual regarding the syntax of the INSERT statement.

  3. I’m so sorry! I lost login please forgive me, apparently I used an incorrect email. I’ll contact the forum admin to see how I can recover my username..

    Denis thanks for the code!

    I’m using it like so

       $my_table = $wpdb->prefix . "rated_posts";

   $wpdb->query( "INSERT INTO $my_table (rated_post_id)
                SELECT ID
                FROM $wpdb->posts as posts
                LEFT JOIN $my_table as dup_check
                ON dup_check.rated_post_id = posts.ID
                WHERE dup_check.rated_post_id IS NULL" );

    I deactivated the plugin and upon activation to test, I get

    The plugin generated 663 characters of
    unexpected output during activation.
    If you notice “headers already sent”
    messages, problems with syndication
    feeds or other issues, try
    deactivating or removing this plugin.

    I have wp_debug on but I’m not getting any detailed errors.