Looping posts with content from different database

Hi,

Thanks for your reply. So as I thought, using WP_Query directly is impossible here – you are right that the table structure would have to be identical, and I know it isn’t. Oh, and the caching plugin, just as you said, I was thinking about reducing the database hits, I wasn’t assuming it will change the structure. And the schedule event should help with my second idea, thank you for that.

So one more thing to figure out, the translation. Would you have any suggestions? If I query forum posts and insert them into WordPress table, there will be problems when trying to use the loop. I would have to somehow translate them before inserting. Or is it possible to do this after inserting and before querying them for the loop?

Thanks for the help.

Thank you for your help. I still have some things to learn, but I’m on the right track, so I’ll mark it as resolved.

Hi,

I’m back after a while. I’m not really stuck (yet), just looking for a few answers. I’m not sure if I should post another topic after setting this one as resolved… I’ll repost it if that’s necessary.

Anyways – after some reading I’ve created a custom post type, connected to my forum database and inserted a few posts into wp_posts. This required simple SQL functions to convert date and post name columns and surprisingly it works. But then I checked the number of queries and now I’m thinking it’s a terrible idea. Or I’m doing this terribly wrong. Here’s the code:

// $mydb is new wpdb connecting to forum database
$forumrows = $mydb->get_results( $sql, ARRAY_N );

foreach ($forumrows as $row) {

$news = array(
        "post_title" => $row["0"],
        "post_content" => $row["1"],
        "post_name" => $row["2"],
        "post_date" => $row["3"],
        "post_date_gmt" => $row["3"],
        "post_type" => "news",
        "post_status" => "publish"
);

// checking forum section ID  (column "4") to set category
    switch ($row["4"]) {
        case 62:
            $cat_ID = 2;
            break;
        case 63:
            $cat_ID = 3;
            break;
    }

$post_ID = wp_insert_post( $news );

wp_set_object_terms($post_ID, $cat_ID,'category');
}

As I said, it works – it queries post data from the forum, inserts it into wp_posts and adds a category depending on the forum section ID. But inserting just 10 posts resulted in 196 queries. If I were to insert all my posts, and there are about 900, I would probably use up database connections limit on my server. And still would need to update them regularly.

Is there a more efficient way? Or is my code that inefficient?

Any help will be appreciated.

Thank you bcworkz!

You gave me an idea. A few even. I’ll start from the end. 😉

Scheduling the import won’t help me. I checked my server limits, it has daily queries limits. But the limit is in millions, and by doing simple math (if 10 posts require 200 queries, 1 post = 20 queries) I can see that inserting my 900 posts this way would give me about 18 000 queries. I know that’s A LOT, but even if I wouldn’t care and updated all my posts every hour, I still would be quite safe. I do care though, and the least efficient way I’m considering right now is doing the big update once a day and updating 10 latest posts every hour. That would give me ~23 000 queries. Still a lot, but could be worse. And I prefer to be efficient, so…

The direct approach. At the very beginning, when I did research on doing this import, I found out how to do this in MySQL – you can use good old INSERT INTO SELECT on both wp_posts and wp_term_relationships (I did it just five minutes ago to be sure if I remember correctly and it works). It’s a little complicated, but here’s how to do it.

First you insert data into wp_posts – you have to remember you’re not using wp_insert_post anymore, so you need to take care of most of the columns by yourself (post_title, post_content, post_name, post_date, post_date_gmt, post_type, post_status). In my case I needed to use FROM_UNIXTIME for both dates and LCASE and REPLACE space with “-” for topic_name.

Then the wp_term_relationships table – there are only three columns here (object_id, term_taxonomy_id, term_order). You insert your post ID in the first one, and in the term_taxonomy_id you insert the ID of your category or tag (which you’ll find in wp_terms table). The third column is set to 0 in my case, but it will probably change if I create categories inside categories, I’m not sure.

And that’s it. My WordPress doesn’t complain after this, I can see the post in the loop, in category view and single post view, so I think it’s OK. But you may be right about using the wp_set_object_terms. If I would need to set a category and a few tags, this could be better. I see a problem though.

wp_set_object_terms needs the post ID. If I’m not using wp_insert_post, I’m not getting the ID. Would it be OK to insert posts with my ID? Posts on my forum have a topic ID. It would be great if I could use it as post ID, later on I could even import topic replies as comments thanks to it. But will WordPress accept that? The ID is auto incremented, won’t there be any errors if I insert posts with IDs like 120 or 350?

Oh, and one more question – warning, it’s kind of noobish. ;p As I said, before I did this in MySQL (INSERT INTO wordpress.wp_posts (all the columns) SELECT all the values FROM forumdb.topics and a few joins), how do I use wpdb here? $wpdb for inserting columns, new wpdb ($mydb in my example) for getting post data from the forum but how should the whole query look like?

Thanks for your patience.

It works! Thank you bcworkz, I would have missed the taxonomies and it would cause quite a few problems.

One more question. Yeah, I know, no surprise here. 😉 But this one is more interesting, I hope.

I’m thinking if there is a way to include author’s name in the inserted post. I can’t insert it – wp_post stores only authors ID while my forum table holds the user name. What may make it easier is that I don’t need it to be a link – so no profile or listing posts by the author. Is there an efficient way to include the name in the post outside of the inserting process that I’m doing?

I’m back after some time. I hope you had great holidays.

I need to thank you for all your advice, bcworkz. After a while I’ve got to a point where I’m building an actual plugin and it works as intended. Now I’m trying to make it use the settings API to dynamically change the sql query and it’s getting a little too complicated for my level of PHP and WordPress knowledge, so it’s time to ask more questions. 😉 But I think it will be better if I start a new topic. Thanks again!