Solved

Wordpress WHERE Category ID equals

Posted on 2011-09-21
6
616 Views
Last Modified: 2012-05-12
I am trying to display my wordpress blog posts in another area of my website that is not wordpress. So I am just connecting to the database directly.

I have done the below query:

$query_rs_cwes_wp = "SELECT post_title FROM wp_posts WHERE post_status = 'publish' AND post_type = 'post' ORDER BY post_date DESC";

Which works, but I need to only show posts in category id 4 which has a name of "blogs".

I can't see a column in the "wp_posts" table that links to the category name or id. Can you please help?
0
Comment
Question by:petewinter
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 

Author Comment

by:petewinter
ID: 36574256
Looking into the wordpress database structure it looks like the "wp_posts" table joins to the "wp_term_relationships" table from the "post_parent" column to "object_id" column, then the "term_taxonomy_id" column shows the category id.

I assume that I need to join these tables, then say where the "term_taxonomy_id" equals '4'

Please can you help as my php sql knowledge is not good enough.
0
 

Author Comment

by:petewinter
ID: 36574571
I have attempted to write the sql, but it's not showing any rows so I must have done something wrong...

$query_rs_cwes_wp = "SELECT ter.*, wpp.post_title
                               FROM wp_term_relationships ter
                               JOIN wp_posts wpp
                               ON ter.object_id = wpp.post_parent
                               WHERE ter.term_taxonomy_id = '4'
                               AND wpp.post_status = 'publish'
                               AND wpp.post_type = 'post'
                               ORDER BY post_date DESC";

Please help
0
 
LVL 23

Expert Comment

by:jeremyjared74
ID: 36574927
Here is how you would do it:

1. Open Text Editor

Place this code in the very top of your blank text doc so WordPress recognizes it as a template file:
<?php
/*
Template Name: Blogs Posts
*/
?>

Open in new window


2. Name The Template

Save the file as blogs-posts.php

3. Replicate Current Layout

Open your index.php file, or any template file that has complete code.
NOTE:
Some themes use a loop.php file, if your theme does you might need to use the page.php file. Your going to copy and paste the code from that template file into the blogs-posts.php you just created (just below the custom template code you added earlier). The reason you want a complete template and not was that uses get_template_part is because it's easier to get the complete div structure so the look matches the other pages.
Once you've decided what template to copy, paste it under the new template.

4. Replace The Current Loop

Now remove everything except for the <div> structure.
You should now have a templage files that looks something like this (all loop code is gone):
<?php
/*
Template Name: Blogs Posts
*/
?>

<?php get_header(); ?>
<div id="left-column">
  <div class="panel">



    <?php endwhile; ?>
  </div>
</div>
  <?php get_sidebar(); ?>
<?php get_footer(); ?>

Open in new window


5. Add New Code

Now you'll add the code that calls the posts for your specific category. This is the code that makes it work:
<?php $wpc_post_array = array( 'cat' => 40, 'posts_per_page' => -1, ); ?>
  <?php query_posts( $wpc_post_array ); ?>

Open in new window

NOTE:
This usually goes just after the first div in your theme. It might vary depending on your layout. Once you're finished you'll notice if something is our of order with the div's. The rest I'll just post the code you need and you can figure out the div and css layout.

That alone won't display the posts, it just defines the custom function/variable. Now you'll need to complete it with the loop stuff.

6. The Complete File

Here is the complete code for my sample:
<?php
/*
Template Name: Blogs Posts
*/
?>

<?php get_header(); ?>
<div id="left-column">
  <div class="panel">
  <?php $wpc_post_array = array( 'cat' => 40, 'posts_per_page' => -1, ); ?>
  <?php query_posts( $wpc_post_array ); ?>
    <?php while (have_posts()) : the_post(); ?>
      <div class="custom-cat">
        <h2><a href="<?php the_permalink() ?>"><?php the_title(); ?></a></h2>
        <?php the_content(''); ?>
      </div>
    <?php endwhile; ?>
  </div>
</div>
  <?php get_sidebar(); ?>
<?php get_footer(); ?>

Open in new window


Save the file after correcting the div's and upload it to your themes root folder.

7

Adding A Page To Hold The PostsNow go to your WordPress Admin Panel and add a new page named (Blogs, or whatever it doesn't really matter). You don't need to add any content, just change the template used to the new one by clicking the drop-down list on the lower right of the page editor screen. Save the page and you should now have the posts of only the "Blogs" category.

NOTE:
In the code above, notice where it says:
'cat' => 40, 'posts_per_page' => -1,

Open in new window

You'll need to use your Category ID where I have 40, and the -1 is to display all posts in that category. If you only wanted to show 5, it would be:
'cat' => 40, 'posts_per_page' =>5,

Open in new window


I hope that helps.
0
Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

 

Author Comment

by:petewinter
ID: 36580362
jeremyjared74: Thanks for your email, but I want to display post blogs outside of the wordpress environment. i.e. I creating page else where, but want the pull in my blogs titles and link to my blog. So it dynamically updates.

Any idea's how I do this?
0
 
LVL 23

Accepted Solution

by:
jeremyjared74 earned 500 total points
ID: 36580593
You should use the RSS feature then:
http://www.feedforall.com/rss2html/index.htm
0
 

Author Closing Comment

by:petewinter
ID: 36580740
Many thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
This article discusses how to create an extensible mechanism for linked drop downs.
The purpose of this video is to demonstrate how to integrate Mailchimp with WordPress, by placing a Mailchimp signup form on a WordPress Page or Post. This will be demonstrated using a Windows 8 PC. Mailchimp will be used. Log into your Mailchi…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question