Solved

Wordpress WHERE Category ID equals

Posted on 2011-09-21
6
603 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
  • 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In order to have all security and back ups taken care of, WordPress users can sign up for services with WP Engine.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The purpose of this video is to demonstrate how to add AdSense Ads to a WordPress Website, and how to set up WordPress to automatically place Ads in Sidebars. This will be demonstrated using a Windows 8 PC. Log into your AdSense account. : Cli…
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now