Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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.

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.

share|improve this question
add comment

3 Answers

up vote 6 down vote accepted

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;
share|improve this answer
    
Thanks for adding this one, I was a bit in a hurry while answering. –  hakre Dec 5 '10 at 23:06
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.