WordPress.org

1. kevine
   Member
   Posted 1 year ago #

   I have a category "Audio" which has an ID of 7954. I'm using a custom field "enclosure". In some of my posts I have URLs pointing to .mp3 files in the enclosure field.

   In MySQL, I can get a listing of all of my posts that have .mp3 enclosures by using:

   SELECT * FROM 'wp_postmeta' WHERE meta_value like "%.mp3%"

   But how do I then set the category ID of those posts to 7954?

   (Member vtxyzzy, I bet you can do this...and combined with my last question, will probably open a huge door of knowledge and understanding...I'm almost getting there. I know there's a join or two involved somehow:)

2. vtxyzzy
   Member
   Posted 1 year ago #

   Please BACK UP first!

   I can't test the exact statement, but this should be close:

   INSERT INTO wp_term_relationships
   SELECT pm.post_id,tt.term_taxonomy_id,0
   FROM wp_postmeta pm
   JOIN wp_terms t ON (t.name = 'Audio')
   JOIN wp_term_taxonomy tt ON (t.term_id = tt.term_id)
   WHERE tt.taxonomy = 'category'
   AND pm.meta_key = 'enclosure'
   AND pm.meta_value LIKE '%.mp3%'

3. kevine
   Member
   Posted 1 year ago #

   Thanks again for helping!

   I'm working with a copy of my database so I can play around and mess it up (and also have a backup).

   What you posted mostly worked, but I got an error:
   #1062 - Duplicate entry '2106-8044' for key 1

   Oddly, it still put some of the posts into the Audio category, but stopped in chronological order, but not on post ID 2106

   When I went to post ID 2106 in WordPress I see there are two enclosure fields, which was an error made by the person who made the post. I deleted one of the enclosure fields and ran the query again. The second time I get:
   #1062 - Duplicate entry '7-8044' for key 1

   Post ID 7 didn't have a second enclosure, but it was already in the Audio category from having run the query the first time. When I delete the category and recreate it (clearing all posts from the category) it then runs without error.

   Is there any way of modifying this query so that it won't have an error if the post is already in the category?

   If not, it's still very useful, and I can assign this as Resolved.

4. vtxyzzy
   Member
   Posted 1 year ago #

   Just add IGNORE to the INSERT statement:

   INSERT IGNORE INTO wp_term_relationships

5. kevine
   Member
   Posted 1 year ago #

   That's the ticket!

   Thanks again. This is wonderful!

Topic Closed

This topic has been closed to new replies.