• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 630
  • Last Modified:

Wordpress WHERE Category ID equals

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
petewinter
Asked:
petewinter
  • 4
  • 2
1 Solution
 
petewinterAuthor Commented:
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
 
petewinterAuthor Commented:
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
 
jeremyjared74Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
petewinterAuthor Commented:
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
 
jeremyjared74Commented:
You should use the RSS feature then:
http://www.feedforall.com/rss2html/index.htm
0
 
petewinterAuthor Commented:
Many thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now