Support » Fixing WordPress » mysql query for all posts in “news” category?

  • Hello,
    I’m hacking WordPress a bit for a custom family project and want to make a custom monthly archive list for a single category. For me it would be easiest to start with a mysql query, but my brain has become mushy this evening.

    What mysql statement would I use to query all posts in my “news” category (or category id of 3?) as an example? If I figure this out first I’ll post the results.

    Thanks for helping me collagulate my mush in advance. 🙂

Viewing 5 replies - 1 through 5 (of 5 total)
  • if my memory serves right…

    $category_sql = 'select * from wp_content order by post_category';
    $category_result = mysql_query($category_sql);

    I think that is along the general lines of what your asking… thats just a basic framework of the code… should query the data base on field wp_content and order the search by post_comment. From there its just a matter of righting the code to obtain which category to work from, that should help with your coagulation.

    The Prophecy
    [sig moderated]

    Hmmm. That’s what I remember as well. The last release I downloaded has wp_posts (no wp_content), and post_category number there is always showing up as 0 on every post. Posts on the test site are clearly in “news” or “testimonials” categories. I’ll keep digging. Let me know if additional thoughts come up. Thanks much. 🙂

    Here’s what worked for me. It makes sense as a post can be in multiple categories:

    SELECT * FROM wp_posts p LEFT OUTER JOIN wp_term_relationships r ON r.object_id = p.ID LEFT OUTER JOIN wp_terms t ON t.term_id = r.term_taxonomy_id WHERE p.post_status = 'publish' AND p.post_type = 'post' AND t.slug = 'news'

    Well you got it working and posted what works 🙂 Helps everyone.

    SELECT * FROM wp_posts p LEFT OUTER JOIN wp_term_relationships r ON r.object_id = p.ID LEFT OUTER JOIN wp_terms t ON t.term_id = r.term_taxonomy_id WHERE p.post_status = ‘publish’ AND p.post_type = ‘post’ AND t.slug = ‘news’

    this code seems to work but doesn’t post only live posts, it also shows posts that are scheduled before they’re supposed to be live. anyone else see this?

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘mysql query for all posts in “news” category?’ is closed to new replies.