Solved

Wordpress WHERE Category ID equals

Posted on 2011-09-21
6
617 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Technology Partners: 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!

Question has a verified solution.

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

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
The purpose of this video is to demonstrate how to Import and export files in WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Click on Too…
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…

717 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