SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2006
    Posts
    182
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Loop through select results and perform insert with results?

    Hey Guys,

    I scripted the import of a bunch of articles from a static site to a wordpress site. I inserted all the images into the wp_table, but in order for them to show up correctly in the media browser I need to add an entry into the wp_postmeta table for each one. So basically I need to run this query:

    Code MySQL:
    SELECT ID,guid
    FROM wp_posts
    WHERE post_type = 'attachment'
    AND
    ID < 7488
    ORDER BY id;

    and then for each row, run this:
    Code MySQL:
    INSERT INTO wp_postmeta (post_id,meta_key,meta_value)
    VALUES
    (@ID,'_wp_attached_file',replace(@guid,'http://localhost/',''))

    where @ID and @guid are the corresponding data for that row. I could write PHP to do this, but it would take forever to loop through the couple thousands of entries. Can someone offer a way to do this right from MySql? I'm getting better with MySql but this stumped me... thanks.

  2. #2
    SitePoint Zealot
    Join Date
    Dec 2006
    Posts
    182
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ha! I figured it out:
    Code MySQL:
    INSERT INTO wp_postmeta_copy (post_id,meta_key,meta_value)
      SELECT ID,'_wp_attached_file', REPLACE(guid,'http://localhost/wp-content/uploads/','')
      FROM wp_posts
    WHERE post_type = 'attachment'
    AND ID < 7488
    ORDER BY ID;

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    nice job in figuring that out

    isn't it good to know that you can do it all in one query, rather than a bazillion query executions in a loop?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •