Huge Data Import, WordPress php & mysql

4 Views

I have 2 databases on 2 different servers. The source database is a massive real estate database and the second is a WordPress database. I have to import the data from the source to the WordPress database’s posts and postmeta tables.

Currently, I’m pulling everything out of the source db and then looping through those results to insert them into the posts table with another nested query to insert each field as a metavalue using the insert id from the post insert. In other words, it’s a memory hog.

Read More

My question is, can the loop I have below be improved so that there aren’t so many individual insert lines? Or does anyone have any suggestions for making this faster / not as sloppy? 

// SOURCE DB
$query = "select $fields from listings where data_id = 'B'"; 

$result = mysql_query($query);

// WORDPRESS DB
while ($row = mysql_fetch_assoc($result)) {
    $query2 = "insert into wp_posts (post_author, post_content, post_title, post_status, comment_status, ping_status, post_name, post_type) values";
    $query2 .= " ('1', '" . mysql_real_escape_string($row['remarks']) . "', '{$row['mls_acct']}', 'publish', 'closed', 'closed', '{$row['mls_acct']}', 'properties')";

    $result2 = mysql_query($query2);

    $id = mysql_insert_id();

    foreach ($row as $key => $val)
    {
        $query3 = "insert into wp_postmeta (post_id, meta_key, meta_value) values ";

        $query3 .= "('$id', '$key', 'mysql_real_escape_string($val)')";

        $result3 = mysql_query($query3);
    }
}
Post Views: 4

Related posts

Leave a Reply

2 comments

  1. The first thought I have on this is to insert the records in batches rather than one at a time. So your inner insert would end up being something like:

    insert into wp_postmeta (post_id, meta_key, meta_value) values
(1, meta_key_1, meta_value_1),
(1, meta_key_2, meta_value_2),
(1, meta_key_3, meta_value_3),
(1, meta_key_4, meta_value_4),
(1, meta_key_5, meta_value_5),
(1, meta_key_6, meta_value_6),
(1, meta_key_7, meta_value_7),
...
(1, meta_key_100, meta_value_100);

    I’m not sure what’s the optimum number of records per batch, but I think you get the idea.

  2. You could probably do an INSERT ... SELECT FROM ... as a single query rather than the looped version you’re doing internally:

    INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
    SELECT $id, meta_key, meta_value FROM wp_posts WHERE id = $id

    I’m not particularly familiar with WP’s internals (and I really really don’t ever want to become so…), but if you can tweak this to work you’ll save yourself a few dozen separate insert queries inside the loop.